Exceljet

Quick, clean, and to the point

How to rearrange fields in a pivot table

Moving fields around in a pivot table is fun and even addictive. Each time you move a field, you see another view of the same data, in a different orientation. This is where the idea of Pivot comes from.

Let's take a look.

When you're working with a pivot table, you can move fields from one area to another at any time. Each time you move a field, the pivot table will be rebuilt itself to respect the new field configuration.

The easiest way to move a field is just to drag it from one area to another. In this pivot table, we have the Product field in the Row Labels area and Region in Column labels areas. We can just drag the fields to swap locations.

And drag again back to the original orientation.

In this same way, we can look at product sales by region and state by adding State to the Column labels area. Product sales are now grouped by Region first, then State.

Let's remove Region and add Category as a row label.

Note that the order that the fields appear in is reflected in the pivot table. Some sequences don't make sense. For example it doesn't make sense to have product appear above category. The pivot table rebuilds itself correctly, but the structure isn't useful, since each product appears in only one category.

However, if we move the Category field so that it appears before Product, we get a configuration that does make sense. The pivot table now shows product sales with products grouped by Category.

Numeric fields are normally placed in the Values area. If we put a numeric field like Quantity into the row labels area, the pivot table breaks out product sales by product, then by quantity, which is interesting but not especially useful.

Note that fields you’ve placed into a pivot table remember their position. If you uncheck the field name, the field will be removed from the pivot table. When you check the field name again, the field will be placed in it’s last location.

As you experiment with different pivot table configurations - and you should definitely experiment to learn how pivot tables work - keep the Undo command at the ready. You can use Control-Z to step backwards until the pivot table is in good shape, then start experimenting again.

Course 
Core Pivot

Related shortcuts

CtrlZ
Z
Author 
Dave Bruns