Separate Washington, DC and Washington State in an Excel PivotTable
Question details
The user needs an Excel PivotTable to distinguish Washington, DC from Washington State when both Geography records display as Washington.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Summarizing location-based data that contains both Washington, DC and Washington State using Excel Geography data types.
- Observed behavior
- Excel identifies the locations as different Geography records, but the PivotTable displays both as Washington without an additional distinguishing field.
Select each Washington Geography cell and open its data card to confirm that one represents Washington, DC and the other Washington State. Check whether the PivotTable source is an Excel table or a fixed range so you can include the additional field.
Add a Geography abbreviation or FIPS code to the PivotTable
Use an additional geographic field to distinguish locations that share the same displayed name.
Select the cells in the Geography column, click the Add Column icon that appears, and choose Abbreviation or FIPS code if available. Give the new column a clear header, such as Location Abbreviation or Location FIPS.
Review the new column for both Washington records. Washington, DC has FIPS code 11, while Washington State has FIPS code 53. If you selected Abbreviation, confirm that the values distinguish DC from WA.
Click inside the PivotTable and open PivotTable Analyze > Change Data Source. Confirm that the source includes the new column, expanding the range if necessary, and click OK. If the source is an Excel table, verify that the new column is part of that table.
Right-click the PivotTable and select Refresh. In the PivotTable Fields pane, drag the new abbreviation or FIPS field into Rows next to the Geography field, or use it in place of the Geography field to group the two locations separately.

Summarize Clearly Labeled Location Data with WPS Spreadsheet
WPS Office is a free, lightweight, easy-to-use alternative with a familiar spreadsheet interface and Excel format compatibility for a smooth transition. It does not fix Excel's linked Geography behavior; use ordinary text or numeric columns containing DC, WA, or FIPS codes when building location-based PivotTables in WPS Spreadsheet.

Frequently Asked Questions
Why does my PivotTable show both locations as Washington?
The Geography records can represent different locations while sharing the same displayed name. Add Abbreviation or FIPS code through the Geography column's Add Column icon, then include that field in the PivotTable Rows area.
What are the FIPS codes for Washington, DC and Washington State?
Washington, DC has FIPS code 11, and Washington State has FIPS code 53. Use these values in a separate source column and add that column to the PivotTable to distinguish the locations.
Why is the new geographic field missing from PivotTable Fields?
The new column may be outside the PivotTable's source range. Select the PivotTable, open PivotTable Analyze > Change Data Source, include the new column, and then right-click the PivotTable and select Refresh.
What if Abbreviation or FIPS code is not available in the Geography field list?
Click each Geography cell's data-type icon to verify the selected location. If the needed field is still unavailable, add an ordinary helper column and enter DC or WA for each corresponding record, then include that column in the PivotTable source and Rows area.




