Excel 2004: Pasting text in cell fails to switch to ‘editing in cell’ mode

Posted by Pierre Igot in: Microsoft
April 2nd, 2007 • 7:00 pm

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:

Selected cell

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

Selected cell

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:

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:

File name copied from Finder

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:

Pasted text in cell

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:

Deleted text!

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.

4 Responses to “Excel 2004: Pasting text in cell fails to switch to ‘editing in cell’ mode”

  1. Schwieb says:

    Here’s a scenerio for you, Pierre, and a question:


    1) select a single cell in Excel (say D4, for example)
    2) go to another app (TextEdit, perhaps) and enter some text that contains tabs and carriage returns (ie, so you have multiple rows and columns of roughly-formatted text)
    3) select all that text and copy it
    4) switch back to Excel and press Cmd-V to paste it.


    What do you expect to happen in Excel? Should all the text you selected be entered into the single cell you have selected, or should Excel paste the text into multiple rows and columns to match as best it can the rows/columns you have set up in TextEdit?

  2. Pierre Igot says:

    It’s a strange scenario. I can’t imagine why someone would want to do this. It’s quite obvious to me that “roughly tabulated”—“roughly formatted” is a misleading phrase, since tabs and returns do not constitute “formatting” in the word processing sense—plain text needs to be cleaned up before an attempt to import it into an Excel sheet can be made. And even then I would try and use a proper import command rather than cut-and-paste.

    I think most users can, at least intuitively, grasp the idea that the optimal place to enter tabulated data is a spreadsheet application. If the data comes in plain text (because it was sent via an e-mail message, for example), then it will obviously need to be cleaned up.

    So I don’t really know what to answer to your question, because I don’t see this situation happening.

    Now, if this is an oblique way to try and justify Excel’s current behaviour in the scenario I provided above, you might save us some time and energy by getting straight to the point—i.e. if your point is that there was situations where pasted text “should” go in more than one cell. The situation you suggest is not a very convincing example.

    Besides, I am not saying that Excel should switch to “editing in cell” mode before pasting the text or should always paste everything into a single cell. I am just saying that, when I am pasting a single string of text (especially if it contains no tabs or returns) into a single cell, the logical thing for Excel to do would be to switch to “editing in cell” mode, because pasting text in is equivalent to typing it in, and I am likely to want to make corrections to that text—especially in light of the fact that Excel will switch back out of “editing in cell” mode as soon as I press Tab or Return anyway (which means that it won’t require extra work from me to get out of that mode if I don’t need it).

    Right now, it’s a pain to have to remember to use the special shortcut to switch to “editing in cell” mode each time I paste some text in a cell that is not already in that mode, before I can use other text editing shortcuts such as Delete, option-shift-Left, etc.

    Incidentally, I should add that Excel’s behaviour when “Edit directly in cell” mode is off is not much more intuitive. If I do the exact same thing as above (i.e. tab into the cell, then press command-C to paste some text, then press Delete), Excel also deletes the entire cell content. But then if I press command-C a second time, and then Delete again, this time it only deletes the last char.

    In other words, even when text can only be entered via the Formula Bar, the behaviour is still rather unpredictable. (The only visual clue is the I-beam cursor blinking in the Formula Bar. And there’s no explanation why it appears after the first delete. After all, if the first Delete deletes the entire cell contents, then it seems that I am not editing IN the Formula Bar, yet it switches me to that mode right after the Delete key.)

    I just think that switching in and out of the “editing in cell”/”editing in Formula Bar” mode should be more predictable, and should not only depend on the user actually typing some text (or pressing the Delete key). Command-C (of a string without returns or tabs, if you’d rather) into a cell (or drag-and-drop into a cell, when “Edit directly in cell” is on) should also switch to the editing mode. And I would argue that, when I am tabbing from a cell that is in editing mode to the next one, Excel should stay in editing mode.

    But maybe it’s just me :).

  3. Radardan says:


    I always feel better after reading Betalogue because you so assiduously detail those crazy annoyances with MS Office. You remind me every time that, oh yes, I’ve plowed right by that many times and thought it was something I did wrong.

    I think in the following paragraph of your original post, you meant Command-V.

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

    A minor matter.

  4. Pierre Igot says:

    Thanks for the kind words. If I can help a few people avoid going insane, that validates my reporting :-).

    Thanks too for the note about the typo. I actually had it wrong in two places in the post. It’s now fixed.

Leave a Reply

Comments are closed.