Change Values in a Pivot Table

Change Values in a Pivot Table

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.

changevalues00

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:
  1. 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.
  2. On the Ribbon’s Options tab, in the Tools group, click Formulas, and then click Calculated Item.
  3. As a name for the calculated item, type Samples.
  4. Leave the default formula of =0, and then click OK.

changevalues01

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:
  1. In the pivot table, select one of the calculated item cells
  2. Type the number of samples you sent to that store, and press the Enter key
  3. The totals will change, to include the typed numbers.

changevalues02

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:

___________

12 thoughts on “Change Values in a Pivot Table”

  1. For the record, in excel 2010, to get to calculated item use the Option tab, but in the Calculations Group, click the “Fields, Items, & Sets” drop down and choose Calculated Item.

  2. This is not working for me. I use Excel 2010 and following the steps outlined but it still does not allow me to enter an item/number in the pivot table. I am trying to use the pivot table for review then allow the user to enter their budget numbers in a columns within the pivot table. Any help is appreciated. Thank

  3. Thanks very much for your post: Change Values in a Pivot Table. The ability to change values opens up new possibilities with pivot tables. I tested it and found that while I could change the value, I was not able to use Excel formulas to calculate the new value. For example, I’ve created a table with Actual Sales (row 1), Sales Forecast (row 2)and a Revised Sales Forecast (row 3, a calculated item equal to row 2) by month. I’d like to change the the Revised Sales Forecast by entering a excel fomula of 10% of Sales Forecast, (row 2). But of course I get an error. Is there a way around this error?

  4. Debra – I came across this post about 2 weeks ago and have found that it works great. I wanted to include this feature in a process here but my only hang up is that after I have entered my values into the table, it’s use is really temporary. If the data source changes either adding items or removing them the values I’ve entered now show up all over the table. I know there is no “one-to-one” relationship since I entered the new values but do you think there is anyway around this?

  5. Debra – I tried this with Excel 2007, but I still receive the known error message. Does it work with Excel 2007? Maybe you can upload you sample sheet

  6. Running Excel 2010, and this doesn’t work. I get the same error message saying: “You cannot edit the formulas in data cells for groups or for calculated fields.”
    I was really hoping that this would work as I want the pivot table to be used as a template showing actual results and allowing the users to enter forecast estimates for future months.

  7. I use Excel 2013 at work, and tried your method. It didn’t seem to be working.
    Could you please post a sample spreadsheet?
    Thanks

  8. Debra: have you ever seen any code that actually updates the back end data if someone over-types a PivotTable cell? I’m working on something similar, whereby you can enter data into a Notes column (rowfield, not data field) and A) it lets you add notes into the actual PivotTable on the fly while B) also updating the source data. I’d love to know if anyone else has accomplished the same.

  9. Jeff,
    I haven’t used it, but I think Power Planner provides the functionality that you’re looking for:
    http://www.power-planner.com/
    I’m doing something similar to what you’re doing. When someone double-clicks a cell, I’m using VBA to update the SQL data source based on a hidden key column. Then the pivot table will need to be refreshed in order to pickup the change.
    Tim

  10. I had a similar problem today and came looking for a solution. Within minutes this easy solution came up for me.
    1. copy your file from pivot table report
    2. Use Paste Special in a new excel worksheet—and either paste value only or value with format (one after the other – you can always paste format first, go back again and paste value, if you don’t want to lose the appearance of your table in the pivot table report)
    So easy and it worked for me!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.