Pivot tables are powerful tools that let you gain very useful insights into your data. A common question on a lot of Google Sheets forums is “How to Refresh Pivot Tables in Google Sheets?”
This Article Covers:
How to Refresh Pivot Table in Google Sheets
In general, Pivot tables don’t need to be manually refreshed. They get automatically refreshed when the data being pulled by the table gets changed.
However, there may still be situations where you find the data in the Pivot table not getting updated.
In this tutorial, we will take a look at some of those situations and how to correct them, so that changes to your original data get updated in the Pivot table too.
Why does the Pivot Table not get Refreshed?
If you find that your Pivot tables are not getting refreshed automatically whenever you make changes to the original dataset, it may be due to one of the following reasons:
- You may be adding new data to rows outside the Pivot Table range
- Your Pivot Table may be using filters
- Your dataset may contain formulae that need to get re-calculated, like RANDOM, TODAY, etc.
Let us take a look at each of the above situations, why they happen, and how to solve them one by one.
Problem 1: Pivot Table does not refresh when adding a new row
The Pivot table works with data from a particular range of cells in your original worksheet. If the new row you are trying to add is not within the Pivot table’s range, it will not have any effect on the Pivot table.
And that obviously makes sense, since the new range does not fall into the range that the Pivot table was built to pull data from.
Solution
When initially creating your pivot table you can use a little foresight and add some extra rows for data you are likely to add in the future.
For example, if your data currently spans over 20 rows, you can create your pivot table with 1000 rows. The extra 980 rows can remain blank, and you can later fill them in with data as and when required.
Now, as long as your data is within the 1000 rows, the Pivot table will get automatically refreshed.
Note: There is a small disadvantage to this method. When you have blank rows in your original dataset, your Pivot table will also show one blank row, which might not look very good. To do away with this, you can add a filter to the table that filters out blank rows and displays only those rows that contain a value.
Problem: Your Pivot Table uses filters
If the Pivot table uses filters, your data is not going to get updated when original data values are changed. This is a limitation of the Google Sheets pivot table features.
Solution
If you have filters in your pivot table, the only solution is to remove them, make the changes to the original data, and then add the filters back. Here are the steps you need to follow:
- Click on the cross symbol next to all the fields under the ‘Filters’ category in your Pivot table editor.
- Make the changes you need to the original dataset.
- The changes should now get reflected in the pivot table.
- Once you are done making your edits, add the filters back using the ‘Add’ button under the ‘Filters’ category (in the Pivot table editor).
Problem: The Pivot table does not refresh when the original dataset contains functions like RANDOM, TODAY, etc.
The Pivot table does not work well with functions that require refreshing. So if your original data contains functions like RANDOM, TODAY, etc, then changes to the original data will not get updated to the pivot table.
Solution
Unfortunately, there is no solution to this problem other than simply avoiding the use of these kinds of functions.
These were three ways in which you can try to ensure that your Pivot table refreshes with changes in your data.
However, issues of Pivot tables not refreshing due to the reasons stated in this chapter are quite commonplace, and Google should look into fixing the issue as soon as possible.
But before they, do, we suggest you follow our guidelines to ensure that your Pivot tables get refreshed automatically, with minimum effort.
I hope you found this tutorial useful.
Other Google Sheets tutorials you may also like:
