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:
- 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", 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:
- Select the range A1:D7.
- From the Insert menu, choose Name, and then Choose Define.
- In the Names In Workbook box, type "Database", 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
Additional query words: PT unexpected empty
Keywords :
Version : 5.00
Platform : WINDOWS
Issue type :
Technology :
Last Reviewed: November 19, 1999 |
