My pivot table shows the same city twice — how to fix duplicate entries
"Chicago" shows up as two separate rows in your pivot table instead of one combined total. A pivot table groups by the exact underlying text value in the source data — a trailing space, a stray non-breaking space, or a different capitalization on just a few rows is enough to create a second, separate group that looks identical to the first. Drop your file in below to find which rows don't actually match.
Pivot tables group on the exact string, not the meaning
When you drop a field into a pivot table's Rows or Columns area, Excel builds its group list from every distinct value that appears in the source column — a plain string comparison, cell by cell. It has no concept that "Chicago" and "Chicago " should mean the same place; as far as the grouping logic is concerned, those are two different strings, so they become two different rows in the pivot, each summing only the records that exactly match its own version.
The three ways one city becomes two rows
A trailing or leading space on some rows but not others is the most common — often from data entered by hand over time, or merged from two source systems with different habits. A non-breaking space, invisible on screen, causes the identical split but is much harder to catch by looking. Inconsistent capitalization ("Chicago" vs. "chicago" vs. "CHICAGO") is the third — unlike formula functions such as VLOOKUP or COUNTIF, which are case-insensitive, the distinct-values list a pivot table groups by treats different casing as different values.
Why this is easy to miss
Nothing about the pivot table looks broken — it built successfully, the numbers in each row add up correctly for what that row actually contains, and it's only when you notice "Chicago" listed twice with two smaller totals instead of once with the combined total that the underlying data issue becomes visible at all.
How to fix it
The fix happens in the source data, not the pivot table: run TRIM (nested with a SUBSTITUTE for CHAR(160) if non-breaking spaces are involved) across the column to normalize whitespace, and use UPPER, LOWER, or PROPER consistently — or Find & Replace — to standardize capitalization, then refresh the pivot table (right-click → Refresh). Upload your file above first and the checker will surface which values in the column are near-duplicates of each other by whitespace or case, before you rebuild the pivot.