Do you want to create a hierarchy in your Excel worksheet? "Hierarchy" in Excel actually has two meanings—the first (and simpler) meaning is a type of chart that helps you visualize a hierarchical structure, such as an organizational chart. The other type of hierarchy is a Power Pivot hierarchy, which allows you to easily drill up and down through a list of nested columns in a table. This wikiHow article will walk you through two ways to create hierarchies in Microsoft Excel—creating a hierarchy chart using SmartArt, and creating a Power Pivot data model hierarchy.
Things You Should Know
- To create a hierarchy in a pivot table, you'll need to enable the Power Pivot add-on in Microsoft Excel.
- If you want to create a hierarchy chart to show relationships between items, you can use a SmartArt design template.
- PowerPivot is only available in recent versions of Microsoft Excel for Windows.
Steps
-
Enable the Power Pivot add-on. If you want to create a hierarchy that rolls a list of columns from a table into a single item in a pivot table, you can do so with the Power Pivot Excel add-on. This allows you to use your hierarchy in a Power Pivot table as a single object in the field list. Here's how to enable the add-on:
- First, you'll need to be using Excel 365, 2021, 2019, 2016, or 2013 to use this feature.
- Click the File menu and select Options .
- Click the Add-ins tab on the left.
- Select COM Add-ins from the "Manage" menu.
- Click Go .
- Check the "Microsoft Office Power Pivot" box and click OK .
-
Convert your source data into a table. To create the hierarchy, you'll need to create a Power Pivot table, which is different from a standard pivot table . To prepare your source data:
- Highlight the data and press Ctrl + T .
- Select "My table has headers" and click OK .
Advertisement -
Add the table to the data model. Here's how:
- Click the Insert tab.
- Click PivotTable .
- Check the box next to "Add this data to the Data Model" and click OK .
-
Click the Power Pivot tab. Now that you've enabled the add-in, you'll have a new tab called "Power Pivot" at the top of Excel. [1] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Click Manage . This opens the Power Pivot window, where you'll see the data from your table in a pivot table.
-
Switch the Power Pivot window to Diagram view. To do this, click the View icon at the top of the new window and select Diagram View . [2] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Select the columns you want to add to the hierarchy. To select multiple table columns, hold down the Ctrl key as you click each column name.
-
Right-click the selected columns and select Create hierarchy . This creates a new parent hierarchy level below the table columns. Each column is now a child in the hierarchy.
-
Type a name for the hierarchy and press ↵ Enter . You can always rename the hierarchy or any of the child levels by right-clicking and selecting Rename .
-
Edit the hierarchy. Before you start working with your data, you might need to change the order of the items in your hierarchy:
- To create more child levels, drag additional columns to the parent level. Or, to place a column at a particular level in the hierarchy, drag the column exactly where you'd like it to appear.
- To rename a parent or child level, just right-click the current name and choose Rename . But be careful—if you rename a child level, it will no longer share the same name as its originating column.
- To change the order of a child level, just drag the item to a new position.
- To remove a child level, right-click the level and select Remove from Hierarchy .
-
Close the PowerPivot window and build your pivot table. Now you'll be able to build your pivot table and include the hierarchy. You'll see the hierarchy under the name of the table in the PivotTable Fields panel.
-
Use the Drill Up and Drill Down icons to navigate through the hierarchy. Once you've placed your hierarchy field in your pivot table, you'll be able to use these buttons on the PivotTable Analyze tab to move up and down through the levels. The data in your pivot table will update along with your hierarchy selection.
Advertisement
-
Click the Insert tab. You'll see this tab at the top of Word.
-
Click the SmartArt icon. Its icon is a green arrow with a window at its bottom-right corner. If you don't see this option in the toolbar, click Illustrations to expand more options.
-
Click the Hierarchy tab. This tab is on the left panel of the Choose a SmartArt Graphic window. Now you'll see the available hierarchy designs.
-
Select a hierarchy design and click OK . This inserts a blank hierarchy graphic into your worksheet.
- While each design only includes a few boxes for your hierarchy, you can add as many as necessary once you've added the graphic to your sheet.
-
Add a box or shape to the hierarchy. While the sample hierarchy might have enough boxes for some, you'll usually need to tweak it a bit to match your needs. Adding a box to the hierarchy is easy:
- Click any of the existing boxes. This activates the SmartArt Design toolbar.
- Click the down-arrow next to "Add Shape" in the toolbar to bring up the menu.
- Choose a position relative to the selected box:
- Add Shape After: Adds a shape at the same level to the right of the selected shape.
- Add Shape Before: Adds a shape at the same level to the left of the selected shape.
- Add Shape Above: Adds a shape that takes the position of the selected shape and demotes the shapes beneath it.
- Add Shape Below: Adds a shape one level below the selected shape. If there's already a shape a level below, the new shape will appear to the right of that shape. [3] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Add text to the hierarchy. Each of the pre-made designs allows you to click some sample text and replace it with your own. You can click the box you want to edit and type right into the box, or use the "Type your text here" box on the left to enter your text.
- Each shape or box on the hierarchy has its own entry in the "Type your text here" panel.
- You can choose any font face, style, and color on the Home tab for your boxes.
-
Connect two non-connected branches. When you add a box or shape to the hierarchy, Excel automatically inserts a line connecting the new shape to the shape you selected. However, none of the SmartArt hierarchy designs are capable of connecting two top-level boxes. In this case, you'd want to draw a line or connector between the two shapes yourself. Here's how:
- Click the Insert tab and select Shapes .
- Select a line style, such as a straight or squiggly line.
- Click and drag the mouse from the starting box to the ending box. [4] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
-
Change the look of your hierarchy.
- To switch to a different hierarchy style, right-click anywhere on the hierarchy and select Change Layout . Choosing another design will move your text and other details into that design.
- To move a shape, just click and drag it to any location. To move it in tiny increments, hold down the CTRL key as you drag.
- To change the colors, shading, shadows, or outlines of the hierarchy, click anywhere on the chart to bring up the SmartArt Design toolbar at the top. Then, click Change Colors to try a different color scheme, or choose a different shape style on the SmartArt Styles panel.
Advertisement
Expert Q&A
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
Video
Tips
Submit a Tip
All tip submissions are carefully reviewed before being published
Thanks for submitting a tip for review!
References
- ↑ https://support.microsoft.com/en-us/office/start-the-power-pivot-add-in-for-excel-a891a66d-36e3-43fc-81e8-fc4798f39ea8
- ↑ https://support.microsoft.com/en-us/office/hierarchies-in-power-pivot-002cf883-3b5f-497c-bfa1-ab2271cdb73b
- ↑ https://support.microsoft.com/en-us/office/create-a-hierarchy-45b915d6-eef0-4722-a7ac-b42f1ffe7c3c
- ↑ https://support.microsoft.com/en-us/office/draw-or-delete-a-line-or-connector-f304ef73-9514-450b-9bb9-28c6057020f2
About This Article
Article Summary
X
1. Open a new Excel spreadsheet.
2. Click Insert
at the top of your screen.
3. Click SmartArt
.
4. Hover over Hierarchy
.
5. Select a hierarchy chart.
6. Fill out the boxes on your hierarchy chart.
Did this summary help you?
Thanks to all authors for creating a page that has been read 87,774 times.
Advertisement
