How to Refresh a Pivot Table in Google Sheets

Let’s say you manage a sales team and keep track of monthly sales data in Google Sheets. You’ve created a pivot table to summarize the total sales for each team member. One day, you update the sales figures in your source data because a few transactions were missing.

In Google Sheets, when you create a pivot table and whenever you update the existing one to the source sheet, Google Sheets is smart enough to refresh the pivot table automatically.

But when you add new data, let’s say you have data in 100 rows and now you have 50 more rows of data in the source; in this case, you need to know how to refresh the source range of the pivot table to refresh it.

Update the Range to Refresh the Pivot Table

Now, let’s understand the simple steps that you can use to refresh a pivot table with newly added data in the source sheet. This includes changing the source range…

1. First, you need to click on any of the cells in the pivot table. This will show the edit button at the bottom of the pivot table, and when you click on it, it will open the pivot table edit on the right side of the window.
   
2. In the Pivot Table editor, find the “Data range” field at the top. Click on the box containing the current data range.
   
3. Here, you get a pop-up to update the data range. You can manually type the new range or select it directly from your worksheet by selecting the range of cells. Once you’ve selected the new range, click “OK”.
   

This will add the new data to the source of the pivot table, and all the values and totals will be updated in the pivot table. But when you add more data to the pivot table, you need to perform these steps again to refresh the pivot with new or updated data.

Use Convert to Table to Automate Pivot Table Refresh Process

As I said, Google Sheets can update a pivot table when you change source data. But when you add new rows of data, then you need to change the source address to include new rows and columns in the source.

Now, in June 2024, Google Sheets introduced a new feature called “Convert to table”, and with this, you can create a table with dynamic source data that will refresh itself when you add new data to it.

1. Select any cells in the source data and then go to Format > Convert to table. This will convert your data into a table.
   
2. After that, click on the top of the table to edit the name of the table, and then by editing it, change it to a unique name that you can identify in the future, or a user can understand the data with the name.
   
3. Return to the pivot table and open the editor (as we did with the previous method). In the range source range input bar, enter the table name you created for the source data.
   

Now, whenever you add new data to the source sheet, it gets updated in the pivot table without anything that needs to be done from your end.

Note – Many people say you can use F5 to refresh a pivot table, but I don’t think it works.

Check Pivot Table Filters

Sometimes, when you apply a filter to the pivot table and add new data to the source or change source data, there might be a chance that your pivot will not show all the updated data.

In the pivot table editor, scroll down the status drop-down in the “Filter” and then click on the “Select all” to select all the values in the filter and click OK to apply it.