Excel Pivot Chart Drill down buttons are a new feature available in Excel 2016. You’ll find them in the bottom right of your Pivot Chart.
They’re available whenever you have more than one field in the Axis area of the Pivot Chart (which is the Rows area of your PivotTable). You can see below that I have 3 fields in my Axis area; years, quarters and date (which is month).
My fields are a result of grouping the date field in my PivotTable, but it isn’t limited to date hierarchies. You can add any fields to the Axis area and drill up/down.
Download the Workbook
Note: if you open this file in versions of Excel earlier than 2016 you won’t see the drill down buttons, but you can still use the double-click or right-click axis technique.
Enter your email address below to download the sample workbook.
Watch the Video
Enable Drill Down Buttons
If you can’t see the drill down buttons on your Excel Pivot Chart you can turn them on by selecting the chart > PivotChart Tools: Analyze tab > Field Buttons > Show Expand/Collapse Entire Field Buttons:
PivotCharts created in earlier versions of Excel will need the buttons enabled. PivotCharts created in Excel 2016 will simply not display them in earlier versions of Excel.
Using Excel Pivot Chart Drill Down Buttons
Drill Down buttons are an intuitive way for your users to drill up and down through a hierarchy:
As you do so the overall chart size remains the same, so it won’t mess up your report layout:
Each click drills up/down one level at a time.
They’re a nice new feature, especially for those of us who build Excel Dashboard reports, but what if you don’t have Excel 2016? Well, there’s a stealth way to drill down in PivotCharts that’s been around since (at least) Excel 2007.
Excel Pivot Chart Drill Down Pre-Excel 2016
To drill up/down in PivotCharts in versions of Excel prior to 2016 you need to left click the axis to select it > then right-click > Expand/Collapse and from here you can choose from the options:
Another quick way to drill down is to left click the axis to select it, then double click to drill down one level at a time. Unfortunately, you can’t drill up or collapse with the double click, instead you must right-click and select Collapse.
If you double-click the axis and it’s already fully expanded you’ll get a list of fields that you can add to the rows area of the PivotTable. Anything you choose here will appear in the horizontal axis of the PivotChart:
The only downside of the drill down buttons is that they can only drill down on one chart at a time. It would be nice if you could link them to other charts, like you can with Slicers, to drill down multiple charts at the same time.
Excel Extract a Unique List
Ashmi
Wow this website is amazing! I have learnt so much from this website. Thank you miss 🙂
Mynda Treacy
So pleased to hear that, Ashmi! Thank you 🙂
Oscar
Hello,
I have a pivot chart just like yours, indicating “years”, “quarters” and “date” fields for 2 years (2018 & 2019) of data, but when I drill down the deeper that I can go is “month” and not DAYS… how can I make the chart to display even the DAY data?
Regards.
Mynda Treacy
Hi Oscar,
Right click a date/month/year cell in the PivotTable > Group. In the Group dialog add ‘Days’ to the grouping.
Mynda
Oscar
Worked like a charm Mynda… thanks a lot!!!
Chuck Trese
Is there a way to allow users to use the +/- buttons on a locked (and protected) pivotchart?
Basis for my question: I don’t want the user to be able to (accidentally) reposition or resize the chart (as that messes up my “dashboard”), but i want them to be able to expand/collapse as needed.
If I leave the chart unlocked, then the layout is easily messed up.
If I lock the chart, then the +/- buttons cannot be accessed (even though associated Slicers can be accessed)
Note: in my case, the user does not have access to the Pivot Table itself. All they see are several Slicers, and the associated PivotChart. I have removed all other buttons from the chart, prefer to keep only the +/- buttons.
(using Office Professional Plus 2016 on Windows 10)
Mynda Treacy
Hi Chuck,
No, you can’t protect the Pivot Chart object and still use the expand/collapse/filter buttons on the chart.
Mynda
Pieter-Jan
Please replace drill down and drill up by expand and collapse, respectively. Drilling down or up is a similar yet different feature associated with the Online Analytical Processing (OLAP) cube or Data Model-based PivotTable hierarchy – see link below.
https://support.office.com/en-us/article/Drill-into-PivotTable-data-c1b11240-fc8f-4fdd-a697-629bf6f7ee0b
Mynda Treacy
Hi Pieter-Jan,
Thanks for sharing the link.
While Microsoft have named the buttons/action ‘expand’ and ‘collapse’, I used the terms ‘drill up’ and ‘drill down’ because this is how most people think of this action.
Mynda
John DiMartino
Thank you for taking time and putting forth the effort to provide these informative and useful examples of Pivot Table features. I’m learning new things from you every time I access your pages. What a blessing.
Mynda Treacy
My pleasure, John. Great to know you’re finding them useful.
jim
I look forward to trying that out in 2023 🙁
But the right-click tip is useful – thanks
Mynda Treacy
🙂 hopefully you won’t have to wait that long!