Moving or swapping fields around in a PivotTable is both amusing and addictive at the same time. You cannot stop yourself from rearranging your data in different ways. Each time you move a field, the Pivot Table will restructure to the new field configuration and you see the same data in a different alignment. May be that’s where the idea of creating Pivot comes from.
Let’s try out a different way of rearranging the same data in a different order. Here I will drag
- Dates fields into Columns area
- Country into the Filters
- Sales into the Values area
- Category into the Rows area
- Products into the Rows area
Check out how the Pivot table rebuilds into a different orientation by dragging fields in different configurations:
You can see the Products sales in the given month with the products grouped by the Categories of Fruits and Beverages.
The Right Order
The sequence you put your fields into the PivotTable field box is how it gets reflected in the PivotTable. Even though it’s not incorrect, still in my opinion some sequences do not make sense. For instance, having products on top of category does not look professional at all as each product appears in only one category. Pivot Table does not disrespect your instructions and rebuilds the table as you want the fields to appear. If you notice, structurally it does not make sense. But when you move category on top of Products we see a structure that makes sense with the Products grouped by category.
Likewise, the numeric fields like Sales, Average, Sum, Count etc. are better placed in the Values area and they look good structurally.
As you play around different configurations in your PivotTable just remember if you are lost in between the configurations and want to get back to the original orientation of your PivotTable or to some good shape – A small trick can do wonders and save you from all the confusions – just do a Control-Z and it takes you steps backwards until you find your desired PivotTable.
