Wailing and gnashing at Excel

I just tripped over a monu-$@^*#(%-mental UI "bug" in Excel (for Mac 2011)! Please permit me to rage against the machine, and perhaps help someone else avoid this gotcha. :-(

In large multi-sheet Excel workbooks, I often use color-coding of sheet tabs to provide some visual grouping of related tabs. And such colouring can be applied en masse by selecting all the relevant tabs together via the usual Shift or ⌘ methods. I just did precisely that to 6 sheets within a 30-sheet workbook. Each of the six contained a table, identical in dimensions to the others, providing related but different functions.

Now colouring the tabs is really just one example of a more general feature of such sheet grouping -- i.e. group editing. And it's a very useful feature, especially when the sheets have common aspects. In my case, each sheet with its table acts like a software function or module, and just like in software it's not unusual to want make the same kind of change across a set of functions/sheets.
Not unusual, but not always. You can probably guess where this is going. Immediately after colouring the group, I needed to make a change to one of the sheets. It was a substantial change to the formula in every cell within that sheet's table. I changed one cell, made sure it was correct, and then copy-pasted the change to the rest of the table in the sheet. Or, I thought it was to the rest of the table in the sheet.

You see, when a number of tabs are group selected they remain selected until another tab, outside the group, is clicked. Now that's fair enough, but far less fair enough is that the fact that the sheets are still grouped is not very obvious. There is a slight change to the shading of the selected sheets, but it's subtle and doesn't stand out when neighbouring tabs are also part of the selected group.

So after applying the colour, my group of tabs remained selected, a fact that escaped my Friday-evening-and-been-hacking-Excel-all-day weary eyes. Since I was still operating within one of the grouped sheets, they all stayed grouped, and my edit, intended for only one of the sheets, was applied across the entire group, essentially making all six sheets identical. Although the sheets are related (hence the colouring in the first place), their respective formulae are both different and fairly complex. Wiping out that complexity in five of my six sheets kinda ruined my evening.

Of course Excel left the best bit to the very end. Deciding I hadn't suffered enough, the world's most popular spreadsheet program delivered its coup de grâce. Such group edits have no Undo option.