How to Keep Excel PivotTable Rows Fixed While Filtering
Question details
The user needs a reliable way to keep agency records aligned when filtering or changing a PivotTable layout.

- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- Working through agencies and matching information to PivotTable rows while applying filters or expanding and collapsing groups.
- Observed behavior
- The PivotTable recalculates its layout, so agencies can appear in different row positions when filters or group states change.
Identify the Agency field and any information entered beside the PivotTable. Check whether you need summarized totals or individual source records, since that determines which workaround to use.
Keep the PivotTable layout unchanged while working through agencies
Use this approach if you need to work in the existing PivotTable without changing its visible row layout.
A PivotTable cannot guarantee fixed row positions when you filter it or expand and collapse groups. Data entered outside the PivotTable is not automatically synchronized with its rows.
Click inside the PivotTable and, in the PivotTable Fields pane, drag Agency to the top of the Rows area.
Work down the agency rows without applying filters or using the expand and collapse buttons. Avoid relying on adjacent worksheet cells to follow an agency if the PivotTable changes.

Filter agency records in an Excel Table
Use a structured table when you need to work with individual records rather than PivotTable summaries.
Build a separate reconciliation sheet keyed by agency
Use a fixed agency list and formulas when you need stable summary rows independent of PivotTable filtering.
Work with agency data in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files, filter agency records, and create separate reconciliation sheets for a smooth transition from Microsoft Office.

Frequently Asked Questions
Can I lock PivotTable rows in place when filtering?
No. Filtering or expanding and collapsing groups changes the PivotTable layout. For stable summary rows, create a separate agency list and use formulas such as SUMIFS against the source data.
Will notes beside a PivotTable move with their agencies?
No. Cells outside the PivotTable are not synchronized with its items. Store notes in a source table with the relevant record or on a separate sheet keyed by agency.
How do I filter individual agency records instead of summaries?
Select the source data and choose Insert > Table. Then use the filter arrow in the Agency column header to show the records you need.
Why did my PivotTable rows move after a refresh?
A refresh recalculates the PivotTable from its source data, which can change the displayed items and their positions. Avoid matching external data by worksheet row number; use an agency key instead.




