What is the fastest way to check Pivot source quality?

Convert the source to an Excel Table, confirm no blank rows or columns, verify numbers are true numbers, then refresh the Pivot.

Should I fix the source sheet or the Pivot settings first?

Fix the source first. Most wrong totals come from bad source data, not from Pivot field settings.

Why does my Pivot Table total not match SUMIFS?

Usually the Pivot source range excludes new rows, numbers are stored as text, or filters differ. Reconcile both against the same cleaned Table.

Tutorials - 2026-01-21

10 Common Pivot Table Mistakes (And How to Fix Them)

Avoid these common Pivot Table errors that trip up beginners and experts alike. Learn the fixes and best practices.

Quick answer: pivot table wrong total

Pivot totals are wrong when the source has blank rows, numbers stored as text, double-counted records, or a source range that does not include new rows. Refresh after fixing the source.
Convert the source to an Excel Table so ranges expand.
Compare Pivot total to SUMIFS on the same cleaned data.
See [Pivot not updating](/problems/pivot-table-not-updating/).

Overview

When a Pivot total disagrees with SUMIFS or your expectation, the source data — not the Pivot UI — is guilty 80% of the time. Use this audit before rebuilding the report.

Top causes of wrong pivot totals

Blank rows break the source range — use an Excel Table.
Numbers stored as text — green triangle or left-aligned numbers.
Same transaction duplicated — define a key and COUNTIFS > 1.
Filter context differs from your SUMIFS criteria.

Reconcile Pivot vs formula

Build SUMIFS on the same cleaned Table as the Pivot source.
Refresh Pivot after fixing source (Alt+F5).
Expand Change Data Source if rows were added below an old range.

Related fixes

[Pivot not updating](/problems/pivot-table-not-updating/)
[Pivot tables guide](/guides/excel-pivot-tables-guide/)

Common Pivot Table Mistakes

Even experienced Excel users make these Pivot Table mistakes. Learn what they are and how to avoid them. For the complete guide, see Excel Pivot Tables: Complete Guide.

Mistake #1: Source Data Has Blank Rows

The Problem: Blank rows in your data cause the Pivot Table to only include data above the first blank. Example: \\\ Row 1: Header Row 2: Data Row 3: Data Row 4: (blank) ← Problem! Row 5: Data ← Not included Row 6: Data ← Not included \\\ The Fix: \\\ Delete all blank rows before creating Pivot Table Use Ctrl+G → Special → Blanks to find them Or use an Excel Table (Ctrl+T) which handles this better \\\ Prevention: Always use Excel Tables as your data source.

Mistake #2: Missing or Duplicate Headers

The Problem: Missing headers → Excel uses "Column1", "Column2" Duplicate headers → Confusing field names Example: \\\ Sales Region Sales ← Two "Sales" columns! 100 North 50 \\\ The Fix: \\\ Ensure every column has a unique header Use descriptive names: "Product Sales", "Service Sales" No blank headers \\\

Mistake #3: Forgetting to Refresh

The Problem: You update source data, but Pivot Table shows old numbers. Why It Happens: Pivot Tables don't auto-refresh. They use a cached copy of your data. The Fix: \\\ Right-click Pivot Table → Refresh Or: Alt + F5 Or: PivotTable Analyze → Refresh \\\ Prevention: \\\ Right-click → PivotTable Options → Data tab Check "Refresh data when opening the file" \\\

Mistake #4: Numbers Showing as Count Instead of Sum

The Problem: Your sales numbers show "Count of Sales" instead of "Sum of Sales". Why It Happens: The column contains blank cells or text, so Excel defaults to Count. The Fix: \\\ Click any value in the Pivot Table Right-click → Value Field Settings Change from Count to Sum \\\ Prevention: \\\ Ensure number columns contain ONLY numbers Replace blanks with 0 Remove any text entries \\\

Mistake #5: Dates Won't Group

The Problem: You try to group dates by month/quarter, but Excel says "Cannot group that selection." Why It Happens: Some dates are stored as text, not real dates. How to Check: \\\ Select a date cell Look at the formula bar Real date: Shows as number (like 45292) Text date: Shows as text (like "1/15/2024") \\\ The Fix: \\\ ' Convert text to dates: =DATEVALUE(A1) ' Or use Text to Columns: Select date column Data → Text to Columns Choose Date format \\\

Mistake #6: Source Range Doesn't Expand

The Problem: You add new rows to your data, but they don't appear in the Pivot Table. Why It Happens: The Pivot Table's source range is fixed (like A1:E100), not dynamic. The Fix: \\\ Click in Pivot Table PivotTable Analyze → Change Data Source Expand the range to include new rows \\\ Prevention: \\\ Use an Excel Table (Ctrl+T) as your source! Tables automatically expand when you add data. \\\

Mistake #7: GETPIVOTDATA Frustration

The Problem: When you click a Pivot Table cell to reference it, Excel creates a complex GETPIVOTDATA formula instead of a simple cell reference. Example: \\\ ' You want: =B5 ' Excel gives: =GETPIVOTDATA("Sales",$A$3,"Region","North") \\\ The Fix (Disable GETPIVOTDATA): \\\ File → Options → Formulas Uncheck "Use GetPivotData functions for PivotTable references" \\\ Or type the cell reference manually instead of clicking.

Mistake #8: Calculated Field Confusion

The Problem: Your calculated field gives unexpected results. Why It Happens: Calculated fields work on SUMs, not row-by-row. Example: \\\ ' You want average price: Price / Quantity for each row, then average ' Calculated field does: SUM(Price) / SUM(Quantity) ' These can give different results! \\\ The Fix: \\\ Add the calculation as a column in source data Then summarize that column in Pivot Table \\\

Mistake #9: Too Many Fields in One Area

The Problem: Your Pivot Table is cluttered and hard to read with multiple fields in Rows. Example: \\\ Rows: Year, Quarter, Month, Region, Product, Salesperson → Massive, unreadable table! \\\ The Fix: \\\ Use Filters instead of Rows for some fields Create multiple focused Pivot Tables Use Slicers for interactive filtering Collapse/expand groups as needed \\\

Mistake #10: Not Using Slicers

The Problem: You're using dropdown filters, which are hidden and hard to use. Better Solution: Slicers \\\ Click in Pivot Table PivotTable Analyze → Insert Slicer Select fields to filter Click buttons to filter instantly! \\\ Slicer Benefits: Visual and intuitive Multi-select with Ctrl+Click Can connect to multiple Pivot Tables Users love them!

Bonus Mistakes

Mistake #11: Ignoring Blank Values Blanks in your data can skew averages and counts. Replace with 0 or "N/A" as appropriate. Mistake #12: Not Naming Pivot Tables Default names like "PivotTable1" are confusing when you have multiple. \\\ PivotTable Analyze → PivotTable Name → Enter descriptive name \\\ Mistake #13: Huge File Sizes Pivot Tables cache data, increasing file size. \\\ Right-click → PivotTable Options → Data tab Uncheck "Save source data with file" \\\

Quick Troubleshooting Checklist

When your Pivot Table isn't working: [ ] Is source data clean? (No blanks, proper headers) [ ] Did you refresh after data changes? [ ] Are dates real dates, not text? [ ] Does source range include all data? [ ] Are number columns actually numbers? [ ] Is the calculation type correct (Sum vs Count)?

Excel Pivot Tables: Complete Guide How to Create Your First Pivot Table Pivot Table Calculated Fields Common Excel Errors Explained

Pivot Table mistake severity matrix

Severity Mistake Business impact High Blank rows in source Under-reported revenue or inventory High Numbers stored as text Wrong sums and averages Medium Not refreshing after import Decisions on stale data Medium Confusing calculated field vs item Mislabeled KPIs

Calculated field vs calculated item

Use a calculated field when the math applies to the data fields (for example Profit = Revenue - Cost). Use a calculated item when the math applies to items inside a field (for example Q2 = Q2 Actual - Q1 Actual within the Quarter field).

Internal links

Pivot Table not updating Excel Pivot Tables guide SUMIFS vs Pivot SUMIFS function

Expert audit checklist (12 mistakes)

Mistake Symptom Fix Blank rows in source Pivot stops at first gap Delete blanks or use Excel Table Wrong totals Numbers as text Convert with VALUE or Text to Columns Grouped dates wrong Months out of order Fix source dates, refresh Pivot Double-counting Same record twice Define duplicate keys, dedupe first Stale data New rows missing Refresh or expand source Table Calculated field vs item Wrong metric type Use field for row math, item for Pivot math Link Pivot not updating and the Pivot Tables guide.

Frequently asked questions

What is the fastest way to check Pivot source quality? Convert the source to an Excel Table, confirm no blank rows or columns, verify numbers are true numbers, then refresh the Pivot.
Should I fix the source sheet or the Pivot settings first? Fix the source first. Most wrong totals come from bad source data, not from Pivot field settings.
Why does my Pivot Table total not match SUMIFS? Usually the Pivot source range excludes new rows, numbers are stored as text, or filters differ. Reconcile both against the same cleaned Table.