Updating source data for pivot table

Maybe you get a weekly report that needs to be added each week.

Instead of recreating the pivot table, you can simply refresh it.

The macro adds a sheet to your active workbook, showing a list of the file’s named ranges.

Then, the macro prompts you to enter one of those names, as the new data source for all the pivot tables.

In the following guide, we explore how to update the Pivot Table after the source data changes.

There are a variety of reasons you might need to update the pivot table.

It’s a really useful formula for setting up dynamic ranges as you can vary the height and width on the result of another formula, in our example above this other formula is COUNTA which sets the height.

If you’d rather see how it’s done, instead of reading the instructions, watch this short video. I'm using Libre Office Calc on Mac OS Yosemite, all English.I have a very simple data set: column A is a date-time (YYYY-MM-DD HH: MM: SS) and column B is a quantity (number).Maybe there were errors in the source data that needed to be corrected. Let’s say you had the following spreadsheet: And you created this pivot table for it: Then your manager informs you of a price correction on the last 4 items. For such a minor correction, it would be a waste of time to create a whole new pivot table.Instead, you will: You can also use the keyboard shortcut Alt F5 to perform this task.Refreshing the pivot table will not pull in the extra days data as the data range is still fixed.You can update this by clicking on the Pivot Table and then choosing Options Change Data Source, but it’s an additional task to remember and if you have multiple pivot tables pulling from the same data range it is quite time consuming.Or if you’ve inherited the pivot table file from someone else, you might not even be able to find the source data, in a large workbook.To help with these problems, I’ve added a new page on my Contextures site, with tips for finding and fixing the source data for a pivot table.After you create a pivot table, you might add or change records in the source data.Sometimes the pivot table does not update correctly, to show the new data.


Leave a Reply