Error in Subtotal Wizard (Grouping and Outlining)


E

Ed

I'm having trouble with Excel 2003 and the Subtotals Wizard. When I perform
a three-level grouping on the data listed below, it appears that some groups
are missing and outline levels 3, 4, and 5 are messed up. Given the
following list of data:

Region columnb Office Employee columne Issue
NW SEA 123 Salary
NW PDX 234 Benefits
NW PDX 244 Salary
NW PDX 212 Salary
NW BOI 232 Benefits
SW LAX 673 Salary
SW LAX 34 Benefits
SW LAX 654 Benefits
SW PHX 556 Salary
SW DEN 322 Benefits
SW DEN 65 Salary
East ATL 778 Benefits
East ATL 890 Salary
East ATL 768 Salary
East NYC 77 Benefits


The "Region" label value is in Cell A1. The "columnb" and "columne" columns
have no detail values -- they act as placeholders for the subtotal labels of
the "Office" and "Issue" subtotals, respectively.

I sorted the list by Region (ascending), Office (ascending), then Issue
(ascending). Then, I started the subtotaling routine as follows:

Select A1
Select Data | Subtotals...
At each change in: Region
Use Function: Count
Add subtotal to: Region
Replace current subtotals = true
Page break between groups = false
Summary below data = true
Click OK

Select B1 (because a new column has been inserted at A)
Select Data | Subtotals...
At each change in: Office
Use Function: Count
Add subtotal to: Office
Replace current subtotals = false
Page break between groups = false
Summary below data = true
Click OK

Select B1
Select Data | Subtotals...
At each change in: Issue
Use Function: Count
Add subtotal to: Issue
Replace current subtotals = false
Page break between groups = false
Summary below data = true
Click OK

What I expected was the data grouped and outlined as follows:
Outline Level 1 = Grand Totals
Outline Level 2 = Subtotals for the Regions (and Grand Totals)
Outline Level 3 = Subtotals for the Offices (and Subtotals for Regions and
Grand Totals)
Outline Level 4 = Subtotals for the Issues (and Subtotals for Regions and
Offices and Grand Totals) and no Detail rows
Outline Level 5 = All Detail rows and all Subtotal and Grand Total rows

What I got was the data counts properly tallied, but the third and fourth
level outline (and associated groups) were all messed up:
Outline Level 1 = Grand Totals
Outline Level 2 = Subtotals for the Regions (and Grand Totals)
Outline Level 3 = Subtotals for some Offices, Subtotals for some Issues (and
Subtotals for Regions and Grand Totals)
Outline Level 3 = Subtotals for the Issues (and Subtotals for Regions and
Offices and Grand Totals) and some Detail rows
Outline Level 5 = All Detail rows and all Subtotal and Grand Total rows

This problem did not seem to occur in Excel 97.

Is this a known bug in Excel 2003 or are my expectations unrealistic?
Please advise.

Thanks in advance,
Ed
 
Ad

Advertisements

E

Ed

Here is another listing of the data (in comma-separated value format):

Region,columnb,Office,Employee,columne,Issue
NW,,SEA,123,,Salary
NW,,PDX,234,,Benefits
NW,,PDX,244,,Salary
NW,,PDX,212,,Salary
NW,,BOI,232,,Benefits
SW,,LAX,673,,Salary
SW,,LAX,34,,Benefits
SW,,LAX,654,,Benefits
SW,,PHX,556,,Salary
SW,,DEN,322,,Benefits
SW,,DEN,65,,Salary
East,,ATL,778,,Benefits
East,,ATL,890,,Salary
East,,ATL,768,,Salary
East,,NYC,77,,Benefits

Now it is easier to see that the detail values under the "columnb" and
"columne" columns are null.
 
Ad

Advertisements

E

Ed

Debra:

Thanks for the information. I'm running SP1 of Excel 2003 (published in
July 2004), which should have included the hotfix that you listed below
(833854, published in Feb 2004) but (upon reading the fine print) did not.
I'll contact Microsoft for the hotfix.

Ed
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top