Pivot Table Issues

Separate Washington, DC and Washington State in an Excel PivotTable

Algirdas JasaitisAlgirdas Jasaitis Nov 13, 2026 868 views

Question details

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

Separate Washington, DC and Washington State in an Excel PivotTable
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.
Before you start

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.

Solution 1Recommended

Add a Geography abbreviation or FIPS code to the PivotTable

Use an additional geographic field to distinguish locations that share the same displayed name.

1
Extract a distinguishing field

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.

2
Check the extracted values

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.

3
Include the new column in the source

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.

4
Refresh and add the field

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.

Add a Geography abbreviation or FIPS code to the PivotTable
Use a unique field, not just the displayed name: The displayed text Washington does not provide enough information to distinguish the locations. Adding a geographic identifier preserves that distinction in the PivotTable.
Free Microsoft Office alternative

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.

Build location-based PivotTables using explicit abbreviation or FIPS columns.Open and save Microsoft Excel formats, including .xlsx and .xls.Use a familiar spreadsheet interface to simplify migration from Excel.Work with everyday spreadsheet data in a free, lightweight Office alternative.
microsoft office alternative - wps office

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.