Changing Row Fields in pivot Table of Excel

I wanted to change the Row Fields used in the Pivot Table, I was wondering if a slicer could do this.

Normally I would create two pivot tables along with two separate charts but in my actual dataset, I have more than 2 important parameters but please note they all rely on the same value parameter here, value, so it would be nice if I had some slicer-like that could change the rows.

These were simple made-up values I used in screenshots.

Lower,Upper,Value
a,A,1
b,B,2
c,C,3
h,H,2
a,B,234
c,H,34
c,D,69

Sorry if my question was repeated or lacks some information.

1 Answer

If you only ever want to use one row field at a time, you can stack the variables using PowerQuery.

Put your cursor in the data and use Data>Get & Transform Data>From Table/Range.

This will create a Table and open the PowerQuery Editor:

Select the Lower and Upper columns by holding down Ctrl and clicking on their headers and use Transform>Unpivot Columns. You should see this:

Rename the columns to something meaningful to you by right-clicking the column header and choosing Rename. You can also drag the columns to reposition them. I have renamed them and reordered as follows:

Now use Home>Close & Load to put the data back into the workbook.

You can create a pivot table from this dataset, putting the VariableMember on the rows, the Value in the Values and creating a slicer on Variable:

EDIT 1:

Here is another way that doesn't use VBA, DAX or PowerQuery, but does require that you refresh the pivot table after you select which column you want as a row header.

I have renamed cell G2 as selected_column. This is done by selecting a cell then entering a name into the Name Box, as described here. This is not strictly necessary, but I find it makes it easier to read formulas referring to this column.

I have converted the data range to a Table using Ctrl+T and added a column called 'Pivot row header' with this formula:

=IFS(selected_column="Lower",[@Lower],selected_column="Upper",[@Upper],TRUE,"")

This is just saying "If the value in cell G2 is Lower, then show the values from the Lower column in this column. If the value in cell G2 is Upper, then show the values from the Upper column in this column. Otherwise, show nothing in this column."

If you have more columns to select from, you can extend the IFS formula as many times as you like.

In cell G2, I have added data validation by using Data>Data Validation, and configuring the dialog like this:

So, my drop-down list in cell G2 shows the column headers from the first two columns in my Table, i.e. the values Lower and Upper.

When I select a value from this drop-down in G2, the values in column C in the table update.

I then need to refresh the pivot table to see the changes.