"NY" and "New York" treated as two different things in Excel — fix inconsistent values
Excel isn't merging NY and New York because, to a spreadsheet, they're two completely different text strings, with no built-in synonym matching. Here's how to standardize inconsistent values so they roll up together
Why Excel treats them as completely unrelated
Every function that groups or matches text — pivot tables, COUNTIF, SUMIF, FILTER, UNIQUE — works by comparing strings for equality. There's no layer underneath that understands abbreviations, aliases, or real-world entities; NY and New York are simply two different sequences of characters, and no amount of familiarity with U.S. geography changes that for a formula.
Where inconsistent values usually come from
This is almost always a data entry or data-merging problem rather than a software one. It shows up when several people fill in a free-text field with their own habits — one types the two-letter abbreviation, another spells out the city — or when you combine exports from two different systems that each picked their own convention for the same field.
How to standardize before you roll the data up
Build a small two-column mapping table — abbreviation on one side, the canonical name you want to use on the other — and pull a clean version into a new column with VLOOKUP or XLOOKUP against that table, then build your pivot or filter on the clean column instead of the original. Power Query's Replace Values step does the same job if you're already using Query to bring the data in. Remove Duplicates won't help here, since the values genuinely differ character-for-character; a Data Validation dropdown list on the source field is the best way to stop new inconsistent values from being entered going forward.