• 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
    • SALE 20% Off All Courses
    • 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
    • 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
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel PivotTable Expand, Collapse and Show Details

You are here: Home / Excel PivotTables / Excel PivotTable Expand, Collapse and Show Details
Excel PivotTable Expand and Collapse options
February 25, 2015 by Mynda Treacy

Excel PivotTables are a treasure trove of features. One of my favourites is the ability to expand/collapse and drill down into the data.

Let’s look at an example; the first column of the PivotTable below lists Categories, which group and summarise Products. We can click on the + symbols to the left of the Category name to drill down and reveal the Products within a Category:

Excel PivotTable expand/collapse buttons

Turning On/Off Expand/Collapse Buttons

If the +/- symbols aren’t visible in your PivotTables you can turn them on in the PivotTable Options > Display tab > ‘Show expand/collapse buttons’:

turn Excel PivotTable expand/collapse buttons on and off

PivotTable Expand/Collapse Menu

Another way to expand/collapse is to right-click the row label to reveal the Expand/Collapse menu:

Excel PivotTable expand/collapse menu

  1. Choose to Expand/Collapse just one subtotal, or
  2. Expand/Collapse an entire field, or
  3. if you have multiple levels of row labels you can choose which level you want to expand/collapse to

Tip: Double clicking the Row labels in the PivotTable also expands/collapses one subtotal at a time.

PivotTable Show Details

But what if we want to see the underlying transactions that make up the subtotals? That’s easy too. We can right-click the value (as opposed to the row label) and choose ‘Show Details’:

Excel PivotTable Show Details

This will insert a new sheet with a list of all the transactions that make up the subtotal amount:

Excel PivotTable show details results

Notice how the data is already nicely formatted in an Excel Table so we can filter it if we want.

Tips:

  1. Double clicking the cell containing the value you want to drill down on will also do this.
  2. You can drill down from any level to extract the level detail that you want. For example double-clicking on the grand total amount will give you all of the transactions that make up the PivotTable.
  3. Tip 2 is why you can go ahead and delete your original PivotTable source data, which is handy if your file is getting too big. Assuming your source data is in the same file as your PivotTable and it’s not going to change or need updating.

Group/Ungroup Selections

So far we’ve looked at the built in grouping that PivotTables automatically provide based on your source data, but you can add your own groups too.

Let’s say I wanted to group all of the meat/poultry and Seafood categories together. It’s easy:

Select the rows you want in your new group > right-click > Group.

Tip: hold down the CTRL key to select non-consecutive categories. I’m going to group Meat/Poultry and Seafood:

Excel PivotTable group items

Now you have a new group in your PivotTable called Group1:

Excel PivotTable new group

To rename it simply type over the default name ‘Group1’. I’ll call it ‘Butcher’.

If you want to display the Subtotal for your new group then right-click the row label and select ‘Subtotal…”:

Excel PivotTable subtotal new group

Want More

If you'd like to learn more tricks like this and really master PivotTables then go ahead and join 1000's of others and check out our Xtreme PivotTable course.

Excel PivotTable Expand and Collapse options

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 how 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:find missing numbers really fastFind Missing Numbers, Really Fast
Next Post:Excel PivotTable FAQ’s

Reader Interactions

Comments

  1. Dan

    August 16, 2018 at 5:45 am

    Is there a way when press +(collapse), it still display the detail of the description? It always displays the detail of the description when it – (expands). Thanks. Dan

    Reply
    • Mynda Treacy

      August 16, 2018 at 9:37 am

      Hi Dan,

      I’m not sure what you mean by the ‘detail of the description’. It displays the subtotal row labels.

      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...

Course Sale

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.

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

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
trustpilot excellent rating
 

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.