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:
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.
- Recalculation of cell range takes longer than expected in Excel 2002 Plus a VBA error
- Print Preview and Replace All with array bugs with no separate KB’s
- SQL Query to Oracle database and chart label bugs
- RealTimeData (RTD) function and related bugs
- “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
- VBA macro to display Templates dialog doesn’t work.
- Subject line of File | Send to | Mail Recipient can be truncated.
- “Text and text boxes may be missing from a chart that you copy as a picture”
- Spelling checking issue with pre-reform rules in the German version.
- Bug when running a custom Web program that streams HTML Web content in IE
- Opening a worksheet with a PivotTable or VBA macro bugs.
- Three more bugs as listed in the KB.
- Mouse pointer on left margin of a worksheet bug.
All these bug fixes are accumulated into a single ‘hotfix’ which you have to call Microsoft support to specially request.
The fix requires Office XP Service Pack 3 to be installed first. If you have one of these bugs then good luck calling MS support.
