XL5: PivotTable Wizard Creates a Blank Column and Row

ID: Q110896

The information in this article applies to:

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:

  1. Select the field names and data only. Do not include any blank rows below the data range.


  2. From the Insert menu, choose Name, and then choose Define.


  3. Type "Database" (without the question 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

  1. 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:


  2. Select the range A1:D7.


  3. From the Insert menu, choose Name, and then Choose Define.


  4. In the Names In Workbook box, type "Database" (without the question marks), and press ENTER.


  5. Select cell F1.


  6. From the Data menu, choose PivotTable.


  7. Select Microsoft Excel List Or Database and choose the Next button twice.


  8. Move the Expense field to the Column area.


  9. Move the Vendor field to the Row area.


  10. Move the Amount field to the Data area.


  11. 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

Additional query words: PT unexpected empty 


Keywords          : 
Version           : 5.00
Platform          : WINDOWS 
Issue type        :

Last Reviewed: July 29, 1999