Pivot tables are one of the best tools in Google Sheets for presenting your data in a more organized and understandable way. Perfect for reporting, data analysis, and presentation of information at meetings. However, while pivot tables are expected to update their data on their own, this may not be the case for a number of reasons.
Normally you don’t want the tool to automatically update the pivot sheet. However, if you don’t know how to manually refresh the pivot table in Google Sheets, the easiest way is to hit the refresh button in your web browser and wait for the spreadsheet to reload. This should update the pivot table as well as the spreadsheet.
However, there are some situations where updating the information in a pivot table requires more than a simple update. Depending on the scenario you are facing, simply follow the steps outlined in this guide and your Pivot Table should return to its correct state in no time.
Contents
What Causes Pivot Tables Not To Refresh?
Under normal circumstances, when you create a pivot table in Google Sheets, it automatically refreshes to show updated data. However, there are some scenarios where the PivotTable does not update even though changes have been made to the data it was extracted from. These scenarios include:
- The pivot table has a filter and may not update correctly.
- The source data retrieved by the pivot table may need to be recalculated by certain functions.
- New data may be added beyond the scope of the pivot table.
How To Refresh a Pivot Table In Google Sheets
As mentioned earlier, Google Sheets automatically updates pivot tables to limit the work you need to do on your part. However, if for some reason you want to update the table manually, it’s a pretty straightforward process. To update a pivot table in Google Sheets, simply open the spreadsheet tab and press the refresh button as described below:
- First, open a web browser instance with a tab displaying a Google Sheet containing a pivot table.
- Then make changes to the dataset or pivot table, such as adding filters.
- Click the Refresh button near the top of your browser window and wait for the tab to reload. The pivot table in Google Sheets should update with the entire spreadsheet.
Alternatively, you can press the F5 button. It acts as a shortcut to refresh your web browser tabs.
The easiest way to refresh the pivot table is to click the refresh button, but in the scenario described in the previous section, we need another way to refresh the Google Sheets pivot table. Let’s take a closer look at each one so that you can accurately display the data in your Pivot Table.
Scenario 1: Pivot Table Is Not Refreshing With Data From New Rows
One of the common problems users encounter while using pivot tables is when the table does not display data for new rows in the dataset. Therefore, when creating a pivot table in Google Sheets, you must specify the range of data that the table will retrieve.
If you don’t see data for new rows in your pivot table, it’s likely that those rows aren’t included in the table’s range. To fix this, simply edit the dataset the pivot table is derived from and add the new rows you want to appear in the table.
Additionally, to avoid this situation in the future, you can include some blank rows in your pivot table range if you plan to add more data. Suppose your pivot data currently uses only four rows, but you know it will use more rows in the future. Simply select an additional 10, 20, or even 100 empty rows when determining the data range for your pivot table.
That way, when more data is added to the dataset, the dataset itself will update properly on the table without having to manually add rows each time new information is added.
The downside of this solution is that the pivot table also shows empty rows, which can be ugly. Luckily, this is easily resolved by filtering the table to hide blank rows and show only rows that hold values.
Scenario 2: Filters Are Preventing Your Pivot Table From Refreshing Properly
When using pivot tables with filters, the data displayed in the table may not update when the information in the dataset is modified. This is one of the most obvious drawbacks of the PivotTable tool, and unfortunately there is no way around it. The only course of action in such cases is to remove the filter first, then edit the data in the dataset before adding the filter back.
Here are the steps on how to do it:
- First, hover your mouse over the pivot table and click the Edit button to bring up the pivot table editor panel from the right side of the window.
- focus on the “Filters” section of the editor and click the “x” button in the upper right corner of each added filter. Note that this will remove these filters. So it’s a good idea to make a note of the filters before continuing so you don’t have a hard time remembering which one you added the first time.
- Then go back to your dataset and make the necessary changes.
- After you have finished modifying the dataset, return to the PivotTable Editor and click the Add button under Filters to recreate any filters you removed in step 2.
Scenario 3: Certain Functions In Your Dataset May Be Barring Your Pivot Table From Refreshing
If your dataset contains formulas with functions that inherently require updating, you’ll almost certainly run into problems with your pivot table. These functions include RAND, which generates random numbers within a defined range, and TODAY, which fills a cell with the current date value.
Given how the values of these types of functions inherently fluctuate within a dataset, pivot tables are limited in that they cannot reflect these changes. The bad news here is that there is currently no solution for accurately reflecting this kind of function in pivot tables. Your best bet in situations like this is to avoid such features and look for alternative ways to achieve the desired result.
Wrapping it Up
Now you know how to update pivot tables in Google Sheets. I hope you find this guide helpful. If you have any questions, feel free to leave a comment below.
