In Excel 2007 Microsoft thought they’d get clever and impose nested row labels in our PivotTables:
If you’re a PivotTable old-timer like me then you’ll most likely wish you could have the classic PivotTable default layout back:
Turn On Classic PivotTable Default Layout
The retro Excel 2003 looking PivotTable above is still available in Excel 2007, 2010, 2013 and 2016. You just have to enable it in the PivotTable Options (right-click PivotTable > Options) > Display tab > Classic PivotTable Layout:
If you go back into the PivotTable Options and uncheck the ‘Classic PivotTable Layout’ you’ll find the tabular format sticks and the retro blue lines around your PivotTable are gone.
Alternatively you can set each of the row labels Field Settings > Layout & Print > ‘Show item labels in tabular form’:
But this is a lot more work if you have many row labels since you have to set this individually for each one. It’s easier to apply the Tabular layout using the ‘Classic PivotTable Layout’ option as this automatically selects the ‘Show item labels in tabular form’ for each field.
Classic PivotTable Default Layout Shortcut
Tip: if you find yourself choosing the Classic PivotTable Layout regularly then you can go ahead and add the ‘Show in Tabular Form’ icon to your Quick Access Toolbar. And while you’re there you’ll probably want to add the ‘Do Not Show Subtotals’ icon too (after all, it doesn’t cost any more to add both of these icons 😉 ):
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.