How to fix excel pivot table missing values caused by mixed data types
- Step 1Identify the misbehaving pivot field — Note which field is wrong: a value field summing too low (or defaulting to Count), or a row/column date field with duplicate groups. The culprit is text-stored values in the source column that feeds that field.
- Step 2Open the source table, not the pivot — Pivots read from a source range or table. Save that source workbook as
.xlsx(the inspector reads XLSX only). If the source is an external CSV, open it in Excel and Save As an Excel Workbook first — a CSV has no type info for the scan to use. - Step 3Drop the source workbook onto the tool — SheetJS parses it in your browser. The scan reads every sheet, so a staging or lookup tab feeding the pivot's data model is audited too. Nothing uploads.
- Step 4Read the type-mismatch report — Each line names the sheet, cell, stored type, and suggested type:
looks like numberfor value-field offenders,looks like datefor date-field offenders. The count panel shows the total; the first 100 lines appear inline. - Step 5Fix the source cells in Excel — Convert the flagged value cells to numbers (warning diamond → Convert to Number, or Data → Text to Columns → Finish on the column). For text dates, convert the column to real dates. The inspector pinpoints the cells; Excel applies the type change.
- Step 6Refresh the pivot and verify — Back in Excel, PivotTable → Refresh. The dropped rows now sum, mixed fields default to Sum, and duplicate date groups collapse. Re-scan the source with the inspector to confirm 'No format mismatches found' before you rely on the numbers.
How a type mismatch corrupts a pivot — and what the inspector reports
The pivot symptoms caused by text-stored values, mapped to what the detector flags.
| Pivot symptom | Underlying cause | Inspector flags |
|---|---|---|
| 'Sum of Amount' totals too low | Some value cells are text; pivot drops them | Each text cell as 'looks like number' |
| Value field defaults to Count, not Sum | Pivot sees the field as mixed (text present) | The text cells making it mixed |
| Blanks in a Sum row | Whole sub-group of the value field is text | Every text cell in that group |
| Two '2024' groups that won't combine | Some dates are text, some are real dates | Text dates as 'looks like date' |
| Date field won't group by Month/Quarter | Text dates can't be date-grouped | The text dates needing conversion |
Currency-symbol values dropped ($50) | $ makes it text and unparseable | NOT flagged — strip the $ first |
Detector scope for pivot source data
Exactly what the two detectors match in a pivot source column.
| Source value (stored as text) | Flagged? | Pivot impact |
|---|---|---|
5021 | Yes — number | Dropped from Sum |
1,234.56 | Yes — number (commas stripped) | Dropped from Sum |
2024/01/15 | Yes — date | Splits date grouping |
01-02-2024 | Yes — date | Splits date grouping |
15 Jan 2024 | No — spelled month not matched | Still breaks grouping; convert manually |
$1,234 | No — $ breaks pattern | Dropped from Sum; strip symbol first |
North-1 | No — letters present | Correctly a text label, no impact |
Cookbook
Real pivot-table failures traced to type mismatches, with the inspector report and the fix-then-refresh path.
'Sum of Amount' totals too low
A sales pivot sums an Amount column, but the total is short because three source cells are text. The inspector flags exactly those three so the refresh corrects the total.
Pivot 'Sum of Amount' = 12,300 (should be 15,800)
Source sheet 'Data', column D 'Amount':
most cells are real numbers, but:
D45 = 1200 (text)
D88 = 1100 (text)
D201 = 1200 (text)
Inspector report:
Found 3 format mismatch(es):
[Data] D45: stored as text, looks like number ("1200")
[Data] D88: stored as text, looks like number ("1100")
[Data] D201: stored as text, looks like number ("1200")
Fix those 3 -> Refresh -> Sum of Amount = 15,800Value field defaults to Count instead of Sum
When a field has any text, the pivot may default the aggregation to Count. You change it to Sum and it's still wrong because the text cells contribute zero. The inspector shows which cells are text.
Pivot put 'Count of Revenue' instead of 'Sum of Revenue'
Source sheet 'Q1', column C 'Revenue':
C2 = 5000 (real number)
C3 = 4200 (text)
C4 = 3800 (text)
Inspector report:
Found 2 format mismatch(es):
[Q1] C3: stored as text, looks like number ("4200")
[Q1] C4: stored as text, looks like number ("3800")
Convert C3:C4 -> set aggregation to Sum -> Refresh -> correctDate field splits into duplicate year groups
A pivot grouped by year shows two '2024' rows that won't merge. The reason: some dates are real dates, others are text. The inspector flags the text dates so you convert them and the groups collapse.
Pivot rows show: 2024 (real dates) AND 2024 (text dates)
Source sheet 'Orders', column A 'Date':
A2 = real date 2024-01-05
A3 = 2024/02/11 (text)
A4 = 2024/03/02 (text)
Inspector report:
Found 2 format mismatch(es):
[Orders] A3: stored as text, looks like date ("2024/02/11")
[Orders] A4: stored as text, looks like date ("2024/03/02")
Convert A3:A4 to real dates -> Refresh -> single 2024 groupBlanks in a Sum sub-group
One region's rows are all text in the value field, so its Sum sub-total shows blank. The inspector flags the whole sub-group.
Pivot 'Sum of Units' by Region:
North = 4,500
South = (blank) <- all South value cells are text
Source sheet 'Sales', column E 'Units' (South rows):
E30..E33 = 200, 150, 175, 90 (all text)
Inspector report (excerpt):
[Sales] E30: stored as text, looks like number ("200")
[Sales] E31: stored as text, looks like number ("150")
[Sales] E32: stored as text, looks like number ("175")
[Sales] E33: stored as text, looks like number ("90")Currency-symbol values silently dropped (not flagged)
A pivot ignores $-prefixed amounts. The detector does NOT flag these because the $ breaks the numeric pattern — so a clean scan can still hide currency-text problems. Strip the symbol first, then re-scan.
Pivot drops all '$'-prefixed amounts from the Sum.
Source sheet 'Billing', column F 'Charge':
$1,200 (text) <- NOT flagged by detector
$980 (text) <- NOT flagged by detector
First scan: 0 mismatches (misleading).
Fix: Find & Replace '$' -> nothing, then re-scan:
[Billing] F2: stored as text, looks like number ("1,200")
[Billing] F3: stored as text, looks like number ("980")
Convert -> Refresh -> amounts now sum.Edge cases and what actually happens
Source is a CSV, not an XLSX
RejectedThe inspector reads .xlsx only — it relies on per-cell type metadata to separate text from number/date. A CSV has none (all text). Open the CSV in Excel and Save As an Excel Workbook, then scan, before building the pivot from it.
Pivot still wrong after a clean scan
Look elsewhereIf the scan is clean but the pivot still skips values, check for issues the detector can't see: currency/percent text ($50, 50%) and accounting parens — none are flagged; the pivot source range not including all rows; a stale pivot that wasn't refreshed; or grouping set up before the data was fixed.
Mixed real-and-text dates
Flagged (text ones only)The detector flags the text dates but not the real ones — which is exactly what you need, because the real dates are fine and only the text ones split the pivot's date grouping. Convert the flagged dates to real dates so all of them group together.
Spelled-out month dates
Not detected15 Jan 2024 or January 2024 don't match the numeric date pattern, so they're not flagged even though they break date grouping. Convert these in Excel (or via a date standardizer) separately; the detector only catches numeric date shapes with /, -, or . separators.
Currency or percent in the value field
Not detected$1,234 and 50% are text the pivot drops, but the symbol breaks the numeric pattern so they aren't flagged. Strip the symbol with Find & Replace first, then re-scan — once they're bare digits-and-commas the detector catches them.
Hidden staging sheet feeds the pivot
SupportedIf the pivot's data model pulls from a hidden helper sheet, the scan still audits it — every sheet in the workbook is checked. Text-stored values on that hidden sheet appear in the report with the sheet name, so you don't miss them.
Report capped at 100 inline lines
Truncated displayA large source table with many text cells exceeds the 100-line inline cap; the report appends '... and N more.' and the count panel shows the true total. For widespread issues, fix the whole column at once in Excel (Text to Columns) and refresh the pivot.
Numeric source cells already correct
PreservedCells already stored as real numbers or dates are skipped — they aggregate correctly in the pivot already. The inspector never flags a correct cell, so a clean source returns the all-clear and you can refresh with confidence.
Text label column flagged because values are pure digits
ExpectedIf a category/label column contains pure-digit codes stored as text (001, 045), they're flagged as 'looks like number'. That may be intentional — they're row labels, not values. Leave them as text if grouping needs the leading zeros; the detector flags, you decide.
Source workbook over the tier limit
Rejected (size limit)Free tier caps the Excel family at 5 MB / 10,000 rows. A large pivot source is rejected before scanning. Upgrade to Pro (50 MB / 100,000 rows) or Pro-media (200 MB / 500,000 rows), or split the source by sheet and scan each.
Frequently asked questions
Why is my pivot table skipping some values?
Because the value field contains a mix of real numbers and numbers stored as text. The pivot engine drops the text cells from a Sum (or defaults the field to Count because it sees text). This inspector finds the exact text cells in your source data so you can convert them and get an accurate aggregation after a refresh.
Does the tool fix the source so the pivot works?
No — it detects, it doesn't convert. You get a report listing every text-stored number or date in the source. Fix them in Excel (Convert to Number, or Text to Columns for a whole column; convert text dates to real dates), then refresh the pivot. The inspector removes the guesswork of finding which source cells are wrong.
Why did my pivot default to Count instead of Sum?
Excel defaults a value field to Count when the column contains any text. Even if you switch it to Sum, the text cells contribute nothing, so the total is short. Run this inspector on the source, convert the flagged text cells, and the field will sum correctly after a refresh.
My pivot shows two '2024' groups — why?
Because some dates in the source are real dates and others are text dates. The pivot treats them as different values, so they don't roll into one group. The inspector flags the text dates ('looks like date') so you can convert them to real dates and collapse the duplicate groups.
Should I scan the pivot or the source data?
The source data. A pivot reads from a source range or table; the type problem lives there. Save the source workbook as .xlsx and scan it. The pivot itself has no cells to inspect — it's a view of the source.
Does it scan a hidden staging sheet my pivot uses?
Yes. The scan walks every sheet in the workbook, including hidden and very-hidden ones. If your pivot's data model pulls from a hidden helper sheet, any text-stored values there are reported with the correct sheet name.
The scan is clean but the pivot is still wrong — why?
The detector only catches plain text numbers and numeric-shaped text dates. It won't catch currency/percent text ($50, 50%), parenthesised negatives, spelled-out month dates, a source range that misses rows, or a pivot that wasn't refreshed. Check those, strip any symbols, and re-scan.
Is my source data uploaded anywhere?
No. The scan runs entirely in your browser with SheetJS. Source rows, amounts, and dates never leave your machine. Free tier needs no upload and no account; only an anonymous usage counter may be recorded for signed-in users, never file content.
What file types does it accept?
.xlsx only. The audit needs the per-cell type metadata XLSX stores. CSV has no type info. Save a CSV source as an Excel Workbook first.
How large a source table can I scan?
Free tier: 5 MB / 10,000 rows. Pro: 50 MB / 100,000 rows. Pro-media: 200 MB / 500,000 rows. Developer: 500 MB / unlimited. Over-limit files are rejected before scanning; split a big source by sheet if needed.
What's the quickest way to fix a whole text-number column before pivoting?
Select the column in Excel and use Data → Text to Columns → Next → Next → Finish to re-parse every cell as a number, then refresh the pivot. For text dates, set the column format and use Text to Columns with the Date option. The inspector tells you which columns need it.
What related tools help with pivot prep?
After fixing types, the date standardizer normalises mixed date formats so they group cleanly, the unpivot tool reshapes wide data into the tall layout pivots prefer, and the pivot generator builds a summary directly. Run this inspector first to clear type mismatches.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.