XL5: PivotTable Wizard Creates a Blank Column and Row
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", 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", 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 :