If a PivotTable in Excel has stopped working, first select it and choose PivotTable Analyze > Refresh; if the result is still wrong, check the source range, filters, and data types. A PivotTable summarizes its source data, so it cannot show new or corrected information until the source is valid and the table is refreshed.
Start with the quickest checks
Before rebuilding a PivotTable, identify what “stopped working” means. It may still be functioning but showing an old result, hiding records because of a filter, or displaying an error because the source changed. A quick review often resolves the issue without changing the report design.
- Click a cell inside the PivotTable and select PivotTable Analyze > Refresh. To refresh every connection and PivotTable in the workbook, use Data > Refresh All.
- Look for filter icons on row, column, and report fields. Clear filters that could exclude the missing items, and check any slicers or timelines connected to the report.
- Confirm that the source data still exists and that its headers and columns have not been renamed, deleted, or rearranged in a way that breaks the source definition.
- Check whether newly added rows are inside the source range. If they are outside it, refreshing alone will not include them.
- Look for blank headers, errors, text values in a date or number column, and blank rows that split the source data.
These checks distinguish a refresh problem from a source-data or display problem. For example, if a new sales record appears in the source but not in the summary, refresh first; if it still does not appear, inspect the source range and filters.
How a PivotTable works
A PivotTable groups and summarizes records from a data source. In a typical sales report, each source row might represent one sale, with columns for date, region, salesperson, and amount. Dragging Region to Rows and Amount to Values produces a summary by region. Excel stores information about the source and its fields, and often keeps a PivotTable cache so it can display the summary efficiently.
This structure explains two common misconceptions. First, changing a source cell does not always update the displayed summary immediately; the PivotTable may need a refresh. Second, typing over a total inside the PivotTable is not a reliable way to correct the report. The displayed number is calculated from the source, so correct the underlying record or the calculation setup and refresh.
PivotTables can use a regular worksheet range, an Excel Table, a workbook Data Model, or an external connection. The refresh and repair options depend on which source type is in use. If the PivotTable was built from a database, Power Query result, or the Data Model, changing a worksheet range may not repair it because that range may not be its source.
Refresh the PivotTable and its source
For an ordinary PivotTable, click inside it and choose PivotTable Analyze > Refresh. The contextual tab appears when the PivotTable is selected. The Data > Refresh All command is useful when the workbook contains several PivotTables or connected queries. Refresh All can take longer because it refreshes multiple data sources, not just the selected summary.
If the source is produced by Power Query or another workbook connection, refresh the source before diagnosing the PivotTable. In Microsoft 365 for Windows, open Data > Queries & Connections to review queries and connections, then refresh the relevant query or use Refresh All. Once the source output has updated, refresh the PivotTable if it has not updated as part of that process. A failed sign-in, unavailable file, changed server, or disabled connection can prevent external data from refreshing; the PivotTable itself may be intact.
To make a standard PivotTable refresh when its workbook opens, right-click inside it, choose PivotTable Options, open the Data tab, and select Refresh data when opening the file. This setting does not fix an unavailable connection or a broken source definition. Excel for the web and Mac may present different menus or have different feature coverage; if a command or connection option is missing, open the workbook in the Microsoft 365 desktop app for Windows and use the corresponding PivotTable or Data ribbon controls.
Check and expand the source range
A PivotTable built from a fixed cell range will not automatically include records entered below or beside that range. Select the PivotTable and choose PivotTable Analyze > Change Data Source to inspect or revise the range. For example, a source set to A1:D250 excludes a new record in row 251. Expand the range to include the new row, then refresh. The range must include the header row and all intended data columns.
A practical way to prevent this particular problem is to convert a clean source range into an Excel Table. Select a cell in the data and choose Insert > Table, or press Ctrl+T on Windows and confirm that the table has headers. When records are added directly below the table, Excel generally expands the table; refreshing the PivotTable then brings those records into its source. This does not remove the need to refresh, and it does not automatically include data placed in an unrelated range.
Inspect the source headers as well. Each column should have a nonblank, distinct header, and the source should have one consistent header row. Merged header cells, duplicate names, or a title row above the actual headings can cause field names to be unclear or prevent a clean PivotTable source. If you change a header or add a column, refresh and check the field list. For a Data Model or connection-based PivotTable, use the query or model that supplies the data rather than assuming Change Data Source can replace it with a worksheet range.
Find filters, layout changes, and hidden items
A PivotTable can appear broken when a filter simply hides the expected records. Check each dropdown in the Row Labels, Column Labels, and report filter areas, as well as any slicers and timelines. A slicer can filter a PivotTable even when the field dropdown does not make the selection obvious. Use the slicer’s clear-filter control or change its selected items. For field filters, open the field dropdown and clear the filter or select the required items. Be careful: clearing a filter may reveal many records and change the report substantially.
Also verify the field arrangement in the PivotTable Fields pane. If a field was moved from Rows to Filters, or removed from Values, the report can look very different while remaining valid. Drag the fields back to the intended areas. If the pane is missing, select the PivotTable and choose PivotTable Analyze > Field List.
Grouping can make dates or numbers look missing or unexpected. Date grouping may fail when the source column contains blanks, text that looks like a date, or errors mixed with real dates. Correct the source values, refresh, and try the grouping again. If unwanted grouped labels remain, right-click a grouped item and choose Ungroup. A date stored as text is not necessarily a real Excel date, even if it looks like one in the cell.
Fix common errors and missing options
If Excel reports that a PivotTable field name is not valid, start by checking the source header row for a blank cell, merged cells, or duplicated headings. Make the headers clear and unique, then inspect the source range. If a field has disappeared from the field list after a source change, refresh the PivotTable and confirm that the source still includes that column. For some connection-based PivotTables, available fields are controlled by the source model rather than the worksheet.
If values display as a count when you expected a sum, inspect the source column. A Values field containing numbers stored as text or a mixture of numbers and text may be summarized by count, or may not sum as expected. Correct the source values so they use a consistent numeric type, refresh, and check the field’s summary setting. In the Values area, open the field menu, choose Value Field Settings, and select the intended calculation, such as Sum or Count. The correct choice depends on what each source row represents; counting records is not interchangeable with adding amounts.
If PivotTable commands are unavailable, check that the active cell is actually inside a PivotTable and that the workbook is not being edited in a mode that restricts changes. A protected worksheet can also prevent some edits. Review Review > Unprotect Sheet if the sheet is protected and you are authorized to unprotect it. If the workbook opens in Excel for the web and a feature is unavailable there, use the desktop application. Avoid deleting and recreating the PivotTable as a first fix: doing so can discard its layout, formatting, and filter selections.
Keep formatting and calculations predictable
Refreshing may make a report appear to lose formatting if the formatting was applied to individual cells rather than set to persist with the PivotTable. Right-click inside the PivotTable, choose PivotTable Options, and look on the Layout & Format tab for Preserve cell formatting on update. Selecting it can help keep formatting after refresh. Also consider using a PivotTable style from the Design tab, since a style is designed to format the report as its size changes.
Calculated results deserve a separate check. A regular worksheet formula placed beside a PivotTable may not expand or adjust as the PivotTable changes size. A calculated field, by contrast, is part of the PivotTable setup and has its own rules. If a result is wrong, confirm whether it comes from a source column, a calculated field, or a formula outside the PivotTable before changing anything. Fixing a source value will not automatically correct an unrelated formula, and changing a formula beside the report will not alter the source records.
Prevent the same problem next time
A few habits make PivotTables more resilient, especially when the source grows or is updated by other people:
- Use a well-formed Excel Table for a changing source, with one header row and no blank header cells.
- Keep dates, numbers, and text consistent within each source column; avoid mixing explanatory labels into data fields.
- When a result looks stale, refresh the source query or connection and the PivotTable, then verify the source range and filters.
- Save a copy before making major structural changes to a complex workbook, particularly one with external connections or a Data Model.
- Document important filters and the source location so another workbook user can tell whether an apparent omission is intentional.
For recurring reports, a reliable workflow is to add new records to the source Table, refresh the relevant query or connection if there is one, use Data > Refresh All, and then review the report filters and totals. Refreshing does not validate whether the underlying business data is correct, so compare key totals with the source when accuracy matters.
When a PivotTable may not be the right tool
If the report needs a fixed presentation that should not change when someone rearranges fields, a normal summary table or chart may be easier to control. If the task is simply to calculate a few totals from a stable list, worksheet functions such as SUMIFS or COUNTIFS may be more straightforward. For data that needs substantial cleanup or repeated combining from files, Power Query can prepare a consistent source before the PivotTable summarizes it.
These alternatives do not make PivotTables obsolete. A PivotTable is particularly useful for exploring categories, changing the level of detail, and summarizing a larger set of records without writing a separate formula for every group. Choose based on the report’s purpose: interactive analysis favors a PivotTable, while a fixed report may favor a carefully designed summary or chart. If the apparent failure comes from a broken external connection, changing report tools will not fix the connection itself.
Frequently Asked Questions
Why is my PivotTable not updating when I change the data?
PivotTables commonly need to be refreshed after source data changes. Select the PivotTable and choose PivotTable Analyze > Refresh. If the changed rows are outside its source range, expand the range or use an Excel Table as the source, then refresh again.
Why are new rows missing after I refresh?
The new rows may be outside the PivotTable’s defined source range. Check PivotTable Analyze > Change Data Source. If the PivotTable uses a query, Data Model, or external connection, confirm that the new rows are included in that source and that it refreshed successfully.
Why does my PivotTable show Count instead of Sum?
Check whether the source column contains numbers stored as text, blanks, or mixed types. Correct the source so the values are consistently numeric, refresh, and then review Value Field Settings to choose Sum if it is appropriate for the data.
Can I refresh every PivotTable in a workbook at once?
Yes. In Excel for Microsoft 365 on Windows, choose Data > Refresh All. This can also refresh workbook connections and queries, so it may take longer than refreshing one PivotTable and may prompt for connection credentials.
Why is the PivotTable Fields pane missing?
Click inside the PivotTable, then choose PivotTable Analyze > Field List to show the pane. If the tab does not appear, the selected cell may not be part of a PivotTable, or the feature may be limited in the app or workbook mode you are using.
Related
- Open a DB File in Excel: Methods, Limits, and Options
- Open CSV in Excel Without Losing Leading Zeros
- Office Contact List Template Excel: Build a Useful Directory
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Column Chart Side Wall: What It Is and How to FormatIn Excel, a side wall is a background plane on a three-dimensional (3-D) chart; an ordinary two-dimensional (2-D)…
- Excel Waterfall Chart Totals: How to Add and FormatTo show a total in an Excel waterfall chart, right-click the bar that represents the total and choose…
- Radar Chart in Excel: How to Create and Format OneTo make a radar chart in Excel for Microsoft 365 on Windows, arrange your categories in the first…
- Excel Gantt Chart Template Monthly: Build One That WorksYes. You can make a monthly Gantt chart template in Excel by listing each task with its start…
- How to Build a Funnel Chart in Excel: Step-by-StepTo build a funnel chart in Excel for Microsoft 365 on Windows, arrange process stages in one column…
- Excel Gantt Chart Scrolling Increment and Setup GuideIn Excel, a Gantt chart does not have a built-in scrolling-increment setting. To move a timeline by a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.