Drill Down in Pivot Chart with Power Pivot Hierarchy in Excel

A drill down in a Pivot Chart lets you start with a big-picture chart and double-click any slice to see what is inside it, level by level. In this tutorial you build exactly that in Excel with Power Pivot: a pie chart of sales by Manager that drills down to Supervisor, then Employee, then Product, and drills back up with a right-click. The secret is a hierarchy in the Power Pivot data model. You will also make the chart title and the Total Sales box change automatically with every level, so the chart always tells you where you are.

⬇️  Download the Drill Down Pivot Chart File
Workbook with Power Pivot data model · .xlsx (no macros) · pie and column drill-down charts

Watch the Video Tutorial

PK demos the finished pie chart, then rebuilds it: data model, hierarchy, Pivot Chart, dynamic title and total.

What You Will Build

• A four-level hierarchy (Manager > Supervisor > Employee > Product) in the Power Pivot data model.
• A drill-down pie chart: double-click a slice to move one level deeper.
• Drill up one level, or jump straight back to any level, from the right-click menu.
• Expand a single slice to the next level while the other slices stay as they are.
• A dynamic chart title and Total Sales box, for example Supervisor Level Sales and Total Sales: 2199.

The Sample Data

The Data sheet of the practice file holds an Excel table named Table2 (A1:E95) with 94 rows and five columns: Manager, Supervisor, Employee, Product and Sales.

• 3 managers, 7 supervisors, 19 employees and 16 products (P1 to P16).
• Total sales: 4201. Manager-1 has 2199, Manager-2 has 1088 and Manager-3 has 914.

How the Drill Down in a Pivot Chart Works

Open the Drill Down Chart Pie sheet and try it before you build it:

1. The chart starts at Manager Level Sales with Total Sales 4201.
2. Double-click the Manager-1 slice. The chart switches to Supervisor Level Sales for Manager-1 only: Supervisor-1 1104, Supervisor-2 648, Supervisor-3 447, and the total becomes 2199.
3. Double-click Supervisor-1. Now you see Employee Level Sales: Emp-1 253, Emp-2 297, Emp-3 200, Emp-4 211 and Emp-5 143 (total 1104).
4. Double-click Emp-2. The chart shows Product Level Sales: P3 35, P4 77, P5 55, P6 76 and P7 54 (total 297).
5. Product is the last level, so another double-click just shows a “can’t drill down” message.

Part 1 – Create the Hierarchy in Power Pivot

Step 1: Make sure your data is an Excel table

Click inside the data. If the Table Design tab does not appear, press Ctrl + T to convert it into a table.

Step 2: Add the table to the data model

With any cell of the table selected, go to the Power Pivot tab and click Add to Data Model. The Power Pivot window opens and Table2 is loaded as a linked table.

Step 3: Switch to Diagram View

In the Power Pivot window go to Home → View → Diagram View. Table2 appears as a box with its five fields.

Step 4: Create the hierarchy

There are two ways to do it, and the video shows both:

• One field at a time: right-click Manager and choose Create Hierarchy. Hierarchy1 is created with Manager as its first level. Then drag Supervisor, Employee and Product one by one into the hierarchy, each below the previous one.
• All at once: Ctrl-click Manager, Supervisor, Employee and Product, right-click and choose Create Hierarchy. Excel may not put the levels in your order, so drag them with the mouse until the order is Manager, Supervisor, Employee, Product.

All the hierarchy columns must sit in the same table. If one of them lives in a related table, bring it in first with a calculated column such as =RELATED('Product Master'[Category]), then add that column to the hierarchy.

Part 2 – Build the Drill-Down Pivot Chart

Step 5: Insert a Pivot Chart from the data model

In the Power Pivot window click Home → PivotTable → PivotChart and place it on a new worksheet. In the PivotChart Fields list:

• Drag Hierarchy1 to Axis (Categories).
• Expand More Fields and drag Sales to Values. It becomes Sum of Sales.

A column chart appears at Manager level. Double-click a column and it drills to the supervisors, then to the employees, then to the products.

Step 6: Hide the field buttons and change to a pie

Select the chart, go to PivotChart Analyze (called Analyze in Excel 2013) → Field Buttons → Hide All. To make a pie, go to Design → Change Chart Type and pick Pie or 3-D Pie. The practice file uses a 3-D pie on one sheet and a column chart on another; both drill the same way.

Part 3 – Drill Up, Drill Down and Expand

Drill down with a double-click

Double-click any slice or column to move one level down for that item only. The Drill Down and Drill Up buttons in the Active Field group of the PivotChart Analyze tab do the same for the selected slice.

Drill up from the right-click menu

Select a slice, right-click and open Drill Down/Drill Up. Drill Up goes back one level. The same menu lists Drill Up to Manager, Drill Up to Supervisor and so on, so you can jump straight back to the top.

Expand only one slice

To open up Manager-1 while Manager-2 and Manager-3 stay as they are, right-click the Manager-1 slice and choose Expand/Collapse and expand it to the Supervisor level. The pie now shows Manager-2, Manager-3 and three slices labelled Manager-1 Supervisor-1, Manager-1 Supervisor-2 and Manager-1 Supervisor-3.

Part 4 – Dynamic Title and Total Sales

A Pivot Chart title does not change on its own when you drill, so PK drives the chart from a small PivotTable on a support sheet (Sheet1 in the practice file).

Step 7: Create a PivotTable from the data model

From the Power Pivot window insert a PivotTable on a new sheet. Put Hierarchy1 in Rows and Sales in Values. Then go to Design → Report Layout → Show in Tabular Form. In tabular form the header cell (B3) shows the name of the current level, such as Manager or Supervisor, instead of “Row Labels”.

Step 8: Add the helper formulas

The practice file uses three cells next to the PivotTable:

G1  =GETPIVOTDATA("[Measures].[Sum of Sales]",$B$3)
H1  ="Total Sales: "&TEXT(G1,"0")
H2  =B3&" Level Sales"

How the formulas work:

• G1 reads the PivotTable Grand Total. With a data model the field is named after the measure, [Measures].[Sum of Sales]; type = and click the Grand Total cell and Excel writes it for you.
• H1 joins a label to that total. TEXT(G1,”0″) shows it as a whole number, so you get Total Sales: 2199.
• H2 takes the level name from the header cell B3 and adds ” Level Sales”, giving Manager Level Sales, Supervisor Level Sales and so on.

Step 9: Build the chart from this PivotTable

Select a cell in the PivotTable and go to Insert → Pie Chart (or PivotChart). Hide all field buttons again. If you want the chart on its own dashboard sheet, cut it with Ctrl + X and paste it there; it stays connected to the PivotTable. Chart and table share one pivot, so drilling the chart drills the table too.

Step 10: Link the title and add a total box

• Dynamic title: click the chart title, click in the formula bar, type =, click cell H2 on the support sheet and press Enter. The title now reads =Sheet1!$H$2.
• Total Sales box: select the chart, go to Insert → Text Box and draw it inside the chart. With the text box selected, type = in the formula bar, click H1 and press Enter. Format it with any shape style.

Step 11: Format the data labels

Add data labels, then open Format Data Labels: set the position to Outside End and tick Category Name next to Value. The practice file adds a note on the chart: Double Click on any slice to Drill Down.

To get the column version, select the chart and use Change Chart Type. The title, total and drill behaviour stay the same.

Common Errors and Fixes

Double-click does not drill down

The double-click drill in this tutorial needs the data in the data model with a hierarchy on the axis. A chart built from a normal range, or one with separate fields on the axis, will not drill level by level. Add the table to the data model, create the hierarchy and build the chart from the Power Pivot window.

Drill Down and Drill Up are greyed out

Either no slice or column is selected, or the axis holds separate fields instead of the hierarchy. Put Hierarchy1 itself in the Axis area and select one data point.

The title shows Row Labels

The PivotTable is still in Compact Form. Switch it to Show in Tabular Form so the header cell shows the level name.

Tips

• Pie charts read best with a handful of slices. If a level has many items, the column chart is easier to read.
• Microsoft’s guide to hierarchies in Power Pivot covers renaming levels and removing them.

Want It Ready-Made?

If you would rather start from a finished interactive chart, the Stylish Pivot Chart with Slicer in Excel and 3 Useful Tips for the Pivot Chart templates on NextGenTemplates.com are ready to use, and you can browse more Excel chart templates. To learn pivot tables, pivot charts, slicers and Power Pivot step by step, join the video course Excel Pivot Tables and Dashboards.

Related Tutorials

• Pivot Chart in Excel: Create, Filter and Hide Field Buttons
• Create Relationship in Power Pivot
• Calculated Column in Power Pivot: LOOKUPVALUE vs RELATED
• GETPIVOTDATA in Excel: Pull Pivot Table Values into Formulas
• Power Pivot KPIs in Excel: Create, Edit and Delete

Frequently Asked Questions

How do I drill down in a Pivot Chart in Excel?

Load your table into the Power Pivot data model, create a hierarchy of the fields you want to drill through, and put that hierarchy on the axis of a Pivot Chart. Then double-click any slice or column to move one level down.

How do I drill back up in a Pivot Chart?

Right-click a slice and choose Drill Down/Drill Up, then Drill Up for one level or Drill Up to a named level to jump straight back. The Drill Up button on the PivotChart Analyze tab does the same.

Do I need Power Pivot to drill down in a chart?

For the double-click drill shown here you need the data in the data model with a hierarchy, and hierarchies are created in the Power Pivot window. Without a hierarchy, a chart with several fields on the axis shows them together instead of drilling level by level.

Why does my chart title not change when I drill down?

A Pivot Chart title is static. Link it to a cell that builds the text from the header of a Tabular Form PivotTable, as shown in Part 4, and it will follow every drill.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, Power Pivot and Power BI since 2016 on his YouTube channel.

Conclusion

A drill down in a Pivot Chart turns one chart into four: Manager, Supervisor, Employee and Product, all one double-click apart. Create the hierarchy once in Power Pivot, put it on the chart axis, and use the right-click menu to drill up or expand a single slice. Add the small support PivotTable with GETPIVOTDATA and a linked title, and your chart always shows the right level and total. Download the practice file and try drilling into Manager-1 yourself.