Microsoft KB Archive/110896

{|
 * width="100%"|

XL5: PivotTable Wizard Creates a Blank Column and Row

 * }

-

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:


 * 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", 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:
 * 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", 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.