How to Refresh a Pivot Table in Excel: Alt+F5, Refresh All and Fixes

Press Alt+F5 to refresh a pivot table in Excel, or Ctrl+Alt+F5 to refresh them all. New rows still missing? Point the source at a table, then refresh.

Founder, Glow Reports. , 4 min read. Updated
  • Excel
  • Pivot Tables
  • Reporting Workflows
New rows of data flowing into a pivot table grid through a circular refresh arrow
On this page
  1. How do you refresh a pivot table in Excel?
  2. Why is my pivot table not updating?
  3. How do you check the source range of a pivot table?
  4. What if only some of the new rows are missing?
  5. Why does a new column not show in the field list?
  6. Why do deleted items still show in the filter list?
  7. How do you make a pivot table refresh when the file opens?
  8. Why does the formatting change when you refresh a pivot table?
  9. What setup keeps the pivot table up to date every month?
  10. Do Google Sheets pivot tables need a refresh?
  11. Where does Glow Reports fit?
  12. Common questions

To refresh a pivot table in Excel, click inside it and press Alt+F5, or select PivotTable AnalyzeRefresh. Press Ctrl+Alt+F5 to refresh every pivot table and query in the workbook. If the totals still haven't moved after you pasted this month's rows, the cause is the source. A pivot table does not read your sheet. It reads a saved copy of its source, called the cache, and a refresh only rebuilds that copy from the range the pivot table points at. When the range stops above the new rows, they never arrive. Below: the shortcuts, the six causes in the order to test them, and a setup that never needs fixing.

How do you refresh a pivot table in Excel?

Click anywhere in the pivot table and press Alt+F5, or select PivotTable AnalyzeRefresh. You can also right-click the pivot table and select Refresh. To refresh every pivot table and query in the workbook at once, press Ctrl+Alt+F5 or select DataRefresh All.

Microsoft's page on refreshing pivot tables lists Refresh with Alt+F5, the right-click, and Refresh All under the arrow of the Refresh button Microsoft Support. Its refresh summary pairs Ctrl+Alt+F5 with DataRefresh All Microsoft Support, and the shortcut is on the list of Excel keyboard shortcuts as "Refresh all data in the workbook" Microsoft Support.

WhereRefresh one pivot tableRefresh all
Excel (Windows)Alt+F5 or PivotTable AnalyzeRefreshCtrl+Alt+F5 or DataRefresh All
Excel for MacPivotTable AnalyzeRefresh or Control-clickPivotTable AnalyzeRefreshRefresh All
Excel for the webPivotTableRefresh AllRefreshPivotTableRefresh All

A monthly report often holds several pivot tables and a query or two. Refresh All covers them in one step, so it is the safer habit before you send anything. To skip the step when you open the file, see the refresh-on-open setting further down.

Why is my pivot table not updating?

In most cases nobody refreshed it. A pivot table built on cells in the workbook keeps the figures of its last refresh until you refresh it again. Excel recalculates formulas on its own, and pivot tables do not follow that rule.

Microsoft is rolling out an Auto Refresh setting that updates a pivot table when its source changes. Its support page says the feature is currently available to participants of the Microsoft 365 Insider program. Where it exists, it is a button under PivotTable AnalyzeAuto Refresh, and it applies to every pivot table on the same data source Microsoft Support. Until your Excel has it, assume every change needs a refresh.

If you refreshed and the new rows are still missing, work down the list below. Each cause comes with a check and a fix.

SymptomLikely causeFix
Totals unchangedNot refreshedAlt+F5 or Refresh All
New rows missing after refreshFixed source rangePoint the source at a table
Some new rows missingRows pasted outside the tableResize the table
New column absent from the field listColumn outside the rangeExtend the source, then refresh
A field vanished after refreshColumn header renamed in the sourceDrag the renamed field back
Deleted products still in the filter listExcel retains old itemsSet retained items to None
Pivot on Power Query output shows old figuresPivot refreshed before the queryRefresh All again
Column widths or formats reset on refreshLayout options in PivotTable OptionsUntick Autofit column widths

How do you check the source range of a pivot table?

Select the pivot table, then PivotTable AnalyzeChange Data Source. The Table/Range box shows what the pivot table reads. A reference such as is fixed, and row 501 will never join, no matter how often you refresh.

Microsoft documents the dialog and the steps to type a new range Microsoft Support. Typing a bigger range fixes this month only. The lasting fix is a table:

  1. Click inside the data and select InsertTable. Keep "My table has headers" ticked.
  2. Note the table name under Table DesignTable Name, for example . Rename it so it reads well.
  3. Open PivotTable AnalyzeChange Data Source and type in the Table/Range box.
  4. Refresh.

The box now holds a name instead of an address. A table grows with the rows you add, so the next refresh picks them up. A Microsoft Tech Community thread asking why a pivot table ignores new rows got the same answer: base the pivot table on a table that grows, then refresh Microsoft Tech Community.

What if only some of the new rows are missing?

The rows sit outside the table. A table grows when you type or paste in the first row under its last row. Paste after a blank row, or a few rows lower, and Excel leaves the new block outside.

Microsoft's rule is to paste "in the leftmost cell below the last table row" Microsoft Support. To check, click the last row you pasted. If the tab disappears from the ribbon, that row is outside.

To fix it, select Table DesignResize Table and extend the range to the last row. Delete the blank rows between the two blocks first, or they show up as "(blank)" in the pivot table.

Why does a new column not show in the field list?

The column sits outside the source. A table takes in a column typed right next to its last one, so after a refresh the new field appears in the field list. With a fixed range, extend the range first. Either way, a new field never places itself: drag it into Rows, Columns or Values.

Renamed headers cause the opposite surprise. Rename "Amount" to "Net amount" in the source, refresh, and the field drops out of the pivot table, because Excel reads it as a new field. Drag "Net amount" back into Values.

When the columns change a lot, Microsoft advises building a new pivot table rather than patching the old one Microsoft Support.

Why do deleted items still show in the filter list?

The pivot table remembers them. By default Excel retains items deleted from the source, so a product you stopped selling stays in the filter dropdown long after its last sale. Change one setting and refresh.

Right-click the pivot table, select PivotTable Options, open the Data tab, and set "Number of items to retain per field" to None. Microsoft describes None as "no unique items for each field" Microsoft Support. Refresh, and the old items go. Set it once in a template and the monthly files inherit it.

How do you make a pivot table refresh when the file opens?

Tick "Refresh data when opening the file". Select the pivot table, then PivotTable AnalyzeOptions, open the Data tab and tick the box. Excel then refreshes the cache each time the workbook opens. In Excel for the web, the same switch is "Refresh data on file open" in the pane under PivotTableSettings Microsoft Support.

This helps when someone else updates the data and you only read the report. It does not help when you paste rows during the session. The paste happens after the opening refresh, so refresh again before you send anything.

Why does the formatting change when you refresh a pivot table?

Two layout options decide it. "Autofit column widths on update" resizes the columns at each refresh, and "Preserve cell formatting on update" keeps the formats you applied. Untick the first and tick the second, both under PivotTable AnalyzeOptionsLayout & Format.

Microsoft's page on the options describes it this way: Autofit fits each column to its widest value, and clearing it keeps the current width. If formatting still changes after a refresh, the same page advises a PivotTable style for the look you want to keep Microsoft Support. A style lives in the pivot table itself, so it survives a refresh where a hand-painted fill may not.

What setup keeps the pivot table up to date every month?

Load the data with Power Query into a table, build the pivot table on that table, and press Refresh All once a month. Power Query reloads the files, the table resizes itself, and the pivot table follows.

If each month arrives as a separate file in one folder, Power Query can combine the whole folder, so next month's file joins without any pasting. The steps are in how to merge Excel and CSV files with Power Query.

One catch: when a query refreshes in the background, Refresh All can refresh the pivot table before the query has finished, and the pivot table shows last month. Press Refresh All a second time, or turn off "Enable background refresh" in the query's properties under DataQueries & Connections. Microsoft describes the background refresh option on its page about refreshing external data Microsoft Support.

Grouping by month, when the dates refuse to group, has its own fix in how to create a pivot table and group it by month.

Do Google Sheets pivot tables need a refresh?

No. A Google Sheets pivot table updates whenever the source cells change. Google's help page says it "refreshes any time you change the source data cells it's drawn from" Google Docs Editors Help.

The range trap still applies. If the pivot table reads , row 501 stays out. Click Edit under the pivot table, then Select data range, and use an open-ended range such as so every new row counts. Filter out the "(blank)" row that the empty cells add.

Where does Glow Reports fit?

Each fix above works without any new tool. A table plus Refresh All keeps most monthly pivot tables honest.

The pivot table is still one part of the report. The charts, the comments and the comparison with last month are yours to rebuild each time. The steps for that are in how to make a monthly sales report in Excel.

Glow Reports skips the cache altogether. You upload this month's CSV or Excel export and ask for the report in plain language. The AI writes the SQL, builds the charts and drafts the text, and the SQL sits under each figure so you can check what was counted. Next month, you upload the new export and ask again. Reports export to PDF and stay in the workspace. The free plan gives 300 credits once, and a report costs 100 credits.

Common questions

Does a pivot table update automatically in Excel?

Not by default. A pivot table keeps the figures of its last refresh. Refresh it with Alt+F5, or tick "Refresh data when opening the file" in PivotTable Options. Microsoft's Auto Refresh setting is limited to the Microsoft 365 Insider program for now.

How do I refresh all pivot tables at once?

Select DataRefresh All or press Ctrl+Alt+F5. Excel refreshes every pivot table, query and connection in the workbook.

What is the shortcut to refresh a pivot table?

Alt+F5 refreshes the selected pivot table. Ctrl+Alt+F5 refreshes every pivot table, query and connection in the workbook. Both work in Excel for Windows.

Why does my pivot table show "(blank)" after a refresh?

The source range includes empty rows or cells. Remove the blank rows from the table, or filter "(blank)" out of the row labels.

El Mehdi Benane
Founder, Glow Reports

I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.

Upload this month's CSV or Excel file to Glow Reports and get the report with the SQL behind each figure.

Keep reading