How to refresh a Pivot table

  • Thread starter Thread starter BCS
  • Start date Start date
B

BCS

I've created a Pivot table with information from an Excel worksheet. When I
update the cell values in the worksheet the Pivot table does not reflect
such change. Is their an "update" or "refresh" command that I have to
execute or do I have to build the Pivot table from scratch again?

Thanks,

Barry
 
Barry,

You are correct. Pivot tables are not dynamic, and sit around with obsolete
results when the data has changed. I know people like that too.
Right-click the pivot table, and choose Refresh. You may wish to record a
macro of the refresh, and put it in the Worksheet_selection event (presuming
the pivot table is not in the same sheet as the data). That way, whenever
the sheet is selected, the pivot table will be refreshed.
 
And if you are using a page field, include sorting it as part of the
recorded macro. New records seem to want to go to the bottom of the
page field list, at least it does for me.
 
However, this unfortunately doesn't cater for all "new data"
scenarios. If you build a pivot table from a range, say A1:D100, and
you add new rows in at the end of the range, say A101:D105, then these
new rows are not included in the pivot table using the refresh button.
You need to fire up the wizard and change the data source range to
include the appended rows (I think!) - or write some VBA code to do
it.

So basically, if the data in the original range from which the pivot
table was built changes (or, of course, rows are inserted into this
range), then the Refresh button is the way to go. But if you are, in
essence, extending the data range of a pivot table to include more
rows than it did previously, then things are not so easy... It caught
me out!

HTH

Gabhan Berry
AI Prediction and Forecasting in Excel
http://www.pececillo.com
 
Gabhan,

You're right. A good way to handle this is with a dynamic range. Works in
most situations. It's discussed in some of the MVP web sites, so I won't
rehash it here. It's a defined range, which you use in place of the range
in the pivot table.
 
Back
Top