Pivot Table Issues

How to Keep Excel PivotTable Rows Fixed While Filtering

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 868 views

Question details

The user needs a reliable way to keep agency records aligned when filtering or changing a PivotTable layout.

How to Keep Excel PivotTable Rows Fixed While Filtering
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.
Before you start

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.

Solution 1Recommended

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.

1
Put Agency first

Click inside the PivotTable and, in the PivotTable Fields pane, drag Agency to the top of the Rows area.

2
Keep the layout consistent

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.

Keep the PivotTable layout unchanged while working through agencies
Row positions can still change: Refreshing the PivotTable or changing its source data, fields, or sort order can also change its layout. Use a separate keyed sheet when alignment must remain reliable.
Free Microsoft Office alternative

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.

Filter agency records and organize reconciliation data in spreadsheetsCompatible with Microsoft Excel XLS and XLSX formatsFamiliar interface for users moving from ExcelFree and lightweight for everyday office work
QA img-9

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.