You could also click on one item, and Ctrl-Click on others if the hierarchy fields are not adjacent. Shift-Click on the last item in the hierarchy (City in my example). Click on the first item in your hierarchy (Continent in my example). Use the resize handle in the lower right corner of Table1 to enlarge Table1 so you can see all of your fields. It is near the right side of the Home tab. In the Power Pivot for Excel window, click on the Diagram View icon. Manage button on the Power Pivot tab in the Ribbon. (Many instances of Excel 20 do not have this tab. Here is the PivotTable Fields before you create the hierarchy.Ĭlick the Manage icon on the Power Pivot tab in the Ribbon. In the Create PivotTable dialog, choose the box for Add This Data to the Data Model. Make sure that the option for My Table Has Headers is selected. Look for the Power Pivot tab to the left of Pivot Table Tools in Excel.įirst step - convert your pivot source data set to a table using either Home - Format as Table or Ctrl + T. (Or, if you just want to try the feature, download the Excel file that I created: Hierarchy.xlsx) 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. Look for the Power Pivot tab to the left of PivotTable Tools tabsĪfter 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. Why are these perpetually greyed out? They take up a lot of space in the Ribbon. Recently, a friend of mine wondered about the Drill-Down and Drill-Up buttons in the Pivot Table Tools tab of the Ribbon.

How to make pivot tables in excel 2013