To refresh a PivotTable in Excel for Microsoft 365 on Windows, click anywhere inside it, then select PivotTable Analyze > Refresh. To update every PivotTable and data connection in the workbook, select Data > Refresh All. Refreshing makes the PivotTable read the latest available data from its source; it does not, by itself, add rows that fall outside a fixed source range.
What refreshing a PivotTable does
A PivotTable summarizes source data, such as a list of orders grouped by product, month, or salesperson. Excel stores information about that source and uses it to build the summary. When the source changes, the displayed totals and categories may remain unchanged until the PivotTable is refreshed. Refresh asks Excel to retrieve the current source data and update the summary.
For example, if a source list contains 40 orders and you add a 41st order, refreshing can update the PivotTable’s totals—provided the new row is within the source the PivotTable knows about. If the source is a fixed range that stops at row 41, a new order entered on row 42 is outside that range and will not appear just because you refreshed.
Refresh is different from changing the PivotTable layout. Dragging a field into Rows, Columns, Values, or Filters changes how Excel displays the data; refreshing updates the data being summarized. It is also different from recalculating formulas in the source. If a source formula has not recalculated, refresh may read its existing result rather than the result you expected.
Refresh one PivotTable step by step
- Click a cell anywhere inside the PivotTable. The contextual PivotTable Analyze tab appears on the ribbon.
- Select PivotTable Analyze > Refresh. In some ribbon layouts, the command appears in the Data group.
- Wait for Excel to finish updating, then check the totals and labels that matter to your task.
You can also right-click inside the PivotTable and choose Refresh. This is useful when the ribbon is collapsed or you are already working in the table. In Excel for Mac, click the PivotTable and use its contextual PivotTable tab’s Refresh command; the tab and group names can differ slightly by version. In Excel for the web, select the PivotTable and use the available refresh command on the ribbon. Some external connections or data sources may require the desktop app or may not support refresh in the web version.
If the workbook has several PivotTables and you want all of them updated, use Data > Refresh All on Windows. This command refreshes workbook connections as well as PivotTables that depend on them, so it can take longer than refreshing one table. In Excel for Mac, look for Data > Refresh All; availability and connection options depend on the data source and Excel version.
Make new source rows appear automatically
For a PivotTable built from data on the same worksheet, converting the source list to an Excel Table is often the simplest way to avoid a range that stops short. Click in the source data, choose Insert > Table, confirm the range and whether it has headers, and select OK. Then create a PivotTable based on that Table, or change an existing PivotTable’s source to the Table. When you add rows directly below the Table, Excel generally expands it; refreshing the PivotTable can then include those rows.
A Table does not make refresh automatic in every situation: the PivotTable still needs to be refreshed to show the latest summary. Also, adding a row somewhere outside the Table may not expand it. Check that the new record is part of the Table by clicking in the source and looking for the Table’s formatting or Table Design tab.
If the PivotTable uses an ordinary cell range, inspect its source. Click inside the PivotTable, select PivotTable Analyze > Change Data Source > Change Data Source, and review or select the intended range before confirming. The exact dialog can vary depending on how the PivotTable was created. If the PivotTable uses the workbook Data Model or an external connection, its source may not be editable as a simple worksheet range; update the relevant model or connection instead.
Refresh automatically when opening the workbook
If you regularly receive an updated source file, you can ask a PivotTable to refresh when its workbook opens. Right-click inside the PivotTable, choose PivotTable Options, open the Data tab, select Refresh data when opening the file, and choose OK. The setting is useful for a workbook that should show current source data each time it is opened, but it does not solve a missing or inaccessible source.
For a workbook with multiple connections, review the connection’s own properties as well. Connection refresh behavior can depend on its type, credentials, network access, and whether the source is available. Automatic refresh is not a guarantee that Excel can reach a server, sign in, or update a source that requires a prompt. If other people will use the workbook, make sure they have access to the source and understand that opening the file may trigger a refresh.
Why a refresh may not show the change
When refreshed results look unchanged, check the source and the PivotTable settings before rebuilding the report. These are common causes:
- The new row is outside the source. A fixed range does not automatically include data entered below its last row. Expand the source or use an Excel Table as the source.
- The source data itself has not changed. Confirm that the edited value is in the correct sheet, file, table, or connected source. A PivotTable cannot show a change that was made in a different copy of the data.
- A filter is hiding the item. Check report filters, row and column filters, slicers, and timelines. A refreshed category can still be excluded by an active filter.
- The source column headers or structure changed. PivotTables rely on a clear header row and consistent columns. Blank or duplicate headers, deleted columns, or a changed source layout can prevent a refresh or alter the available fields.
- The connection did not update. A password, network, permission, or server issue can block an external refresh. Read any message Excel displays and confirm that the source is accessible.
- The displayed result is formatted or summarized differently. A field may use Count rather than Sum, or number formatting may round the displayed value. Check the field settings and the underlying values before concluding that the data is missing.
Excel can also retain items that no longer occur in the source in a PivotTable’s filter lists. Those old labels are not necessarily current records. To review this behavior, open PivotTable Options, select the Data tab, and inspect the setting for retaining items deleted from the data source. The available choices and exact wording can vary by PivotTable type and Excel version.
Refresh, Refresh All, or change the source?
Choose the command that matches the issue. Refresh updates one selected PivotTable and is usually the quickest choice when you know which report needs updating. Refresh All is useful after a source file or connected dataset changes and several reports may depend on it. Because it can update multiple connections, it may take longer or prompt for access.
Change Data Source is the right step when the PivotTable’s current source range or source table is wrong or incomplete. It is not a replacement for refresh: after correcting the source, refresh the PivotTable so it reads the intended data. Conversely, repeatedly choosing Refresh will not fix a source range that excludes the new records.
One more distinction matters: a PivotTable connected to Power Query, an external database, or the Data Model may rely on a sequence of updates. Refreshing the PivotTable alone may not re-run every upstream import or query in the way you expect. Use Data > Refresh All when you need the workbook’s connections and dependent reports updated together, and check the query or connection status if the result remains old.
Good habits for reliable PivotTable updates
A few small practices make refreshes more predictable, especially in workbooks used for recurring reports:
- Keep one clear header row, with a distinct name for every column and no merged header cells.
- Use consistent data types in each column—for example, store dates as dates and sales amounts as numbers rather than mixing numbers with text.
- Use an Excel Table for a source list that grows over time, and create or repoint the PivotTable to that Table.
- After refresh, check a few totals against the source and verify that important filters are in the intended state.
- Save the workbook after updating it, especially if other people rely on the saved PivotTable results.
These checks do not replace refresh; they help ensure that Excel has a well-structured, accessible source to read and that the result is interpreted correctly.
Frequently Asked Questions
How do I refresh all PivotTables in Excel at once?
Select Data > Refresh All. This updates workbook connections and dependent PivotTables where supported. If you only need one PivotTable updated, click inside it and choose PivotTable Analyze > Refresh.
Why is my new data not showing in the PivotTable?
The new rows may be outside the PivotTable’s source range, hidden by a filter, or in a source connection that has not updated. Check the source range or Table, then refresh. If the range is correct, inspect filters and any connection error messages.
Does a PivotTable refresh automatically when source data changes?
Usually, an ordinary PivotTable does not update its displayed summary immediately after each source edit. You can refresh it manually or enable Refresh data when opening the file in PivotTable Options. That setting applies when the workbook opens; it is not the same as refreshing after every edit.
Can I refresh a PivotTable in Excel for the web?
Excel for the web offers refresh options for supported PivotTables and sources, but not every external connection can be refreshed there. If the command is unavailable or the connection needs desktop features or credentials, open the workbook in Excel for Windows or Mac and refresh it there.
Will Refresh All update Power Query data too?
Refresh All is the workbook-level command for refreshing available connections and related reports, including supported queries. The exact result depends on connection settings and source access. If a PivotTable remains unchanged, check the query’s refresh status and confirm that it loads to the source used by that PivotTable.
Related
- Break External Links in Excel: Steps and Side Effects
- Break External Links in Excel: Safe Ways to Remove Them
- Border Not Showing in Excel? Causes and Fixes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Trendline in a Cell: Formula or Sparkline?Excel cannot put a chart’s native trendline object inside a worksheet cell. For a one-cell numeric estimate, enter…
- Pivot Chart Colors by Value in Excel: Setup GuideExcel PivotCharts do not automatically change each data point’s color according to its value, and worksheet conditional formatting…
- Power Pivot Sort by Column: How It WorksIn Excel for Microsoft 365 on Windows, set a Power Pivot sort column by opening the model in…
- Interactive Excel Dashboard: How to Build OneYes. In Microsoft Excel for Microsoft 365, a practical interactive dashboard usually combines a clean source table, PivotTables…
- Excel Pivot Table: How to Remove a Calculated FieldTo remove a calculated field from an Excel pivot table, click anywhere inside the pivot table, go to…
- Power Pivot in Excel for Mac: What You Can and Can’t DoNo: Excel for Mac does not include Power Pivot, so you cannot create or manage a Power Pivot…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.