To refresh a pivot table in Google Sheets, click anywhere inside the pivot table, then click the refresh icon (circular arrow) in the pivot table editor panel, or go to Data > Pivot table refresh. If you have multiple pivot tables, refresh them all at once by clicking the arrow next to the refresh icon and choosing “Refresh all pivot tables.”
Why a Pivot Table Doesn’t Update Automatically
Unlike Excel, Google Sheets pivot tables do not automatically recalculate when the underlying source data changes. This is by design—pivot tables are a snapshot of your data at the time of the last refresh. When you add, delete, or modify rows in the source range, the pivot table remains unchanged until you explicitly refresh it. This behavior prevents the pivot from slowing down your sheet during heavy data entry, but it also means you must refresh manually or set up a trigger.
How to Refresh a Single Pivot Table
There are three ways to refresh one pivot table:
- Click the refresh icon in the pivot table editor panel (the panel that opens when you click on the pivot table).
- Go to the menu: Data > Pivot table refresh.
- Right-click inside the pivot table and choose Refresh pivot table.
After refreshing, the pivot table will immediately show any changes in the source data, including new rows, edited values, or removed entries. Note that if you added new columns to the source, you may need to update the data range in the pivot table editor (under “Data range”) to include them.
How to Refresh All Pivot Tables at Once
If your sheet contains multiple pivot tables (for example, one per summary tab), refreshing each individually is tedious. To refresh all at once:
- Click on any pivot table to open the editor.
- Click the small arrow next to the refresh icon (top of the editor panel).
- Select Refresh all pivot tables.
This action refreshes every pivot table in the spreadsheet, regardless of which sheet it lives on. There is no built-in keyboard shortcut for this, but you can create a custom menu with Google Apps Script if you need it frequently.
Using Google Apps Script to Auto-Refresh on Edit
If you want the pivot table to refresh automatically whenever the source data changes, you can write a simple script. This is not a built-in feature, but it’s a common workaround.
function onEdit(e) {
// Refresh all pivot tables in the spreadsheet when any edit occurs
var sheet = e.source.getActiveSheet();
var pivotTables = sheet.getPivotTables();
pivotTables.forEach(function(pivot) {
pivot.refresh();
});
}
This script refreshes only the pivot tables on the edited sheet. To refresh all pivot tables in the entire spreadsheet, you would need to loop through all sheets. Note: This script runs on every edit, which can slow down your sheet if you have many pivot tables. Use it with caution. To install, go to Extensions > Apps Script, paste the code, save, and close. The script runs automatically on edits.
Refreshing Pivot Tables in Excel vs. Google Sheets
Excel and Google Sheets handle pivot table refresh differently. In Excel (Windows), you right-click the pivot table and choose Refresh, or use the shortcut Alt+F5 to refresh the current pivot and Ctrl+Alt+F5 to refresh all. Excel also has an option to refresh the pivot table when the file opens. Google Sheets has no such open-file refresh option, and the shortcut is not available. In Excel for Mac, the shortcut is ⌘+R (or Ctrl+R in some versions) to refresh the current pivot, but the “refresh all” shortcut is ⌘+⌥+F5. Google Sheets only offers the menu and icon methods, plus the script approach.
Common Problems and Fixes
- New rows not showing: If you add rows below the original data range, the pivot table may not include them. Check the “Data range” in the pivot editor and expand it (e.g., change A1:D100 to A1:D200). Alternatively, use a named range or convert your source to a table (Google Sheets doesn’t have Excel tables, but you can use a named range).
- Refreshing doesn’t update calculated fields: Calculated fields in pivot tables do refresh, but if you changed the formula itself, you may need to re-add the field. This is a known limitation.
- Pivot table is slow to refresh: Large data sets (tens of thousands of rows) can make refresh take a few seconds. Consider using a separate sheet for raw data and limiting the range.
- Refresh icon is greyed out: This happens if the pivot table is not selected. Click on the pivot table first, then the icon should become active.
Frequently Asked Questions
Does Google Sheets auto-refresh pivot tables?
No, Google Sheets does not automatically refresh pivot tables when the source data changes. You must refresh manually or use a script to trigger a refresh on edit.
Why is my pivot table not showing new data?
Most likely because the data range in the pivot table editor does not include the new rows. Expand the range to cover all data, or use a named range that automatically expands (e.g., using OFFSET or a dynamic named range). Then refresh.
Can I refresh a pivot table with a keyboard shortcut?
There is no built-in keyboard shortcut in Google Sheets for refreshing pivot tables. You can use the menu (Data > Pivot table refresh) or the icon. In Excel, the shortcut is Alt+F5 (Windows) or ⌘+R (Mac).
How do I refresh all pivot tables in Google Sheets?
Click on any pivot table, then click the small arrow next to the refresh icon and select “Refresh all pivot tables.” This refreshes every pivot table in the spreadsheet.
Does refreshing a pivot table affect my source data?
No, refreshing only updates the pivot table’s cached data. Your source data remains untouched.
Alternatives to Pivot Tables in Google Sheets
If you find the manual refresh annoying, consider using QUERY functions instead. For example, =QUERY(A1:D100, "select A, sum(B) group by A") creates a summary that updates automatically when the source data changes, because it’s a formula. Similarly, SUMIF, COUNTIF, and FILTER can provide dynamic summaries without the need to refresh. The trade-off is that QUERY formulas can be slower on large datasets, and they don’t offer the drag-and-drop grouping interface of a pivot table.
Related
- SPLIT Function in Google Sheets: How to Use It
- Shortcut Keys Save As: The Full Guide
- Is SELECT a Clause in SQL? Yes, Here’s Why
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Power Pivot: Add Rows to a Table and RefreshTo add a row to a Power Pivot table, add it to the table or other source that…
- Black Bullseye Chart in Excel: How to Create OneA black bullseye chart is usually a custom target graphic made from concentric circles; it is not a…
- Excel Stacked Bar Chart: Add, Move, and Format the LegendTo add a legend to a stacked bar chart in Excel for Microsoft 365 on Windows, select the…
- Excel Trendline Start From a Different Point?Yes. In Excel, a trendline is fitted to a chart series, so to start it at a later…
- Combo Chart in Excel: How to Create, Format, and Read OneA combo chart in Excel combines two or more chart types, commonly columns for one measure and a…
- Excel Pivot Table Refresh Button: Add One to a SheetTo add a button on an Excel worksheet that refreshes a PivotTable, insert a shape, assign it a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.