• 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
    • 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
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Pivot Chart Drill Down Buttons

You are here: Home / Excel Charts / Excel Pivot Chart Drill Down Buttons
September 7, 2017 by Mynda Treacy

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.

excel pivot chart drill down buttons

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

drag fields between areas below

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.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Watch the Video

Subscribe YouTube

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:

enable drill down 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:

using excel pivot chart drill down buttons

As you do so the overall chart size remains the same, so it won’t mess up your report layout:

overall chart size

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:

excel pivot chart drill down pre-excel 2016

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:

double click the axis

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.

More Pivot Chart Posts

Change PivotTable Aggregation Methods using Excel Slicers

Change PivotChart Aggregation Methods using Excel Slicers

Change Pivot Chart Aggregation Methods using Excel Slicers and Power Pivot. Disconnected tables and the SWITCH function are the secret sauce.
pivot charts

Pivot Charts

How to insert Excel Pivot Charts. Includes workbook and video.

More Excel Charts Posts

excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.
stacked bar waffle chart

Stacked Bar Excel Waffle Charts

Stacked Bar Excel Waffle Charts are an alternate to using conditional formatting to build waffle charts, and some say they're easier.
Category: Excel ChartsTag: pivot chart
Previous Post:Excel Extract a Unique List
Next Post:VBA Shellvba shell

Reader Interactions

Comments

  1. Ashmi

    September 10, 2020 at 9:07 pm

    Wow this website is amazing! I have learnt so much from this website. Thank you miss πŸ™‚

    Reply
    • Mynda Treacy

      September 10, 2020 at 9:09 pm

      So pleased to hear that, Ashmi! Thank you πŸ™‚

      Reply
  2. Oscar

    May 1, 2019 at 8:52 am

    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.

    Reply
    • Mynda Treacy

      May 1, 2019 at 8:56 am

      Hi Oscar,

      Right click a date/month/year cell in the PivotTable > Group. In the Group dialog add ‘Days’ to the grouping.

      Mynda

      Reply
      • Oscar

        May 3, 2019 at 12:16 am

        Worked like a charm Mynda… thanks a lot!!!

        Reply
  3. Chuck Trese

    March 19, 2019 at 6:22 am

    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)

    Reply
    • Mynda Treacy

      March 19, 2019 at 6:31 am

      Hi Chuck,

      No, you can’t protect the Pivot Chart object and still use the expand/collapse/filter buttons on the chart.

      Mynda

      Reply
  4. Pieter-Jan

    January 18, 2019 at 2:26 am

    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

    Reply
    • Mynda Treacy

      January 22, 2019 at 6:08 pm

      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

      Reply
  5. John DiMartino

    May 4, 2018 at 2:06 am

    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.

    Reply
    • Mynda Treacy

      May 4, 2018 at 8:03 am

      My pleasure, John. Great to know you’re finding them useful.

      Reply
  6. jim

    September 7, 2017 at 9:20 pm

    I look forward to trying that out in 2023 πŸ™
    But the right-click tip is useful – thanks

    Reply
    • Mynda Treacy

      September 8, 2017 at 8:44 am

      πŸ™‚ hopefully you won’t have to wait that long!

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

Shopping Cart

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
  • 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
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

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.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x