Betalogue

There is something fundamentally flawed in the way that Microsoft Excel handles Copy/Paste operations. And here’s an example to illustrate it.

I frequently have lists of items that I need to be able to number, like the following list of tracks:

I don’t particularly fancy having to type all the numbers manually myself, so I have a pretty simple Excel formula to do this automatically for me. It uses the ROW() function, which automatically inserts the current row number:

Here the value in the cell is “2” because that’s the number of the row on which the cell is located. But the formula makes this value context-sensitive. If I insert a row above and the row that is currently Row 2 becomes Row 3, then automatically the value in the cell will change to “3.” Like I said, it’s pretty straightforward stuff.

Now, let’s go back to my list of items above. Instead of having to type the item numbers manually, I want to use this formula that I have to get Excel to automatically insert the row number in the cell in column A in front of each item.

In order to do this, all I have to do is go to my sheet with the formula, select the cell with the formula, and press command-C to copy it. This copies the cell to the Clipboard, and also puts a dotted line around the cell to indicate that it is the cell that has just been copied:

Now all I have to do is go to my list of items, select cells A1 to A19, as indicated in the first screen shot above, and press command-V. This will automatically past the row number formula in each cell in the selection and, since the value of the formula is context-sensitive, this means that Excel will automatically insert the value of the row number. And that’s how I get this:

Perfect! That’s exactly what I wanted. Now I can copy my list of items with column A and I’ll have an item number in front of each item.

So what’s the problem? The problem is that this only works if you follow exactly the procedure as I have described it above. There is one particular step in the procedure where you have no flexibility at all, and it is the step immediately after pressing command-C to copy the cell with the row formula. After you’ve pressed command-C, like I said, the cell is copied and Excel puts a dotted line around the cell to indicate that it has been copied.

At that stage, however, you have to make absolutely sure that you do not lose this “dotted line” status for the copied cell. Yet there are several things that you might do that will lose the “dotted line” status. For example, if, while the cell has this dotted line, you go back to your list of items and accidentally double-click on one of the cells in column A (instead of single-clicking on it to select it), Excel makes the cell you’ve just double-clicked on editable. This is the expected behaviour after a double-click.

What is not expected, however, is that this accidental double-click also causes Excel to lose the “dotted line” status around the cell with the formula in the other spreadsheet. This double-click somehow breaks the “connection” with the source cell where the formula has been copied from, and now if you try and paste the contents of the Clipboard in column A (after having corrected your accidental double-click and selected the whole range of cells again, as illustrated in the first screen shot above), you just get this:

In other words, Excel completely fails to paste the copied formula in the 19 selected cells in column A. Instead, it just pastes “2” in the first cell in column A, and deselects all the other cells.

What happens here is that Excel completely loses the connection to the copied formula, and instead just pastes the value of the cell where the formula was as a plain number in the first cell of the selection.

Why is this wrong? Well, simply because a regular Mac user does not expect the connection to the copied item to break after a Copy operation, no matter what he does in the interval between the Copy operation and the Paste operation! In most other Mac applications, once you’ve copied something to the Clipboard with command-C, you don’t lose what you’ve copied, no matter what you do, as long as you don’t select something else and press command-C or command-V again.

In other words, once you’ve copied something, as long as you don’t affect the contents of the Clipboard with another Copy or Cut operation, the contents of the Clipboard should stay intact, and you should be able to paste them in the intended location without any problems.

Here, when you press command-C in Excel, the application supposedly copies the entire formula (with its context-sensitive value). Therefore, when the user pastes the contents of the Clipboard in the selected cells in the other spreadsheet, he normally expects the entire formula (with the context-sensitive value) to be pasted in the selected location.

But, as indicated above, if the user accidentally loses the “dotted line” status in Excel, the whole Copy/Paste process falls apart, and Excel only pastes the value of the copied cell, instead of its formula (and only pastes it in the first cell of the selection).

I am sure that Excel engineers have a good excuse for this unexpected behaviour. But the bottom-line is that it is non-intuitive, because it assumes that the user understands that, in addition to the normal Copy/Paste mechanism that he’s familiar with in Mac applications, there is a special connection with the source after the Copy operation, which can easily be broken before the Paste operation is done—in which case, the Paste operation will not work as expected.

And, like I said, it’s very easy to break this connection. A simple accidental double-click on a cell is enough. Of course, once you know about this, you learn to avoid it and you try to be extra careful about what you do between the Copy operation and the Paste operation. But I still don’t think it’s normal that a Mac application should force us to be more careful than we normally are during Copy/Paste operations if we want to get the expected results.

And this is just one of the many ways in which Microsoft applications such as Excel and Word are simply different from other Mac applications and constantly force us to adjust our behaviour and our work habits as Mac users who are used to consistency across all applications.

This entry was posted by Pierre Igot on Wednesday, May 17th, 2006 at 8:19 am 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.