How to fix an excel pivot table that splits the same value across cases
- Step 1Identify the grouping column — Find the field that's splitting in the PivotTable (e.g.
City,Category,Status). That's the column to normalise in the source data, not in the pivot. - Step 2Export the source to CSV — Excel: File → Save As → CSV UTF-8. The Case Normalizer redirects to the CSV Case Converter, which reads CSV.
- Step 3Pick a consistent case for that column — Title for place/category names (
London), UPPER for codes (GB,EMEA). Tick only the grouping column so other fields stay untouched. - Step 4Convert and verify the count — Run it. The cells-changed count tells you how many variants were re-cased — useful confirmation that the split-causing rows were touched.
- Step 5Trim hidden whitespace if groups still split — If
LondonandLondon(trailing space) still split, run csv-whitespace-trimmer on the same column. Case + trim together fix almost every spurious split. - Step 6Reload and refresh the PivotTable — Open the cleaned CSV (or paste the column back), then PivotTable → Refresh. The variants now collapse into one group with a single correct subtotal.
Why a pivot splits the 'same' value
Case is one cause; these others survive a case fix and need a different tool.
| Cause | Looks like | Fix |
|---|---|---|
| Different case | London vs LONDON | This tool — normalise to one case |
| Trailing/leading space | London vs London | csv-whitespace-trimmer |
| Non-breaking space (CHAR 160) | London vs London\u00a0 | Whitespace trimmer / special-char stripper |
| Genuinely different text | London vs Londres | excel-fuzzy-dedup or manual map |
| Numbers stored as text vs number | 1001 (text) vs 1001 | excel-format-inspector |
Pick the case for the grouping field
Match the case to the field type so the pivot label reads naturally.
| Grouping field | Mode | Result |
|---|---|---|
| City / Region | Title | London, New York |
| Country code / business unit | UPPER | GB, EMEA |
| Email (if grouped) | lower | sue@x.com |
| Free-text category | Title or Sentence | Premium Plan / Premium plan |
Tier limits (the CSV Case Converter you reach)
CSV-family limits apply because the Excel page redirects to the CSV tool.
| Tier | Max file size | Max rows | Files at once |
|---|---|---|---|
| Free | 2 MB | 500 | 2 |
| Pro | 100 MB | 100,000 | 10 |
| Pro-media | 500 MB | 500,000 | 50 |
| Developer | 500 MB | Unlimited | Unlimited |
Cookbook
Real source-column before/after, then the refreshed-pivot result — including the trailing-space case that fools people into thinking the case fix failed.
Three case variants collapse to one group
Normalise the City column to Title Case; the pivot then sees one string and produces one group with the combined subtotal.
Source City column (before): London LONDON london Mode: title, City only -> all become 'London' Pivot (after refresh): London | sum 3 rows <- was three rows of 1 each
Codes to UPPER for a clean BU pivot
Business-unit or region codes group cleanly when forced to uppercase.
Source (Region): emea EMEA Emea Mode: upper, Region only Pivot: EMEA | one group
Case fix done, but groups STILL split — trailing space
After case normalisation London and London (trailing space) still split. The case fix worked; the residual cause is whitespace. Trim it.
After case pass (City): London London <- trailing space, invisible Pivot still shows TWO 'London' rows. Fix: csv-whitespace-trimmer on City -> single 'London' group.
Don't normalise the whole sheet — scope the column
Only re-case the grouping column. Leave value columns (amounts as text IDs, SKUs) alone so you don't change identifiers the pivot also relies on.
Tick: City only. Leave unticked: SKU, OrderID, Amount. Mode: title. Only City data cells change; everything else is byte-identical.
Lowercase emails before grouping by recipient
If the pivot groups by email, case differences create duplicate recipient rows. Lowercase the email column first.
Source (Email): Sue@X.com sue@x.com Mode: lower, Email only Pivot: one 'sue@x.com' group instead of two.
Edge cases and what actually happens
Page redirects to the CSV Case Converter
By designThe Excel Case Normalizer redirects to the CSV Case Converter. Export the pivot's source column as CSV UTF-8, normalise, re-open, and refresh the pivot.
Groups still split after the case fix
ExpectedThe most common residual cause is a trailing space (London vs London) or a CHAR(160) non-breaking space. The case pass was correct; run csv-whitespace-trimmer on the same column to finish the job.
Normalise the source, not the pivot
ExpectedRe-casing inside the PivotTable display doesn't merge groups — the underlying source values still differ. Fix the source column, then refresh.
Different words, not different case
LimitationLondon vs Londres (or a typo Londn) are different strings; case normalisation won't merge them. Use excel-fuzzy-dedup or a manual lookup map for synonyms/typos.
Header row untouched
PreservedThe field name the PivotTable points at (the header) is never converted; only data rows change, so the pivot's field reference keeps working after refresh.
Hyphenated category names under Title
Expectedpay-as-you-go → Pay-as-you-go (hyphen is not a token boundary). If you want each segment capitalised, split on the hyphen first.
Numbers-as-text vs numbers in a value field
LimitationIf a pivot splits because some cells are numbers stored as text, case conversion won't help — that's a format mismatch. Diagnose with excel-format-inspector.
Free tier 500-row cap on a large fact table
Tier limitThe CSV Case Converter caps Free at 500 rows. Large pivot source tables need Pro (100,000 rows) or higher, or normalise the column in chunks.
Sentence case can re-split if cells differ mid-string
ExpectedSentence mode only fixes the first character. customer A and Customer a both become Customer a, but Customer A stays distinct from Customer a. For grouping, prefer Title or UPPER over Sentence.
Re-open output as UTF-8
SupportedSave the source as CSV UTF-8 and re-open as UTF-8 so accented group labels (São Paulo) don't garble — garbled labels would themselves split the pivot.
Frequently asked questions
Why does my pivot show London and LONDON separately?
PivotTable grouping keys on the underlying cell string. London and LONDON are different strings, so they form different groups. Normalise the source column to one case and refresh to collapse them.
Do I fix this in the pivot or the source?
The source. Re-casing the pivot's displayed labels doesn't change the underlying values. Normalise the source column, reload, then PivotTable → Refresh.
How do I run it on an Excel file?
The Excel Case Normalizer redirects to the CSV Case Converter. Save the source as CSV UTF-8, convert the grouping column, re-open, and refresh the pivot.
Which case should I use for the grouping column?
Title for place/category names, UPPER for codes/business units, lower for emails. Pick one and apply it to the whole column so every variant becomes identical.
I normalised case but the groups still split — why?
Almost always a hidden trailing space or non-breaking space. Run csv-whitespace-trimmer on the same column; case + trim fixes the vast majority of spurious splits.
Will it merge London and Londres?
No — those are different words, not different cases. Use excel-fuzzy-dedup or a manual mapping table for synonyms and typos.
Does it change the header / field name?
No — only data rows are converted. The PivotTable's field reference (the header) stays intact, so a refresh just works.
Can I scope it to only the grouping column?
Yes — tick only that column in the checklist. Value columns, SKUs, and IDs are left byte-identical.
Should I use Sentence case for grouping?
Usually not. Sentence case only fixes the first character, so mixed-case mid-string values can still differ. Title or UPPER give more reliable grouping.
Is my pivot data uploaded?
No. The CSV Case Converter runs in your browser; the source data never leaves your machine.
How large a source table can I fix?
Free 500 rows / 2 MB; Pro 100,000 / 100 MB; Pro-media 500,000 / 500 MB; Developer unlimited. These are the enforced CSV-family limits.
What if the split is numbers stored as text?
Case conversion won't help a number/text mismatch. Diagnose and fix that with excel-format-inspector.
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.