Rearranging PivotTable Rows and columns

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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

img 5a0099036fb73 - Rearranging PivotTable Rows and columns

Check out how the Pivot table rebuilds into a different orientation by dragging fields in different configurations:

img 5a009915deb87 - Rearranging PivotTable Rows and columns

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.

subs profile e1563205311409 - Rearranging PivotTable Rows and columns
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address