How to fix excel pivot table missing values caused by mixed data types
- Step 1Upload the workbook with the broken pivot table — Drop the .xlsx. Every text-type cell is analysed for numeric and date pattern mismatches.
- Step 2Review the mismatch report — Every cell where a number looks like text is flagged with its address and value.
- Step 3Fix the cells and refresh the pivot — Convert the flagged cells to numbers in Excel (Text to Columns or VALUE function), then refresh the pivot table.
Frequently asked questions
Why does the pivot table show (blank) in some rows?+
Text-stored values in a numeric column are treated as blank by pivot aggregations. Converting them to numeric types makes them visible.
The SUM row in my pivot shows less than the manual total — why?+
Text-stored numbers are excluded from SUM. The manual total (using AutoSum in a worksheet) adds them correctly because it operates on cell content, not type.
Can the Format Inspector automatically fix the type mismatches?+
Inspection only in the current version. Auto-conversion of text-stored numbers is on the Pro roadmap.
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.