A finance team once spent half a day convinced their accounting system had lost transaction records, when the actual problem was a pivot table source range that had simply not been updated to include several months of additional data that had been added below the original selection.
This kind of panic is completely understandable and completely avoidable once you know the specific places to check. Missing data in a pivot table almost always comes from one of five identifiable causes, and working through them in order resolves the vast majority of cases within a few minutes.
Cause 1: The Source Range Does Not Include New Rows
This is the most common cause by a significant margin, and it is exactly what happened to that finance team.
When you build a pivot table, Excel records the specific range you selected at that moment as the source. If new rows of data get added below that original range afterward — entered manually, pasted in, or imported from another system — the pivot table has no way of knowing about this expansion unless you explicitly tell it.
How to confirm this is your issue: Click inside your pivot table, go to PivotTable Analyze, and click Change Data Source. Look at the range shown in the dialog and compare it to where your actual data currently ends. If your data now extends further than the range shown, this is your problem.
The fix: In that same Change Data Source dialog, update the range to include all your current data, extending it down to cover any new rows, then click OK and refresh.
The permanent fix: Convert your source data into an official Excel Table using Control plus T before building future pivot tables. Tables automatically expand their range as new rows are added directly below the existing data, which means a pivot table built from a Table source will never need this manual range adjustment again, regardless of how much new data gets added later.
Cause 2: A Filter Is Active and Excluding Data
As covered in detail in the filtering tutorial, multiple types of filters can be active on a pivot table simultaneously, and they are often easy to forget about since the visual indicators (a small funnel icon, a non-"(All)" selection in a Filters box) are subtle and easy to overlook during a quick glance.
How to confirm this is your issue: Click inside your pivot table, go to PivotTable Analyze, and check whether Clear, then Clear Filters is available (not grayed out). If it is available, some filter is currently active somewhere.
The fix: Click Clear Filters to remove every active filter at once, then compare your totals to what you expected. If the numbers now match, a forgotten filter was your issue.
Cause 3: The Source Data Itself Is Missing Rows Due to a Data Type or Structural Issue
Sometimes the pivot table is correctly built from a wide-enough range with no filters active, but specific rows within that range are still effectively excluded because of a structural problem with those specific rows.
Common specific scenarios:
A blank row exists somewhere within your data range (not at the very end, but in the middle), which can cause Excel to treat everything below that blank row as a separate, unselected range when the pivot table was originally created, especially if you used the auto-detect range feature rather than manually selecting the full range.
Merged cells exist somewhere within the data range, which can cause Excel to misinterpret which row certain values belong to, effectively dropping or misattributing some data during summarization.
How to confirm this is your issue: Carefully scan your actual source data range for any blank rows or merged cells within the data (not just at the edges). This requires actually looking at the raw data directly, since this issue would not necessarily show up clearly just by checking the Change Data Source range, which might show a range that looks wide enough on paper while still containing structural problems within it.
The fix: Remove any blank rows within the data range (a blank row used purely as a visual separator should be deleted, not just left empty), and unmerge any merged cells within the data area, replacing them with individually filled values in each cell as needed. Refresh the pivot table after these structural fixes.
Cause 4: Items Were Manually Deselected and Forgotten
This relates to the Row Label and Column Label filter dropdowns specifically. Someone (possibly you, in an earlier session, or possibly a colleague sharing the workbook) may have manually unchecked specific items in a Row Label or Column Label filter, which behaves differently from the Filters box but produces a similar end result of certain categories simply not appearing.
How to confirm this is your issue: Click the dropdown arrow on your Row Labels or Column Labels header directly within the pivot table. Look at the checklist — if “Select All” is not fully checked, meaning some individual items are unchecked while others remain checked, this is your issue.
The fix: Check “Select All” at the top of that checklist to restore every item, then click OK.
Cause 5: The Pivot Table Has Not Been Refreshed Since Data Changed
This relates directly to the automatic refresh tutorial. If source data was edited, added, or corrected, but the pivot table simply has not been refreshed since that change occurred, it will continue showing the older cached snapshot rather than reflecting the update, even though nothing is actually wrong with either the range or any filters.
How to confirm this is your issue: This is often the simplest to test — just try refreshing first, before investigating any of the other four causes, since it costs nothing to rule out and is the most common simple explanation.
The fix: Right-click inside the pivot table and select Refresh, or use the keyboard shortcut Control, Alt, F5 to refresh everything in the workbook simultaneously.
A Recommended Diagnostic Order
Given how each of these causes requires a different specific check, working through them in this particular order is the most time-efficient sequence, since it starts with the fastest checks and moves toward the more involved ones only if needed:
First, refresh the pivot table (Cause 5) — this takes one click and rules out the simplest explanation immediately.
Second, check for active filters using Clear Filters (Cause 2) — also extremely fast to check and rule out.
Third, check the Row and Column Label dropdowns for any unchecked items (Cause 4) — slightly more involved but still quick.
Fourth, check the actual source range via Change Data Source and compare it to your current data extent (Cause 1) — this requires looking at your actual data to know where it currently ends.
Fifth, and only if the above four have not resolved it, carefully inspect your source data range directly for blank rows or merged cells within the data (Cause 3) — this is the most time-consuming check, which is why it comes last, but it is also less common than the other four causes in most everyday business spreadsheets.
Working through this sequence for the finance team’s “missing transactions” panic took about four minutes once we actually sat down with the specific checks in order, landing on Cause 1 — the source range simply needed to be extended to include several months of data that had been added below the original selection. No data had actually been lost at any point; it had simply never been included in the pivot table’s awareness in the first place.
Preventing This Going Forward
The single highest-value habit for preventing Cause 1 specifically (the most common cause by far) is converting source data into an Excel Table before building any pivot table from it, as mentioned above and covered in more detail in the automatic refresh tutorial. This one habit change eliminates the most frequent cause of this entire category of problem permanently, for any pivot table built from that point forward.
For Cause 2 and Cause 4, building a habit of checking Clear Filters availability before trusting any pivot table’s output, especially before presenting it to others, catches forgotten filters before they cause confusion or, worse, incorrect business decisions based on incomplete data nobody realized was being filtered out.
Which of these five causes matches what you are seeing — and have you already tried refreshing or checking for filters? Describe your specific symptom and I can help narrow down exactly which cause applies to your situation.