Pivot Table Issues

How to Keep Adjacent Data Aligned When Filtering Excel PivotTables

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs a way to filter a PivotTable without losing the correct row alignment of remarks or additional data entered in the adjacent columns.

Product: Excel / Spreadsheets
Device & OS: not provided
Scenario: Adding manual remarks or complementary data into the worksheet cells directly next to an existing PivotTable report.
Observed behavior: When the PivotTable is filtered or updated, the adjacent data does not filter or shift with the PivotTable rows, causing the remarks to become misaligned with the corresponding values.

Before you start

Identify whether your adjacent remarks can be integrated directly into your original data source sheet, as this dictates the most reliable approach for keeping everything aligned.

Solution 1Recommended

Integrate Remarks Directly into the Source Data

The most reliable way to ensure data stays aligned during filtering is to add your remarks to the original dataset driving the PivotTable.

Because a PivotTable is a dynamic object, standard worksheet cells next to it will remain static when the table changes shape or applies filters. Moving your remarks into the source data allows the PivotTable to manage them natively.

Update the source data

Navigate to the original source data sheet used by the PivotTable and add a new column for your remarks.

Expand the data source

Go back to your PivotTable, click anywhere inside it, and select 'PivotTable Analyze' > 'Change Data Source' to ensure the new remarks column is included in the data range.

Refresh the PivotTable

Right-click anywhere inside the PivotTable and select 'Refresh' from the context menu to pull in the new column.

Add remarks to the layout

In the PivotTable Fields pane, check the box next to your new remarks field or drag it into the 'Rows' area so it appears alongside your filtered data.

Solution 2

Use GETPIVOTDATA or Lookup Formulas for External Remarks

If you cannot modify the source data and remarks must remain outside the PivotTable, use dynamic formulas to link the remarks to the filtered items.

WPS Spreadsheet Solution

Manage and Filter PivotTables Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides powerful and highly compatible PivotTable features, allowing you to easily integrate new data, refresh sources, and use advanced lookup formulas to keep your reports perfectly aligned.

1. Open your data file: Launch WPS Spreadsheet and open your existing workbook containing the PivotTable.
2. Add remarks to source data: Navigate to your raw data sheet and insert a new column to house your text remarks.
3. Refresh the PivotTable: Return to your PivotTable sheet, click on 'PivotTable Tools' in the top ribbon, and click 'Refresh' to sync the new column.
4. Adjust the layout: Drag the newly added remarks field into the Rows section of the Field List to keep them perfectly aligned during any filtering.

Full compatibility with Microsoft Excel PivotTable structures and .xlsx formats.Advanced formula support including GETPIVOTDATA, VLOOKUP, and XLOOKUP.Intuitive interface for quickly updating and refreshing data source ranges.Lightweight and fast, handling large datasets and complex filters without lagging.

Frequently Asked Questions

Why doesn't Excel automatically link adjacent cells to my PivotTable?

PivotTables are dynamic objects generated from a data cache. When you apply a filter or refresh, Excel rebuilds the visual table from that cache. Adjacent cells are standard worksheet cells that exist independently of this dynamic object, so they do not move or filter with it.

Can I group the adjacent static data with the PivotTable object?

No, you cannot directly group standard worksheet cells into a PivotTable. You must either include the adjacent data inside the actual source range so the PivotTable can process it, or link it using lookup formulas like VLOOKUP or INDEX/MATCH.

How do I stop column widths from changing when filtering a PivotTable?

To prevent your adjacent columns from resizing unexpectedly when filtering, right-click anywhere inside the PivotTable, select 'PivotTable Options', and under the 'Layout & Format' tab, uncheck the box for 'Autofit column widths on update'.

Excel & Data Problems