Excel 2008: Cell format problem not just with existing Excel 2004 files

Posted by Pierre Igot in: Microsoft
January 25th, 2008 • 4:31 pm

Earlier this week, I wrote about a problem that I was encountering with cell formatting options when opening my existing Excel 2004 files with Excel 2008.

I have since discovered that the problem is actually worse than I thought. It does not just affect my existing Excel 2004 files. It also affects new files created with Excel 2008, as long as they are saved in the Excel 2004 format.

The problem appears to be that, if a new formatting option (either number format or alignment option) is applied to an entire column by selecting the column heading, then Excel 2008 fails to correctly apply the formatting to the range of empty cells that extends vertically below the last cell that contains some value.

Even with the simplest of spreadsheets, the number of rows appears to be infinite. If you open a brand new spreadsheet file in Excel and then press the Page Down key and hold it down, Excel will scroll down to what looks like the bottom edge of the table (based on the position of the blue blob in the vertical scroll bar) and then… it will continue to scroll down, and down, and down. (And the blue blob will gradually be reduced to its smallest size. But if you then scroll back up to the beginning of the table, the blob will go back to its “normal” proportional size.) It has always been that way in Excel, because that’s the only way that Microsoft knows how to deal with such UI challenges: Create a completely illogical UI.

Because of this infiniteness of the number of rows, the logical thing to do when you want to apply a certain formatting option to an entire column is to select the entire column, by clicking once on the column heading.

Alas, in Excel 2008, this is no longer an acceptable approach, because of a new bug introduced in this new version. If you select an entire column this way, and then apply a formatting option to the column, and then close and reopen the file, there is a good chance that the formatting will only be preserved for cells in the column that already contain a value. For cells that do not contain a value yet, Excel 2008 tends to revert to the previously used format, not the formatting option you’ve just selected.

I say “tends to,” because it does not occur all the time. Right now, in order to be able to reproduce this problem, I need to save my spreadsheet file in Excel 2004 format (.xls) rather than the new Excel 2008 format (.xlsx). And I also need to apply one particular formatting option to the column first, using the column heading. Then if I select the entire column and apply a new formatting option, and then close and reopen the file, when I try to enter a new value in an empty cell in the column, Excel 2008 reverts to the previous formatting option, instead of using the one I have just applied the entire column.

In other words, I can reproduce the problem 100% of the time by following these steps:

  1. Create a new file in Excel 2008 and save it in Excel 2004 (.xls) format.
  2. Select the entire first column by clicking on the “A” column heading above the first row.
  3. Apply a specific number format to the selection (for example the custom “yyyy-mm-dd” format for dates).
  4. Type a few date values in row 1, 2, 3, 4, etc. of column 1.
  5. Save and close the file.
  6. Reopen the file.
  7. Select the entire first column by clicking on the “A” column heading above the first row.
  8. Apply a different number format to the selection (for example the custom “yyyy/mm/dd” format for dates, with slashes instead of dashes).
  9. Type a few more values in the next rows of column 1.
  10. Save and close the file.
  11. Reopen the file.
  12. Go to the next empty row in this date column and type a new value.

This time, instead of using the custom “yyyy/mm/dd” format, Excel 2008 uses the custom “yyyy-mm-dd” format again:

Bug with cell formatting

So it looks like the problem is caused by improper conversion between the .xls and .xlsx formats when closing and opening an Excel 2004 file in Excel 2008. Am I surprised? Of course not. With Microsoft, it was certain that a file format change would introduce a whole slew of new bugs, because Microsoft would not bother to properly test the new software with a range of existing real-world files.

But of course the problem is that all my existing files are in Excel 2004 format, and most of them have various cell formats applied by selecting the entire column. So most of my existing files seem to be affected.

Am I going to bother to convert all my files to the new format to avoid the problem? Probably not. I think a much more sensible approach will be to convert my files… to Apple’s Numbers. Most of my needs are pretty basic, and yet obviously they are too basic for Microsoft to ensure that I won’t have to deal with stupid Excel bugs on a regular basis. I am quite frankly completely fed up with Microsoft’s products, and the less I have to use them, the happier I’ll be. When it comes to my spreadsheet needs, I am definitely going to switch to Numbers.

3 Responses to “Excel 2008: Cell format problem not just with existing Excel 2004 files”

  1. AlanY says:

    In one sense I feel sorry for the Office developers… it’s a small team and clearly they’re being overwhelmed by the amount of entropy that sets in with a large, old code base. On the other hand, it’s also an enormously profitable product and one for which they could hire an adequate number of developers and testers. (Mythical Man Month aside, time spent on refactoring can make a difference, as it did with Office 2007.)

    That said, Numbers is genuinely the better product anyway. It’s missing some core stuff (pivot tables, macros, some key types of charts, etc.), but it’s so much nicer to use for personal spreadsheets.

  2. Pierre Igot says:

    My main beef with Numbers at this point is a common problem with Apple’s applications, i.e. the lack of support for keyboard shortcuts/navigation. I hate having to switch the mouse all the time. But other than that, of course it is much nicer. The transition is already in progress as far as I am concerned :-).

  3. Paul Ingraham says:

    Numbers has a few missing features that deal breakers for a lot of Excel users, unfortunately. For me, it’s the inability to freeze header rows. I’m making the transition, but painfully, and it’s going to stay painful until I can freeze rows!

    As usual, Pierre, you point out subtleties with significance. I couldn’t easily see the problem your were describing in this post at first, but I trusted you to make it matter, and you did.

    Alan: Good observation about entropy in large old code bases, and I’m sure that’s a factor. But Pierre is often pointing out problems with Office that are not the consequences of inadequate resources, neglect and chaos, but of choices — really bad choices — that the Office developers actually went out of their way to make. This kind of thing has to also be in large part a case of institutional dementia, of a corporate culture that bullies and overwhelms the good judgement of the many intelligent individuals who must work there.

    They must be there, the intelligent individuals! They must! And yet something goes wrong, and the products are almost a parody of good design.

Leave a Reply

Comments are closed.