How to Refresh Pivot Table: Excel and Google Sheets

You finally finish building your perfect report, and a neat PivotTable shows sales by region, product, and month. So everything looks great.

Then your teammate updates the data source. You open the file again and expect that the new numbers will appear, but nothing changes.

Your totals are off. The new entries are missing. And now you’re wondering if you did something wrong.

Here’s what you need to know: Excel and Google Sheets use a Pivot cache, which is basically a stored snapshot of your data. It helps your PivotTable load faster, but it doesn’t automatically update when the data changes.

In this guide, I’ll walk you through how to refresh your PivotTables manually, whether you are working in Excel or Google Sheets.

How to Refresh a PivotTable

Let’s explore a few ways to refresh the PivotTable in Excel and Google Sheets.

Refreshing a PivotTable in Excel

There are three ways to refresh a PivotTable in Excel: 

Manual way

Here’s how you can do it manually: 

1. Click anywhere inside your PivotTable.
2. Go to the Data tab in the top ribbon.
3. Click Refresh All (or just Refresh, depending on your Excel version).

Once done, Excel updates your PivotTable using the latest data from your source sheet.

Manually refresh the PivotTable. Image by Author.

Tip: If you have more than one PivotTable of different datasets in your workbook, Refresh All updates.

Keyboard shortcuts

If you like using keys as much as I do, there’s a faster way: Press Alt + F5 to refresh the selected PivotTable.

If you need to refresh all PivotTables in your workbook, press Ctrl + Alt + F5 instead.

Auto refresh

You can also make Excel refresh your PivotTable each time you open a file:

1. Click anywhere on your PivotTable.
2. Go to Analyze > Pivot Table > Options. (or right-click on your PivotTable and select PivotTable Options)
3. Inside the PivotTable Options dialog box, go to the Data tab.
4. Check the box for Refresh data when opening the file.
5. Click OK.

Now, each time you open your workbook, Excel will pull the latest data for you.

Set the auto refresh of the pivot table. Image by Author.

Refreshing a Pivot Table in Google Sheets

In Google Sheets, Pivot tables (Google writes this with a space) update automatically when your source data changes.

For example, if you make some changes in your data and then look at your PivotTable, you will see that the PivotTable automatically reflects the changes. You don’t have to click Refresh or do anything special.

Automation Options

If you regularly update data, manual refreshing may become hard. Luckily, both Excel and Google Sheets allow you to automate the process, so your PivotTables (or Pivot Tables) stay up to date without requiring extra clicks.

In Excel

There are three ways to automate the refresh process in Excel.

VBA automation

If you’re comfortable with macros, you can use VBA (Visual Basic for Applications) to refresh PivotTables automatically:

1. Press Alt + F11 to open the VBA editor.

2. Click Insert > Module.

3. Paste this simple code:

Sub AutoRefreshPivotTables()
    Dim PT As PivotTable
    Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
        For Each PT In WS.PivotTables
            PT.PivotCache.Refresh
        Next PT
    Next WS
End Sub

4. Close the editor.
5. Now, when you open the workbook, it will automatically refresh the PivotTable 

Refresh the PivotTable using VBA. Image by Author.

Power query refresh

If your PivotTable is connected to Power Query, you can refresh both your data and PivotTables in one step: 

Go to Data > Refresh All. 

This updates your query and every connected PivotTable.

Pro tip: Under Data > Queries & Connections, on the right side, you’ll see the Queries & Connections panel. Right-click and select Properties. Here, you can schedule refreshes.

Using Power Query to update PivotTables. Image by Author.

External data connections

If your PivotTable pulls data from external sources (like a database, CSV, or web feed), Excel can update it automatically. Here’s how you can set this up:

• Go to Data > Connections > Properties.

• In the Usage tab, check Refresh data when opening the file.

• Check Refresh every n minutes if you want it to update on a timer. (Optional) 

This setup is suitable for dashboards that rely on live data or daily imports.

In Google Sheets

In Google Sheets, you don’t have to automate anything using Apps Script or add-ons because it automatically updates the PivotTable. 

If you add new columns or rows, just expand the data range or insert them between existing columns or rows, and Google Sheets will update everything on its own.

Pivot table editor in Google Sheets. Image by Author.

Troubleshooting PivotTable Refreshes

Even with everything set up correctly, sometimes your PivotTable doesn’t refresh the way you expect. Let's see some common reasons why this happens and how you can fix these issues in both Excel and Google Sheets.

In Excel

Here’s how you can troubleshoot PivotTable-related issues in Excel:

Check range references and pivot cache

If your PivotTable isn’t showing new data, your data range might not include the latest rows. To fix this:

• Click anywhere on the PivotTable. Go to Analyze > Change Data Source and confirm the range covers all your data.

• Turn your data range into a Table (Ctrl + T) because tables automatically expand the PivotTable as you add new rows after refreshing.

• If your PivotTable still doesn’t update, the pivot cache might be outdated. Try Data > Refresh All to rebuild it.

Reconnect external data sources

Sometimes your PivotTable can’t refresh because the data source it’s linked to, such as a database, Power Query file, or online sheet, has gone offline or moved:

• Go to Data > Connections.
• Check the status of each connection listed.
• If one shows as inactive or missing, click to reconnect or update the file path.
• Once the connection is restored, click Refresh All to reload the latest data.

Tip: If the data file was renamed or relocated, update the link directly in the connection properties so it doesn’t break again.

Reduce refresh lag for large datasets

If your workbook takes too long to refresh, do the following:

• Turn off automatic calculations temporarily (Formulas > Calculation Options > Manual) and then refresh.
• Split very large PivotTables into smaller ones filtered by region, product, or month.
• Remove unnecessary fields or totals that slow down processing.

In Google Sheets

Even though Pivot Tables in Google Sheets usually refresh automatically, sometimes they still don’t show the latest numbers. Let’s look at two common reasons and how to fix them.

Check the range reference

If you add new rows or columns outside the original data range your PivotTable was built on, Sheets won’t include them automatically.

For example, you added a new column called Payment Method to the right of the dataset, but the PivotTable didn’t see it:

• Open the PivotTable editor and adjust the range. 
• When creating your PivotTable, select a range that’s slightly larger than your current data.
• If you need to add new rows or columns later, insert them within the existing range rather than beyond it.

For example, if your current range is A1:C11, don’t add a new column in D. Instead, insert a new column between A and C, so the new column stays inside the range.

This way, your PivotTable will always update automatically.

Remove filters 

Sometimes your Pivot Table may not show the latest numbers even though your data has changed. This usually happens when a filter is active.

For example, you may have set a filter to only show items with sales greater than 50. Then you update an item’s sales from 25 to 130, but it still doesn’t appear in the report. That’s because the filter is blocking the view:

• Go to Data > click on Remove filter to turn it off.
• Check your PivotTable to confirm the updated entry now appears.
• Once everything looks right, reapply your filter if you still need it.

When you turn the filter off and on again, it forces Google Sheets to recheck your data and display the most recent values.

Final Thoughts 

A quick refresh can make all the difference. It keeps your reports accurate and ensures the numbers you see reflect your latest data.

You can update your tables manually, use a shortcut, or set up automation if you want to save time. Remember to enroll in our dedicated courses if you want to take your skills a step further. I recommend Data Analysis in Excel and our Introduction to Power Query in Excel courses.