If an Excel PivotTable is missing data, first check that its source range includes every record, then refresh it and clear any field, report, slicer, or value filters. Also confirm that the PivotTable is not simply grouping or summarizing records: it normally shows totals and categories, not one row for every source entry.
Why a PivotTable may appear to omit data
A PivotTable builds a summary from a source range or data connection. It groups records by fields such as date, product, or region, then calculates values such as sums and counts. That means a table with 500 source rows may show only a handful of rows if those records share the same categories. This is normal aggregation, not missing data.
When a category or amount really is absent, the cause is usually one of a few things: the source does not include the latest rows, the PivotTable has not been refreshed, a filter is excluding records, or Excel is treating apparently similar values as different categories. Less often, the data comes from an external connection or Data Model with its own refresh or modeling behavior.
Start by identifying what is missing. Is it a newly added record, an entire category, a date range, a total, or the individual source rows behind a total? That distinction helps you choose the right check instead of changing the PivotTable layout unnecessarily.
Check the source range first
A PivotTable based on a fixed cell range does not necessarily include records typed below that range. For example, a source might be set to A1:D200 while new transactions have been added in rows 201 through 225. Refreshing the PivotTable will update its existing source, but it will not automatically make that fixed source range larger.
- Click inside the PivotTable.
- On the ribbon, select PivotTable Analyze > Change Data Source.
- Review the range or table name shown in the dialog. Check that it covers the new records and all required columns.
- If necessary, select the full current range, choose OK, and then refresh the PivotTable.
For data that grows over time, consider converting the source range to an Excel table. Select a cell in the source data and choose Insert > Table, or use Home > Format as Table. Confirm that the table includes headers. A PivotTable based on an Excel table can use the table as its source; when rows are added to the table, refreshing the PivotTable can include them without manually extending a cell address. This is more reliable than guessing how many future rows to include.
Check the source columns too. If the PivotTable needs a field that was added after the source was created, the source definition may not include that column. A row of blank cells inside a dataset can also make a manually selected range end earlier than expected, especially if the PivotTable was initially created from a selected block.
Refresh the PivotTable and its connection
A PivotTable does not always update immediately when its source cells change. To refresh an individual PivotTable, click inside it and choose PivotTable Analyze > Refresh. To refresh workbook connections and PivotTables, choose Data > Refresh All. A refresh updates the PivotTable from its defined source; it does not fix an incomplete source range or remove filters.
After refreshing, look for an error or a prompt. A PivotTable connected to an external database, text file, or another workbook depends on that source being available and on the connection being able to update. If the source file was moved, credentials changed, or a query failed, the displayed results may remain old or the refresh may not complete. Check the connection or query status rather than assuming that the PivotTable itself is broken.
For an external source, use Data > Queries & Connections to review workbook queries and connections. The available controls depend on how the PivotTable was created and what the connection supports. If a refresh is set to run when the workbook opens, that setting can help keep results current, but it does not guarantee that the external source is reachable at opening time.
Clear filters, slicers, and hidden selections
Filters are a common reason a PivotTable shows only part of its data. Check the dropdowns beside row and column labels, any Report Filter above the table, and all connected slicers or timelines. A filter can be active even when the visible layout makes it easy to overlook. A slicer may also control more than one PivotTable, so changing it can affect multiple summaries.
- Open each field dropdown and check whether particular items are unchecked or a label, date, or value filter is applied.
- For a field dropdown, use Clear Filter From [field name] when that option is available.
- For a PivotTable-wide reset, select the PivotTable and choose PivotTable Analyze > Clear > Clear Filters.
- Click each slicer and use its clear-filter button, usually shown as a funnel with a red mark, to remove that slicer’s selection.
Also inspect value filters such as Top 10 Items, Greater Than, or Does Not Equal. These do not delete source records; they hide categories that fail the condition. A filter on a date field may be harder to spot if the field has been grouped into years, quarters, or months. Clear the relevant filter and compare the result before rebuilding the report.
Understand what the layout is showing
PivotTables summarize records according to the fields placed in Rows, Columns, Values, and Filters. Suppose the source has several sales transactions for the same product and region. If Product and Region are in Rows and Sales is in Values, Excel displays a combined sales amount for each product-region pair. The individual transactions are still in the source, but they are represented by a total.
To inspect the records behind a numeric result, double-click that value in the PivotTable. Excel generally creates a new worksheet containing the underlying records available for that result. This is often called showing details. The drill-down result is useful for investigation, but it is not a substitute for checking the source or the PivotTable’s filters. If the PivotTable uses a Data Model or certain OLAP connections, the detail behavior can differ or be restricted.
If you want individual source records displayed rather than totals, include a field that distinguishes each record, such as a transaction ID, in the Rows area. This changes the granularity of the report and can make it much longer. Adding every detail field may be appropriate for an audit list, but it can make a summary harder to read. A normal worksheet filter or a separate detail sheet may be a better choice when the goal is to browse all raw records.
Look for inconsistent categories and data types
Values that look alike may not be identical to Excel. A category might appear as North in some rows and North followed by a space in others. Excel can list those as separate items. Likewise, dates stored as real Excel dates may not group with dates stored as text, and numbers stored as text may behave differently from numeric values in summaries.
Inspect the source column when a category seems split, blank, or missing. Compare the underlying entries, not only their displayed formatting. Clean inconsistent spaces and standardize dates or numbers in the source, then refresh. Be careful with bulk cleanup: two categories that look similar might be intentionally different, so confirm the intended values before replacing them.
Blank source cells can appear as a (blank) item in a field. That does not necessarily mean Excel has lost a category; it may mean some records have no value in that column. If a value field displays Count instead of Sum, Excel may be encountering text or blanks in that field, or its summarization setting may have been changed. Right-click a value and choose Summarize Values By to review the available calculation. Choosing Sum will not turn text values into numbers, so correct the source data if the values are stored as text.
When items with no data are involved
The option to show items with no data can make categories appear even when they have no matching records in the current PivotTable view. Depending on the field and source, you can check field settings by right-clicking a field item and choosing Field Settings, then reviewing the Layout & Print options. The exact options available vary with the PivotTable source and layout.
This setting does not retrieve omitted source records, expand the source range, or override a filter. It only affects whether certain categories without matching data are displayed. For example, showing every month in a reporting period can make an empty month visible, but it cannot create sales transactions for that month. Treat it as a presentation option, not a data-recovery tool.
For PivotTables built from the Data Model or an OLAP source, available field settings can differ from those for a standard range-based PivotTable. If a category is missing from the source dimension itself, changing the display setting will not add it; the underlying model or connection must provide that item.
A practical troubleshooting order
Use this sequence to isolate the problem while keeping the original report intact:
- Compare the source data with the report and write down one specific record or category that should appear.
- Check PivotTable Analyze > Change Data Source to confirm the relevant row and column are included.
- Choose PivotTable Analyze > Refresh, or Data > Refresh All for a workbook using connections.
- Clear field filters, report filters, slicers, timelines, and value filters, then check whether the missing item appears.
- Check whether the field is being grouped or summarized, and double-click a result if you need to inspect its underlying records.
- Review source values for blanks, inconsistent spelling, extra spaces, text-formatted numbers, or dates stored as text.
- If the source is external or uses the Data Model, verify that its connection or query refreshed successfully.
For a high-stakes report, save a copy before changing the source or rearranging fields. Compare a small known sample against the underlying records after the fix. That simple check can catch a corrected display that still excludes data for a different reason.
Frequently Asked Questions
Why is my PivotTable not updating when I add new rows?
It may be using a fixed source range that stops before the added rows. Check PivotTable Analyze > Change Data Source, extend the range if needed, and refresh. An Excel table used as the source can make future row additions easier to include.
Why does Refresh not bring back the missing data?
Refresh rereads the source currently assigned to the PivotTable. It will not extend an incomplete range, remove an active filter, or repair a failed external connection. Check those separately, then refresh again.
Why does my PivotTable show totals instead of every row?
A PivotTable is designed to group and summarize records. Add a unique record identifier to Rows to distinguish individual entries, or double-click a value to create a detail sheet when that option is supported.
How do I remove all filters from a PivotTable?
Click inside the PivotTable and select PivotTable Analyze > Clear > Clear Filters. Also clear connected slicers or timelines separately if they continue to limit the report.
Why is a category missing even though it is in my source?
Check that its row is inside the source range, refresh the PivotTable, and inspect field filters. If it still does not appear, compare the source spelling and data type with similar entries; extra spaces or text-versus-number differences can create separate items.
Related
- Power Query Reference: What It Does and When to Use It
- Excel Data Validation: Can You Select Multiple Items?
- Protect Sheet in Excel: Allow Editing Specific Cells
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Histogram Tool: Create, Read, and Customize ChartsIn Excel for Microsoft 365 on Windows, select a column of numeric data and choose Insert > Insert…
- Box Plot in Excel: Show Outliers and Read the ChartYes. In Excel for Microsoft 365 on Windows, select your data, choose Insert > Insert Statistic Chart >…
- Excel Not Generating Charts? Fix the Common CausesIf Excel is not generating a chart, first select a clean range that includes the labels and values,…
- Create a Horizontal Line in an Excel Chart: MethodsTo create a horizontal line in an Excel chart, add another data series whose value is the same…
- Excel Combo Pivot Chart: How to Build One in Microsoft 365Excel does not support a true combo chart as a PivotChart: you cannot combine chart types such as…
- Excel Chart X-Axis: Group Categories and Fix LabelsTo group categories on an Excel chart’s x-axis, arrange the category labels in adjacent columns—for example, Region and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.