Excel Pivot Chart Drill Down Buttons

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.

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.

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.

Please Share

If you liked this please click the buttons below to share.