Creating a Hierarchy in a Pivot Table
April 26, 2018 - by Bill Jelen
Recently, a friend of mine wondered about the Drill-Down and Drill-Up buttons in the Pivot Table Tools tab of the Ribbon. Why are these perpetually greyed out? They take up a lot of space in the Ribbon. How is anyone supposed to use them?
After some research, there is a way to use them, but you have to use the Data Model and use the Power Pivot diagram view to create a hierarchy. If you don't have the Power Pivot tab in your Ribbon, you will have to find a co-worker who has the button in order to create the hierarchy. (Or, if you just want to try the feature, download the Excel file that I created: Hierarchy.xlsx)
First step - convert your pivot source data set to a table using either Home - Format as Table or Ctrl + T. Make sure that the option for My Table Has Headers is selected.