Excel/PivotTable: Is there any way to prevent the cells from moving when I filter information?

Question

My current pivot table contains 4 columns: time in Column A, then Zone 1 in Column B, Zone 2 in Column C, and Zone 3 in Column D.

Is there any way to "lock" the columns, e.g. if I apply filter by Zone and only select Zone 3, Zone 3 will stay in Column D?

Currently, if I filter by Zone and only select Zone 3, Zone 1 and Zone 2's column disappear and Zone 3 will then be in Column B.

Thanks for any help you can provide.

Answer 1

I'm going to say "No". Pivot tables are a world to themselves inside of Excel.

You may have noticed that if you do something that expands them into surrounding data, they don't shift that data, they just warn you. Similarly, if you have formulas that refer to certain columns in a pivot table, filtering the pivot table as you've done doesn't adjust the cell reference as it does when you add or delete regular columns. I assume this is the type of issue you're seeing.

If so, consider using the GetPivotData function, which allows you to refer to pivot table columns.

Below I've shown how this could work with a simple table. The GetPivotData formula uses a variable (an added bonus!) in cell F1. In this case it gives you the overall total for fiona:

=GETPIVOTDATA("value",$E$4,"name",F1)

Note that when I filter to just "fiona" it still works:

Note that you can make the formula as specific as the data in the pivot table. Here I get the sales for doug on Thursday. (I could use variables for both "doug" and "Thursday" here as well:

=GETPIVOTDATA("value",$E$4,"day","Thursday","name","doug")

The easiest way to create a GetPivotData formula is to type "=" in a cell and then click in the pivot table cell you are interested in. You can then edit from there.

Here's a great Contextures GetPivotData tutorial.