Note that from the Continent level, you can click Expand Field to show Continents and Countries. At this point, the Drill Down button is greyed out. Drill Down three times and I end up at the cities in the Carolinas region. Note at this point, both the Drill Down and Drill Up buttons are enabled.ĭrill Down and Drill Up buttons are enabled. With the cell pointer on Canada, click Drill Down and you will see Eastern Canada and Western Canada. With the cell pointer on North America, click Drill Down and Continent is replaced by Country. However, if you move the cell pointer to North America in A4, you will see that Drill Down is enabled. When you initially create the pivot table, the active cell is on A3 and the Drill Down icon is greyed out. There is a lot to notice in the image above. Open More Fields by clicking the triangle next to it. To build the pivot table, check the box for the Geography hierarchy.
But I don't understand why they hide Sales under More Fields. I somewhat understand why they hide Continent, Country, Region, Territory, City under More Fields.
Your Sales field is hidden under More Fields.
The Pivot Table Fields now shows the Geography hierarchy and More Fields. Right-click Hierachy1 and choose Rename.Ĭlose Power Pivot and return to Excel. If you click away from Power Pivot, Hierarchy1 is no longer in Rename mode. Hierarchy1 is created and is waiting for you to type a new name. Once you have the fields selected, right-click any of the fields and choose Create Hierarchy. 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.
#Tabular pivot table in excel download#
(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.
0 kommentar(er)
