One of my clients uses a pivot table to summarize product sales, using sales data from their accounting system. Occasionally, they’d like to type a number in the pivot table, but Excel won’t let you change values in a pivot table. Here is a workaround for that limitation.
Pivot Table Sales Report
Occasionally, the client’s salespeople send product samples to a customer, and those sample quantities aren’t included in the sales data. They’d like to have those numbers in the printed report though, which is based on a pivot table.
For a quick solution, they’d like to enter the sample quantities in the Values area of the pivot table, instead of creating records in the source data.
However, if you try to type in a cell in the Values area, an error message appears — Cannot change this part of a PivotTable report.
Create a Calculated Item
Although you can’t type in most cells in the Values area, you can type in cells that contain calculated items. To allow manual entries for the sample quantities, you could create a calculated item with the name Samples, as described below.
To create a calculated item for Samples:
- Select one of the label cells for the product category field. If you don’t select one of these cells, you won’t be able to create a calculated item for that field.
- On the Ribbon’s Options tab, in the Tools group, click Formulas, and then click Calculated Item.
- As a name for the calculated item, type Samples.
- Leave the default formula of =0, and then click OK.
Change the Values
After you create the calculated item, it’s automatically added to the pivot table, and you can change the values.
To change a value:
- In the pivot table, select one of the calculated item cells
- Type the number of samples you sent to that store, and press the Enter key
- The totals will change, to include the typed numbers.
NOTE: If you delete the number in a calculated item’s cell, you won’t be able to make any further changes to that cell. Type a zero instead of pressing the Delete key, and you will be able to edit the cell again later.
More Pivot Table Info
For more information on pivot tables, follow these links: