Excel Pivot Table Troubleshooting Guide (2026)
Home›Excel›Pivot Tables›Troubleshooting
ExcelSub-Guide⏱ 13 min read

Pivot Table Troubleshooting: The Complete Guide

Every pivot problem worth solving — Count instead of Sum, refresh not working, blank cells, dates ungrouped, missing fields, slicers disconnected, style resetting. Root causes and fixes for the 20 most common pivot table headaches.

Data setup problems

Problem: Excel won't build a pivot from my data

Common causes:

  • Merged cells — Excel refuses to pivot data with merged cells. Home → Merge & Center dropdown → Unmerge Cells across the whole range.
  • Blank rows or columns inside data — Excel thinks the data ends. Delete blank rows or fill in blanks.
  • Missing header row — data must start with a header row. Add one.
  • Duplicate column names — every header must be unique. Rename duplicates.
  • Data on multiple sheets — pivot needs one contiguous range or table.

Problem: Sum shows as Count

Cause: Excel picks Count when the field contains any text or blank values. Even one text cell in a numeric column triggers Count.

Fix: Clean the source column so it contains only numbers. Or right-click value cell → Summarize Values By → Sum. But the underlying data issue will bite you again — better to clean it.

Problem: Dates not grouping correctly

Cause: Some "dates" in the column are stored as text, not real dates.

Fix: Select the date column. Check the number format — if it's General with left-aligned values, they're text. Data → Text to Columns → Delimited → Next → Next → Date → Finish. This forces Excel to parse them as real dates.

Refresh problems

Problem: Pivot doesn't show new data after refresh

Cause: The pivot's source range doesn't include the new rows.

Fix: PivotTable Analyze → Change Data Source → expand the range to cover new data. Or: convert source to an Excel Table (Ctrl + T) so it auto-expands. Pivots based on tables never have this problem.

Problem: Refresh gives error "PivotTable field name is not valid"

Cause: A header cell in the source data became blank, or the source layout changed (a column was deleted or reordered).

Fix: Check every source column has a header. If a column was removed, either restore it or remove that field from the pivot.

Problem: Refresh is very slow

Cause: Large source data, or too many pivots refreshing at once.

Fix: Use Excel Tables (indexed, faster). Move heavy data to Power Pivot (columnar engine, much faster than range-based pivots). Consider filtering source data before pivoting.

Formatting problems

Problem: Formatting resets on refresh

Cause: Default pivot behavior wipes formatting on refresh.

Fix: PivotTable Analyze → Options → Layout & Format tab. Tick "Preserve cell formatting on update". Untick "Autofit column widths on update". Both settings are needed.

Problem: Number format not sticking

Cause: You used Format Cells instead of Number Format.

Fix: Right-click value cell → Number Format (not Format Cells). Number Format is pivot-field-level; Format Cells is one-time.

Problem: Blank cells look wrong

Cause: Blank cells in the value area appear as empty strings by default.

Fix: PivotTable Analyze → Options → Layout & Format tab → "For empty cells, show:" enter "0", "-", or "n/a" as you prefer.

Problem: Error values show as #DIV/0!

Cause: Calculated field divides by zero (or missing data).

Fix: PivotTable Analyze → Options → Layout & Format tab → "For error values, show:" enter "-" or "n/a". Or fix the formula to handle zeros (=IFERROR(Revenue/Deals, 0)).

Slicer problems

Problem: Slicer isn't filtering the pivot

Cause: Slicer isn't connected to that pivot.

Fix: Right-click slicer → Report Connections → tick the pivot. Slicers only connect to pivots from the same data source.

Problem: Slicer connections lost after copy/paste

Cause: Copying pivots between workbooks or sheets sometimes breaks slicer connections.

Fix: Right-click slicer → Report Connections → re-tick the pivots. Or delete and re-insert the slicer.

Problem: Timeline slicer doesn't appear

Cause: The pivot has no date field, or dates were manually grouped (which disables timelines).

Fix: Ensure the source has a date column. Ungroup any manual date groupings (right-click date field → Ungroup). Insert Timeline should now work.

Calculation problems

Problem: Calculated field returns wrong result

Cause: Calculated fields compute at grand-total level using summed inputs — this breaks weighted averages and any calculation that shouldn't be sum-of-sums.

Fix: Move the calculation to source data (add a helper column) or use Power Pivot's DAX for calculations that respect filter context.

Problem: Grand total row shows a strange number

Cause: If you're using a Show Values As percentage (like % of Row Total), the grand total row shows totals of the underlying values, not percentages.

Fix: Design tab → Grand Totals → toggle off if the grand total isn't meaningful for your context. Or show two value columns — raw values with grand total, percentages without.

Problem: Ranking (Show Values As Rank) gives unexpected results

Cause: Ties in ranking, or the scope of ranking (default is per row) is different from what you intended.

Fix: Right-click value → Show Values As → Rank Largest to Smallest → check the Base field. Change if needed. If ties matter, add tiebreakers via secondary sort.

Field pane problems

Problem: Field list pane disappeared

Cause: Accidentally closed with the X.

Fix: Click inside the pivot → PivotTable Analyze → Field List. Or right-click inside the pivot → Show Field List.

Problem: Field names are cryptic (like "Sum of Revenue2")

Cause: Excel auto-names duplicates. Or you added the same field twice.

Fix: Right-click the field header → Value Field Settings → "Custom Name" → give it a clean name like "Revenue" or "Revenue %".

Layout problems

Problem: Row labels are all in one column, indented

Cause: Compact Form layout (the default).

Fix: Design tab → Report Layout → Show in Outline Form or Tabular Form. Tabular Form is best for exporting or printing.

Problem: Blank cells in row labels for nested fields

Cause: Standard nested-field display — parent labels only show on the first row of each group.

Fix: Design tab → Report Layout → Repeat All Item Labels. Every row now shows all parent labels — essential for exporting or when pivots need to look like flat tables.

Performance problems

Problem: Workbook is slow after adding pivots

Cause: Multiple pivots each caching a large dataset separately.

Fix: Base all pivots on a single source (same Excel Table). Or move to Power Pivot — one shared model, much faster.

Problem: File size ballooned after adding pivots

Cause: Pivot cache stores its own copy of source data.

Fix: PivotTable Analyze → Options → Data tab → untick "Save source data with file". Pivots refresh from external source instead of cached copy. Trade-off: file must be able to re-read the source on open.

Excel Wizard

Diagnostics for broken pivots

Some pivot problems are subtle — dates that look like dates but are text, slicers connected to the wrong pivots, formatting that resets mysteriously. Excel Wizard can inspect a pivot and diagnose the issue ("this pivot shows Count because column D has mixed data types") — often catching problems faster than manual review.

Install Excel Wizard →

Frequently asked questions

Why is my pivot table showing Count instead of Sum?

Excel picks Count when the numeric field contains any text or blank values. Clean the column so it's purely numeric. Or force it: right-click the value cell → Summarize Values By → Sum.

Why does my pivot table lose formatting on refresh?

PivotTable Analyze → Options → Layout & Format tab. Tick 'Preserve cell formatting on update'. Untick 'Autofit column widths on update'. Both are required.

How do I fix "PivotTable field name is not valid"?

Source data has a blank header, or a column was removed/reordered. Restore missing headers. Ensure every column in the source range has a unique, non-blank header.

Why won't dates group in my pivot?

Some dates in the source are stored as text. Select the date column → Data → Text to Columns → Delimited → Next → Next → Date → Finish. Now dates are parsed correctly and grouping works.