XL5: PivotTable Wizard Creates a Blank Column and Row

Last reviewed: September 12, 1996
Article ID: Q110896

The information in this article applies to:

Microsoft Excel for Windows, version 5.0

SUMMARY

When you create a pivot table from a range defined as Database, you may create a column and row called (blank).

This is by design.

WORKAROUND

To work around this problem, do the following to redefine the database range:

Select the field names and data only. Do not include any blank rows below the data range.
From the Insert menu, choose Name, and then choose Define.
Type "Database" (without the quotation marks), and press ENTER.

MORE INFORMATION

Databases created in Microsoft Excel versions 2.x, 3.0, and 4.0 often contain a blank row below the data. By definition, a database contains the field names, the data, and a blank row. The PivotTable Wizard interprets the blank row as a separate field with no name and blank value fields.

NOTE: By default, the value field will be counted rather than summed.

Steps to Reproduce Behavior

Enter the following data into a new worksheet:

      A1: DATE       B1: EXPENSE    C1: AMOUNT     D1: VENDOR
      A2: 1/1/94     B2: overhead   C2: $1,000     D2: A.B. Properties
      A3: 1/5/94     B3: overhead   C3: $566       D3: Ace Power & Light
      A4: 1/6/94     B4: overhead   C4: $600       D4: Wheelin's Gas Co.
      A5: 1/10/94    B5: overhead   C5: $200       D5: Ralph J Cook Garbage
      A6: 1/15/94    B6: overhead   C6: $440       D6: City of Franklin
      A7:            B7:            C7:            D7:

Select the range A1:D7.
From the Insert menu, choose Name, and then Choose Define.
In the Names In Workbook box, type "Database" (without the quotation marks), and press ENTER.
Select cell F1.
From the Data menu, choose PivotTable.
Select Microsoft Excel List Or Database and choose the Next button twice.
Move the Expense field to the Column area.
Move the Vendor field to the Row area.
Move the Amount field to the Data area.
Choose the Finish button.

The resulting pivot table has a column and row titled (blank) that is filled with zeros. The Data area reflects a count of the amounts instead of a sum of the amounts.

REFERENCES

"User s Guide," version 5.0, Chapter 24, "Creating a Pivot Table" "User s Guide 1," version 4.0, page 306 "User s Guide," version 3.0, page 348 "Reference Guide," version 2.x, page 165

For more information about How a Pivot Table Works, choose the Search button in Help and type:

    Pivot

KBCategory: kbusage
KBSubcategory:
Additional reference words: 5.00 PT unexpected empty

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: September 12, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.