• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Classic PivotTable Default Layout

You are here: Home / Excel PivotTables / Classic PivotTable Default Layout
PivotTable Classic Layout
April 11, 2016 by Mynda Treacy

In Excel 2007 Microsoft thought they’d get clever and impose nested row labels in our PivotTables:

Nested PivotTable row labels

If you’re a PivotTable old-timer like me then you’ll most likely wish you could have the classic PivotTable default layout back:

Classic PivotTable Default Layout

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:

Classic PivotTable Default 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’:

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 😉 ):

Show in tabular form icon in QAT

PivotTable Classic Layout

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you the tricks to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.


Category: Excel PivotTables
Previous Post:Excel Tool BeltExcel PivotTable Default to SUM instead of COUNT
Next Post:Excel MROUND FunctionExcel MROUND Function

Reader Interactions

Comments

  1. Francisco

    June 27, 2020 at 12:55 am

    Is there something similar to pivot tables in Power BI? I’ve been looking for this for a long time. Thanks!

    Reply
    • Mynda Treacy

      June 28, 2020 at 7:54 pm

      Hi Francisco, Not exactly, but you could try a regular ‘Table’ in Power BI or a Matrix Table. Mynda

      Reply
  2. Peter Buyze

    May 5, 2016 at 7:01 pm

    What’s the advantage of the Classic lay-out? Is just a question of taste or is there something it does better than the “new” lay-out?

    Reply
    • Mynda Treacy

      May 5, 2016 at 7:15 pm

      Hi Peter,

      The Classic Layout is in more of a tabular format where there is one column for each item and this is useful when working with formulas related to the PivotTable etc., whereas the new report style layout has nested row labels which means one column could have multiple fields and this can make it difficult to work with.

      If you just need your PivotTable for a report then the new layout is fine, it’s more for when you want to perform further analysis on the PivotTable that it becomes annoying.

      Mynda

      Reply
      • Peter Buyze

        May 7, 2016 at 3:11 am

        OK Mynda, I understand. It might be an idea to add that comment to the spiel above, because you just state that the classic lay-out is for “old-timers” like you. But that is not the whole story as there is another reason too, as you point out.

        Reply
  3. Ted Wells

    April 16, 2016 at 1:17 am

    A lot of times when I go to your website, despite the fact I work in MS Excel all the time, I realize that I am an outsider and not a member of the club. You guys are so immersed in your Excel world you have no clue that outsiders need a few more directions.

    Let me illustrate: How to change a flat tire. Remove the lug bolts; take off the flat tire, put on the new tire; tighten the lug bolts. Still struggling to get the flat tire off? Oh, I forgot to tell you to jack up the car.

    I said all that to point out, it took me 15 minutes on Google to find the Pivot Table Options menu.
    And I have read Alan Wyatt’s Book on “Pivot Tables for the Faint of Heart. It is a confusing book with a lot of omissions for the newbie. No one if the office could tell me why I got the wrong dates when I opened up his workbook. I finally found the “Refresh” button after a month. Yeah, jack up the car. I made a lot of suggestions for his book, but I still have not sent them. I doubt if he would care anyway since he is so far above my level.

    So I said all that to cry out: Why didn’t you tell me at the beginning to bring up the Pivot Table Options Menu by placing your cursor on any cell in the pivot table and right click?

    I am having the same type trouble following your Excel Slicer Trick. Wow, there were a lot of omissions there also. I am still struggling with the hidden pivot table. But once I figure your stuff out it is worth it?? Like Livingston upon being shown Victoria Falls. “Yes it is worth seeing, but not worth going to see.”

    Reply
    • Mynda Treacy

      April 16, 2016 at 3:18 pm

      Hi Ted,

      The Options menu is fundamental to knowing how to use PivotTables, which is the level this post was pitched at, i.e. people who know how to use PivotTables learn where the Options menu is very early on in their learning journey. I can’t start at the beginning with every post so I have to assume a certain level of knowledge otherwise they would be too long. I’ll add a note about how to open the Options menu, BTW you’ll also find it on the PivotTable tab on the Ribbon which is active anytime you have a cell in a PivotTable selected.

      It sounds like you need to start at the beginning with PivotTables and I highly recommend this comprehensive PivotTable course that doesn’t leave any stone unturned. And if you’re quick you could catch our free PivotTable webinar which ends Monday April 18, 2016.

      Mynda

      Reply
  4. Paul W

    April 14, 2016 at 4:53 pm

    Hi Mynda,

    Is there any difference between the classic default layout tips above and just using the “Show in tabular form” option under Report layout on the Pivot Tables – Design tab? The latter would appear to be less clicks (but not as quick as the QAT tip!). Am I missing something?

    Reply
    • Catalin Bombea

      April 14, 2016 at 5:13 pm

      Hi Paul,
      It’s the same setting in fact, you can change that setting from the Design tab in ribbon or in Pivot Table options. As usual, the most frequently used commands are displayed in the right click menu too, not only in the Pivot Table Tools tab from ribbon.
      Same thing is for worksheet cells: in the right click menu, you can see commands from different Ribbon tabs: Format Cells (from Home tab), Define Name (from Formulas Tab), Hyperlink (from Insert tab)
      Cheers,
      Catalin

      Reply
  5. Anne Walsh

    April 14, 2016 at 4:27 am

    I found that if I actually add the number field before anything else that seems to lessen the occurrence of the cursed Count. Another thing that I find annoying is that if I want to group by figures e.g. currency or percentage, I either have to manually change the numbers to show currency or percent or locate a macro to do the same…grrr…another thing that’s annoying is how if you add a new item it doesn’t automatically go in the list in its correct alphabetical position – instead it just plonks itself at the end. 🙁

    Reply
    • Mynda Treacy

      April 14, 2016 at 9:15 am

      Hi Anne,

      PivotTables sure do have some annoying features. The number formatting is one of my biggest too. You should check out this PivotTable add-in as it makes working with PivotTables and their annoying features less of a pain by reducing the number of clicks required set your preferences etc.

      BTW, in my experience if there are any empty cells in a values column it will count the results irrespective of the order you add the field to the PivotTable. I suspect if it’s returning a SUM then there aren’t any blanks in your column.

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.