Betalogue

Last week, I wrote about the fact that Excel’s text wrap mechanism fails to work properly when the text, instead of being typed out manually by the user in the table cell, is entered by pasting it from the Clipboard.

Well, there are more problems with pasted text in Excel. Here’s one that has to do with the intrinsic duality of Excel’s user interface when editing a spreadsheet.

Excel’s user interface schizophrenia resides in the way it handles cell editing once you have checked the “Edit directly in cell” option in Excel’s preferences, under “Edit.”

Once this option is checked, you can, as the label says, edit the cell’s contents directly in the cell, which, you know, makes sense. The problem, as usual with Microsoft, is in the implementation.

Because Excel was not initially (i.e. a long time ago, in computer prehistory) designed to allow such immediate and intuitive user interactions (you had to do everything through the Formula Bar), a good part of its core functionality still works based on the assumption that you are not editing directly in cells.

In effect, what happens is that Excel constantly switches in and out of the “editing in cell” mode. You can usually tell which mode you are in by looking at the cell. If the cell is surrounded by a frame in the selection colour, you are not in “editing in cell” mode:

If the cell “sticks out” and has a drop shadow, on the other hand, then you are in “editing in cell” mode:

Unfortunately, the way that Microsoft has implemented the toggling between the two modes is, at best, confusing, and at worst, downright infuriating.

I have already described, in previous posts, various problems that are related to these two editing modes:

• “Excel 2004: Cursor keys don’t work as expected in editable cell“
• “Excel 2004: ‘Select All’ command doesn’t work as expected“
• “Excel 2004: Behaviour when double-clicking on cell edges“
• “Excel 2004: Switches date formats when editing cells“

Well, here’s another one to add to the list.

Let’s say you have a row to fill in a spreadsheet, with a column for a numeric value and then a column for text. The text that has to go in the second cell is actually a file name. You already have that file name in a Finder window, so you select the file in the Finder and make the file name editable, and you copy it by pressing command-C:

The Clipboard now contains “File Name That I Don’t Want To Have To Type Again.doc.”

Then you go back to your Excel spreadsheet and start editing the row. You double-click on the cell in the first column to switch to “editing in cell” mode, as indicated by the drop shadow and the blinking I-beam cursor in the cell. And you type whatever numeric value you need to enter.

Then you press Tab to jump to the next cell, where you want to paste that file name. Only you actually want the file name only, without the “.doc” file extension. So what’s the natural thing to do?

Once you are in the second cell (after pressing Tab), you type command-V, to paste the contents of the Clipboard:

So far, so good. But now you want to edit this text, i.e. delete the “.doc” extension. So you just press the Delete key to start deleting from the end, right?

WRONG! If you press the Delete key now, Excel deletes… the entire content of the cell:

And it even kindly switches you to “editing in cell” mode at the same time! How nice of Excel to do this, uh?

Here again, Excel gets it completely wrong. During the whole process, once I double-clicked on the first cell to start entering my number value, I never meant to exit the “editing in cell” mode. But Excel decided unilaterally to exit the mode for me, when I pressed the Tab key to jump from the first cell to the second cell.

And when I pressed command-C in the second cell, even though I was clearly editing in the cell (by pasting some text in it), Excel completely failed to register that fact, and continued to behave as if I was not in “editing in cell mode.”

That is why, when I pressed the Delete key after that, Excel deleted the entire contents of the cell. Because when you have selected a cell, but are not in “editing in cell” mode, Delete means “delete the entire contents of the cell,” whereas when you are in “editing in cell” mode, the Delete key behaves the way it normally does when editing text, i.e. it just deletes the last character before the I-beam cursor.

So here we are. A very simple demonstration of how Excel’s interface for editing cells simply falls apart because of the utter confusion between the two editing modes, and the lack of consistent, intuitive behaviour.

It could be argued here that, once I entered the “editing in cell” mode in the first cell, Excel should have stayed in that mode even when I pressed Tab to go to the next cell. But even without adopting such a drastic approach, Excel should at the very least know that, when I paste text in a cell with command-V, I am actually editing the cell’s contents, and therefore it should switch to “editing in cell” mode.

I am not saying that designing a nice interface for editing tables is easy. Many people use Excel in many different ways. But the fundamental problem with Excel is that it is, in so many ways, unpredictable. When does it automatically to “editing in cell” mode and when does it not? It’s very hard to predict, and it certainly not based on consistent, predictable rules.

This entry was posted by Pierre Igot on Monday, April 2nd, 2007 at 7:00 pm and is filed under Microsoft. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.