Data Associated with Blank Cells May Be Excluded from Subtotals

Last reviewed: February 12, 1998
Article ID: Q164499
The information in this article applies to:
  • Microsoft Excel for Windows, version 5.0
  • Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In Microsoft Excel 5.0 or 7.0, when create a list of data that contains empty cells, and you use the Subtotals command on the Data menu, the summary values for the data may be incorrect.

CAUSE

This problem occurs when the following conditions are true:

  • You click a cell in a list that contains blank cells and click Subtotals on the Data menu.

        -and-
    
  • In the "At each change in" box in the Subtotal dialog box, you specify a column that contains blank cells.

        -and-
    
  • You select Sum in the Use Function list, clear the Summary Below Data check box, and click OK.

When you do not clear the Summary Below Data check box, Microsoft Excel 5.0 and 7.0 includes data for the blank cells in the summary function. The value that Microsoft Excel associates with a blank cell is the value for the preceding cell that is not blank.

NOTE: The correct behavior is for Microsoft Excel to ignore the values for blank cells when you use the Subtotals command regardless of whether you select the Summary Below Data check box.

RESOLUTION

To resolve this behavior, type values in the blank cells.

For example, if you are using the following data

   A1: Name   B1: Amount
   A2: a      B2: 1
   A3:        B3: 1
   A4:        B4: 1
   A5: b      B5: 1
   A6:        B6: 1
   A7: c      B7: 1

type values for cells A3, A4, and A6 as follows:

   A1: Name   B1: Amount
   A2: a      B2: 1
   A3: a      B3: 1
   A4: a      B4: 1
   A5: b      B5: 1
   A6: b      B6: 1
   A7: c      B7: 1

When you click A1 and click Subtotal on the Data menu, Microsoft Excel calculates the following totals:

   A1:  Name        B1:  Amount
   A2:  a           B2:  1
   A3:  a           B3:  1
   A4:  a           B4:  1
   A5:  a Total     B5:  3
   A6:  b           B6:  1
   A7:  b           B7:  1
   A8:  b Total     B8:  2
   A9:  c           B9:  1
   A10: c Total     B10: 1
   A11: Grand Total B11: 6

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel versions 5.0 and 7.0 for Windows. This problem was corrected in Microsoft Excel 97 for Windows.

MORE INFORMATION

Behavior in Microsoft Excel 5.0 and 7.0

In the Subtotal dialog box, if the field you are selecting in the "At each change in" list contains empty cells, Microsoft Excel ignores the rows that contain the empty cells when you clear the Summary Below Data check box. Example:

To see this behavior, use the following steps:

  1. Type the following data in a new worksheet:

       A1: Name   B1: Amount
       A2: a      B2: 1
       A3:        B3: 1
       A4:        B4: 1
       A5: b      B5: 1
       A6:        B6: 1
       A7: c      B7: 1
    
    

  2. Select cell A1 and click Subtotals on the Data menu.

  3. In the Subtotals dialog box, click Name in the "At each change in" list, click Sum in the Use Function list, and then click Amount in the Add Subtotal To list.

  4. Click OK.

    The data appears as follows:

          A1:  Name         B1:  Amount
          A2:  a            B2:  1
          A3:               B3:  1
          A4:               B4:  1
          A5:  a Total      B5:  3
          A6:  b            B6:  1
          A7:               B7:  1
          A8:  b Total      B8:  2
          A9:  c            B9:  1
          A10: c Total      B10: 1
          A11: Grand Total  B11: 6
    
       NOTE: The blank cells in column A are associated with the value for the
       preceding nonblank cell in the column.
    
    

  5. Select cell A1 and click Subtotals on the Data menu.

  6. In the Subtotal dialog box, click to clear the Summary Below Data check box, and then click OK.

    The data appears as follows:

          A1: Name         B1: Amount
          A2: Grand Total  B2:  6
          A3: a Total      B3:  1
          A4: a            B4:  1
          A5:  Total       B5:  3
          A6:              B6:  1
          A7:              B7:  1
          A8:  b           B8:  1
          A9:   Total      B9:  2
          A10:             B10: 1
          A11: c           B11: 1
       
    
NOTE: The blank cells are not associated with cells that contain values in column A.

Behavior in Microsoft Excel 97 for Windows

In Microsoft Excel 97, in the Subtotal dialog box, if the field you are selecting in the "At each change in" list contains empty cells, the behavior is the same regardless of whether you clear the Summary Below Data check box. Example:

To see this behavior, use the following steps:

  1. Type the following data in a new worksheet:

          A1: Name   B1: Amount
          A2: a      B2: 1
          A3:        B3: 1
          A4:        B4: 1
          A5: b      B5: 1
          A6:        B6: 1
          A7: c      B7: 1
    
    

  2. Select cell A1 and click Subtotals on the Data menu.

  3. In the Subtotals dialog box, click Name in the "At each change in" list, click Sum in the Use Function list, and then click Amount in the Add Subtotal To list.

  4. Click OK.

    The data appears as follows:

          A1:  Name        B1:  Amount
          A2:  a           B2:  1
          A3:  a Total     B3:  1
          A4:              B4:  1
          A5:              B5:  1
          A6:  b           B6:  1
          A7:  b Total     B7:  1
          A8:              B8:  1
          A9:  c           B9:  1
          A10: c Total     B10: 1
          A11: Grand Total B11: 6
    
    

  5. Select cell A1 and click Subtotals on the Data menu.

  6. In the Subtotal dialog box, click to clear the Summary Below Data check box, and then click OK.

    The data appears as follows:

          A1:  Name        B1:  Amount
          A2:  Grand Total B2:  6
          A3:  a Total     B3:  1
          A4:  a           B4:  1
          A5:              B5:  1
          A6:              B6:  1
          A7:  b Total     B7:  1
          A8:  b           B8:  1
          A9:              B9:  1
          A10: c Total     B10: 1
          A11: c           B11: 1
    
    

REFERENCES

For more information about subtotals, click the Index tab in Microsoft Excel Help, type the following text

   Subtotals, data summary

and then double-click the selected text to go to the "Summarize data by using subtotals and outlines" topic.


Additional query words: XL97 sub total
Keywords : xllist kbtool
Version : WINDOWS:5.0,7.0,97
Platform : WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 12, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.