Microsoft Excel, Office Watch / Microsoft Excel, Microsoft Office /

Multi-level subtotal bug in Excel

by Office Watch 28 July 2005

We look at an interesting bug in both Excel 2002 (XP) and Excel 2003.

Thanks to Bruce N for reminding us about an interesting bug in both Excel 2002 (XP) and Excel 2003.

As Microsoft puts it: “multilevel subtotals for data in Microsoft Excel 2002 or Microsoft Office Excel 2003, and you set the subtotals to appear in the rows that are immediately above the data that they are based on, the totals appear in the incorrect order (staggered incorrectly) and may exclude extra Grand Totals values. “

It’s detailed here.

The fix is simple for Excel 2003 – simply get the Service Pack 1.

For Excel 2002 (XP) you have to get a special hotfix after calling Microsoft support – therein lies a totally separate story below.

Amazingly, the fixes supplied are NOT complete fixes. There are still situations where the sub-totals are in the wrong order. Customers are expected, in effect, to override the patch by changing a registry setting which tells Excel to revert to Excel 97/2000 functionality.

With updating policies like this, is it any wonder why we’re nervous about automatic updating of the Office programs.

THE NEVER-ENDING EXCEL HOTFIX

Finding the details of the Excel 2002 hotfix will take you through a seemingly never ending line of Knowledge Base pages. Each new release includes updates to fixes in the previous patch but there seems to be no single list .. except for the one we’ve prepared below.

We made this list after finding the first KB article which says the hotfix detailed in it has been superceded, so you click the link to another hotfix KB article and so on through 14 KB articles until you find the end of the chain.

Here’s a list of all the fixes in the patch for Excel 2002 showing the KB article link, original date of the article and a summary of the bug fixes:

6 May 2004

Note: registry changes also needed to active some fixes after applying the patch

Error message when you try to save a workbook that was opened from a mapped drive location in Excel 2002
Date format is not correct when you convert a CSV text file in Excel 2002 by using a VBA macro
“‘filename.xls’ cannot be accessed” error message when you open a workbook in Excel 2002 during a remote desktop session
Controls move to the left of the worksheet in Microsoft Excel 2002
Multilevel subtotals are in the wrong position in Excel 2002 Plus 20 other fixes that are not listed in separate KB articles.

13 May 2004

Recalculation of cell range takes longer than expected in Excel 2002 Plus a VBA error

11 June 2004

Secrets and tips for the Windows 10 May 2020 update

A detailed and independent look at Windows 10, especially for Microsoft Office.

Fully up-to-date with coverage of the May 2020 major update of Windows 10.

This 1,000 pages, 40 chapter book shows you important features and details for Windows 10 users.

Print Preview and Replace All with array bugs with no separate KB’s

17 June 2004

SQL Query to Oracle database and chart label bugs

24 June 2004

RealTimeData (RTD) function and related bugs

16 July 2004

“Currency with the French (Canada) format, the negative currency format of (1 234,10$) is not available.” And “programmatically create a query table by using a parameter query” bugs

30 August 2004