How to change the PivotTable Layout Options in Excel – did you know there was more than one layout and which do you prefer?
This week’s hint and tip is on PivotTable Layout Options in Excel. We look at PivotTables in our Advanced Excel training course and demonstrate how you can change the layout of your PivotTable in the options area. We decided to do a hint and tip on it though to go through it in more detail and are going to go through it now below.
Comparing the ‘newer’ layout to the classic layout
In more recent years, when you have created a PivotTable the layout that it chooses and displays it in is a more ‘newer’ one compared to the classic layout that everyone was familiar with. The classic layout allowed for dragging and dropping of fields on to the PivotTable itself rather than only being able to do it in the quadrants area on the right hand side.
To change the layout to be classic manually, you click on the Options button on your ribbon on the PivotTable Analyze Tab. Then under the Display tab heading, tick the box for ‘Classic pivot table layout…’ and click OK. This will change the layout to the classic style.
The screenshots below show the ‘newer’ layout style on the left and the classic layout on the right.
Changing the default to be the classic layout option
Currently if you wanted the classic layout option for a PivotTable, you would have to keep changing it each time. However there is an option where you can edit the default layouts in Excel. To do this, follow the steps below:
- Firstly click on Options found on the File Tab
- In the window, click on the Data option on the left hand side
- Next click on ‘Edit Default Layout’
- In this next window, click on the PivotTable Options button
- Finally under the Display tab in this window tick the option for ‘Classic pivot table layout…’
- Keep clicking OK until you are back in Excel
Following these steps means that you no longer have to keep changing the layout each time. The new default layout will be the classic one each time you create a PivotTable in Excel.
The video below goes through how you can change the layout to be classic manually by using the option on the ribbon as well as how to change the default settings. Changing the default settings can be good for if you prefer the classic layout as it means you don’t have to keep changing it each time. Take a look below at the video to find out more and then try it out on your own computer!
We hope you have enjoyed this hint and tip on PivotTable Layout Options in Excel. Why not take a look at our previous video hint and tip on the SUBTOTAL function and how to use it in Excel?