Excel Pivot Table Not Pulling All Data? Fix It

Charts & pivot tables · Updated November 18, 2026

If an Excel PivotTable is missing rows, first refresh it, then check whether its data source includes those rows and whether a filter is hiding them. In Microsoft Excel for Microsoft 365 on Windows, right-click inside the PivotTable and choose Refresh; if the missing records remain, inspect the source range, filters, and source data before changing anything else.

What it means when a PivotTable misses data

A PivotTable summarizes the records in its source; it is not a live view that automatically follows every edit to a worksheet. Excel stores a snapshot of the source data, called a PivotCache, and uses it to build the summary. As a result, new or changed source records may not appear until the PivotTable is refreshed. Even after a refresh, records outside the selected source, excluded by a query, or hidden by PivotTable filters will not appear as expected.

It is also worth confirming what “missing” means. A record might be absent entirely, included in a total but not shown as an individual row, grouped under another label, or represented by a blank item. A PivotTable groups matching values together. For example, 12 sales records for the same region and month may appear as one summarized row rather than 12 detail rows. A changed total can also point to a wrong calculation or data type rather than a missing source record.

Start with one specific record you expect to see. Find it in the source worksheet, note its identifying value and the row it occupies, and then check whether that value appears in the PivotTable. This makes it easier to distinguish a source-range problem from a filter or grouping issue.

Refresh the PivotTable and its source

To refresh one PivotTable, right-click a cell inside it and select Refresh. You can also select the PivotTable and use PivotTable Analyze > Refresh on the ribbon. To refresh all connected PivotTables and queries in the workbook, use Data > Refresh All. On Windows, Alt+F5 refreshes the selected PivotTable, while Ctrl+Alt+F5 refreshes all data connections. Keyboard shortcuts can differ on Mac, so use the Refresh commands on the ribbon there.

Refresh updates a PivotTable from its existing source; it does not automatically repair an incomplete source range. For example, a PivotTable based on A1:D500 will not include a record entered on row 501 just because you refreshed it. Refresh first because it is quick, but check the source if the result is still incomplete.

For a PivotTable linked to a regular worksheet range, select it and choose PivotTable Analyze > Change Data Source. Review the range shown in the dialog and make sure it covers the header row and every intended record and field. If the source should extend through row 650, but the dialog ends at row 500, select the correct range and confirm. Refresh the PivotTable afterward.

A PivotTable may also be configured to refresh when the workbook opens. Right-click it, select PivotTable Options, open the Data tab, and select Refresh data when opening the file. This setting can help keep a workbook current when it is opened, but it does not fix an incorrect source or a failed external connection.

Check whether the source range includes every record

One frequent cause is that the original source was a fixed cell range. This often happens when someone adds records below the data after creating the PivotTable. A source range such as $A$1:$D$500 will continue to stop at row 500 even if the worksheet later contains hundreds of additional rows. The same problem can affect new columns: if the source ends at column D, a new field in column E is outside the PivotTable’s source.

To inspect the source, click inside the PivotTable and open PivotTable Analyze > Change Data Source. Check both the final row and final column. Also verify that the selected range starts with the intended header row. Changing the source to include extra blank rows may be harmless, but selecting a range that includes unrelated notes or a second table can create confusing fields or records.

For data that will grow, convert the source list into an Excel Table. Select a cell in the list and choose Insert > Table, then verify that My table has headers is selected if the first row contains field names. When adding records directly below an Excel Table, Excel normally expands the table to include them. A PivotTable based on that table can then use the expanded source after you refresh it. Check that the table actually expanded if data was pasted elsewhere or separated from it by blank rows.

When the PivotTable uses an Excel Table as its source, Change Data Source should identify the table by name rather than showing only a fixed cell address. You can check or change that name by selecting a cell in the table and looking at Table Design > Table Name. Making the source a table reduces the chance of missing future rows, but it cannot include information that was entered outside the table and never added to it.

Look for PivotTable filters, slicers, and grouping

Filters can hide records even when the source and refresh are correct. Check the drop-down arrows beside row and column labels, report-filter fields above the PivotTable, and any connected slicers or timelines. A manually selected subset, a label or value filter, or an active date period can make a category seem absent. In Excel for Windows, select the PivotTable and use PivotTable Analyze > Clear > Clear Filters to clear its PivotTable filters. Slicers may need to be cleared separately using their clear-filter control.

Be careful about where a filter is applied. A filter in the PivotTable controls the summary’s displayed items. A filter applied to the source worksheet with the ordinary column filter does not, by itself, mean that hidden source rows are excluded from a PivotTable based on the full range. However, if the source was copied from a filtered list, or a query was configured to keep only certain rows, the PivotTable can only summarize the records that actually reach its source.

Grouping can make a value hard to recognize. Dates may be grouped into months, quarters, or years, and numeric fields can be grouped into intervals. A date such as 4/15/2026 may therefore appear under April rather than as a separate row. Expand the grouped field or review its grouping settings before concluding that the underlying record is missing. Also check whether the expected field is in the Filters, Columns, or Values area instead of Rows; its position changes how the result is displayed.

Check headers, blanks, and inconsistent data types

PivotTable sources work best as a single rectangular list: one header row, one record per row, and one field per column. Each column should have a distinct, nonblank header. Blank rows or columns splitting the list can lead someone to select only part of the data, while merged headers, duplicate names, or headings several rows above the records can make the source difficult to interpret. Confirm that the range starts at the real headers and contains the same columns throughout.

Inconsistent values can make records appear under unexpected categories. For instance, a product code stored as a number in some rows and as text in others may not behave like one consistently typed field. Extra spaces can also make apparently identical labels distinct: North and North followed by a space may be separate PivotTable items. Review the actual source values, standardize the field where appropriate, and refresh again.

Check the Values area if the concern is an incorrect total rather than an absent row. A numeric-looking column containing text or blanks may be summarized as Count instead of Sum, or may not contribute to a sum. Right-click a value in the PivotTable, select Summarize Values By, and confirm the intended calculation. A count can be useful for identifying how many records Excel recognizes, but it is not the same as a total of the underlying amounts.

Blank cells in a source field may appear as a (blank) item. That does not necessarily mean Excel discarded the entire record; the record may simply have no value in that particular field. Check other fields or the relevant total to determine whether the record is included. Errors in source cells, inconsistent date entries, and text mixed with numbers are also worth inspecting when only certain records behave unexpectedly.

Consider queries, external connections, and the Data Model

Some PivotTables do not read directly from a visible worksheet range. Their source may be a database connection, a Power Query result, an external file, or the Excel Data Model. In those cases, refreshing the PivotTable may not be enough if the upstream source has not refreshed, a query filters out rows, or a connection is unavailable. Use Data > Queries & Connections to review workbook queries and connections, and refresh the relevant source before checking the PivotTable again.

Power Query can intentionally remove rows through filters or transformation steps. If the worksheet table produced by a query is already missing a record, inspect the query steps in the Power Query Editor rather than adjusting the PivotTable source. For a database or shared file, check that the source has the expected record and that the workbook can access it. A refresh error, expired sign-in, unavailable file path, or permission change can prevent new records from reaching the workbook.

PivotTables built on the Data Model can combine related tables. A row from one table may not appear in a particular summary if the relationship between tables is missing or incorrect, or if a field from another table does not match the expected key. Check which table supplies each field and whether the model’s relationships connect them as intended. A worksheet range adjustment will not fix a relationship problem in the Data Model.

Older category names sometimes remain available after source items have been deleted because a PivotTable cache can retain past items. This can make a stale label appear in a filter list, even though it is no longer present in current source records. In PivotTable Options, the Data tab includes the setting for how many items to retain per field; setting it to None and refreshing can remove retained items. This issue explains lingering labels, not missing new records, so check the source range separately when new data is absent.

A practical order for finding the cause

Work from the quickest checks to the more involved ones, changing one thing at a time. This helps identify the cause and avoids accidentally altering a working summary.

  1. Find an example of a missing record in the source, and confirm it is present in the expected worksheet, query output, or external data.
  2. Refresh the PivotTable. If it relies on a connection or query, refresh that source as well and note any error messages.
  3. Open Change Data Source and verify the full row and column boundaries. For an Excel Table, confirm the record is inside the table.
  4. Clear PivotTable filters, inspect slicers and report filters, and check whether date or number grouping changes the way the record is displayed.
  5. Review the source field for blanks, extra spaces, mixed data types, errors, and inconsistent headers. Check the Values summary method if totals look wrong.
  6. If the PivotTable uses a query or Data Model, investigate its filters, refresh status, and relationships rather than treating it like a simple range-based PivotTable.

After making a correction, refresh and compare a small, known set of records or totals against the source. If the PivotTable is shared, explain which filters are active and whether the underlying source was refreshed; otherwise, another person may see a different summary and assume the data is still missing.

Frequently Asked Questions

Why does my PivotTable not update when I add a new row?

It may be using a fixed source range that ends above the new row, or it may simply need a refresh. Check PivotTable Analyze > Change Data Source, then refresh. Converting the source list to an Excel Table helps it expand when new records are added directly below it.

How do I make a PivotTable include new data automatically?

Use an Excel Table as the source so the table can expand as records are added, then refresh the PivotTable to update its summary. A PivotTable does not normally recalculate its cached results immediately after every source edit. You can also enable Refresh data when opening the file in PivotTable Options.

Why is a PivotTable missing a new column?

The new field may be outside the original source range. Open PivotTable Analyze > Change Data Source and include the column, then refresh. If the source is an Excel Table, first make sure the new column is part of the table; the field should then be available after refreshing.

Why does my PivotTable show Count instead of Sum?

Excel may be treating the value field as text, or the column may contain a mix of numbers, text, and blanks. Inspect and standardize the source values, refresh, and check the field’s Summarize Values By setting. Count tallies records with values; Sum adds numeric values.

Can a worksheet filter cause rows to disappear from a PivotTable?

A normal filter on the source worksheet does not by itself remove hidden rows from a PivotTable based on the full source range. PivotTable filters, slicers, query filters, or a source range that excludes rows can hide or omit records. Check those separately rather than assuming the worksheet filter is responsible.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.