Excel 2008: Can’t change cell format while editing cell contents

Posted by Pierre Igot in: Microsoft
October 10th, 2008 • 9:29 am

I am not an Excel specialist, but I do know my way around (although I use Apple’s Numbers most of the time for my own needs).

The other day, an employee at one of my tech support services’ clients called me because she was trying to fill out an Excel table and whenever she typed an item code number for one of the items she was cataloguing, if the code number started with zeros, Excel would automatically strip the zeros (and right-align the number).

The reason was obvious to me: Excel’s default cell format for table cells is a category called “General” which tries to guess what the user wants by detecting whether the value entered is a number value or a text label.

Because the employee’s code numbers only consisted of digits, Excel was treating them as number values—therefore simplifying them—even though they were not number values. And so Excel was altering them without the employee’s consent and she didn’t know how to prevent it from doing that.

I told her to select the cell(s) she wanted to change and then use the “Format › Cell…” menu command.

I told her to go to the “Number” tab in the “Cell Format” dialog box and…

Mmm. She didn’t have a “Number” tab. At least that’s what she said on the phone. I couldn’t see what she had on the screen, so I asked her to describe to me the contents of the dialog box from top to bottom. I told her to look for the row of buttons at the top. (There is no point in trying to call these “tabs” when speaking with ordinary users. They don’t know what a “tab” is.)

But she kept skipping that row and telling me that all she could see was fields for font values (i.e. character formatting). I insisted that there was a row of buttons there, until she told me that all she could see was “a thing that said ‘Font’ in blue.” That’s when I realized that she obviously wasn’t seeing that row of buttons and therefore not able to switch to the “Number” tab where the cell format type can be changed.

I couldn’t figure out why, so I just asked her to start all over again, select a bunch of cells and try again. And this time she saw the row of buttons and all was good. I told her to change the cell format type from “General” to “Text” and her code numbers starting with zeros were now preserved.

So what happened here? Well, I quickly discovered, by experimenting on my own machine, that what she did was that, instead of just selecting a cell, she entered the cell, by double-clicking on it instead of single-clicking. It’s a simple, common mistake. The difference between a single click and a double click is not always obvious to ordinary users. (The fact that Mac OS X’s Dock or Mac OS X’s column view only require single clicks, for example, does not help clarify the confusion for ordinary users. Does a single click select or open? Well, it depends…)

So she had double-clicked on a single cell, which meant that, in Excel, she had switched from cell selection mode to cell editing mode. And it just so happens that, when you are in cell editing mode, if you try to bring up the “Cell Format” dialog box, you’ll see that, instead of the full row of tabs at the top, it only has… a single tab, the “Font” tab. Which is exactly what she was seeing, and which explains why she was not able to change or even see the cell format type.

The fundamental question here, however, is: Why? Why is it not possible to change, or even see the cell format type while editing the cell’s contents?

And the simple answer is: Because Microsoft says so. There is no intrinsic reason why the user should not be able to change the cell format type while editing the cell’s contents. Apple’s Numbers lets you do that. It simply does the logical thing, which is to exit the cell editing mode as soon as you try to change the cell format type/options in the inspector palette.

Excel could do that too. But Microsoft’s engineers obviously think, if you are in the process of editing a cell’s contents, you have no reason to want to change its cell format type. Or at least they used to think that way.

Because the “Cell Format” dialog box obviously predates Excel’s Formatting Palette by many years. And it just so happens that, if you look at the “Number” section in Excel’s Formatting Palette, which contains the same controls as the “Number” tab in the “Cell Format” dialog box, this particular section of the Formatting Palette stays perfectly visible and accessible even when you are in cell editing mode, with the cursor blinking inside a table cell.

And you can change the cell format type via the Formatting Palette while editing the table cell.

Why the inconsistency? Why it is possible to change the cell format type via the Formatting Palette, but not through the “Cell Format” dialog box when you are in the process of editing a cell’s contents?

Because it’s Microsoft.

Comments are closed.

Leave a Reply

Comments are closed.