How to Create a Hierarchy in Excel: 2 Easy Methods

A quick guide to create an organizational chart in Excel

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.

Creating a Hierarchy Chart in Excel

  1. Click Insert and then SmartArt .
  2. Click the Hierarchy tab and select a design, then click OK .
  3. Select an existing box and click Add Shape to manage boxes.
  4. Select a box to edit text or use the Type your text here box.
  5. Click Insert and then Shapes to add lines between boxes.
  6. Right-click anywhere and choose Change Layout to alter your design.
Method 1
Method 1 of 2:

Creating a Hierarchy in a Power Pivot Data Model

PDF download Download Article
  1. 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: [1]
    • 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 .
  2. 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
  3. Here's how:
    • Click the Insert tab.
    • Click PivotTable .
    • Check the box next to "Add this data to the Data Model" and click OK .
  4. Now that you've enabled the add-in, you'll have a new tab called "Power Pivot" at the top of Excel. [2]
  5. This opens the Power Pivot window, where you'll see the data from your table in a pivot table.
  6. To do this, click the View icon at the top of the new window and select Diagram View . [3]
  7. To select multiple table columns, hold down the Ctrl key as you click each column name.
  8. This creates a new parent hierarchy level below the table columns. Each column is now a child in the hierarchy.
  9. You can always rename the hierarchy or any of the child levels by right-clicking and selecting Rename .
  10. Before you start working with your data, you might need to change the order of the items in your hierarchy: [4]
    • 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 .
  11. 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.
    • Drag any fields you want to work, including your hierarchy field, with into the Rows , Columns , Values , or Filters area as needed.
    • If you don't see your other fields in the PivotTable Fields panel, click the arrow next to "More Fields."
  12. 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.
    13. Advertisement
Method 2
Method 2 of 2:

Creating a Hierarchy Chart

PDF download Download Article
  1. You'll see this tab at the top of Word. [5]
  2. 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. [6]
  3. This tab is on the left panel of the Choose a SmartArt Graphic window. Now you'll see the available hierarchy designs. [7]
  4. This inserts a blank hierarchy graphic into your worksheet. [8]
    • 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.
  5. 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: [9]
    • 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. [10]
  6. 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.
  7. 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: [11]
    • 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. [12]
    • 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.
    9. 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
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!

      You Might Also Like

      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
      How to
      Change from Lowercase to Uppercase in Microsoft Excel
      How to
      Link Sheets in Excel
      How to
      Make a Spreadsheet in Excel
      New to Excel? Here's Super Easy Tricks to Get You Started
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/start-the-power-pivot-add-in-for-excel-a891a66d-36e3-43fc-81e8-fc4798f39ea8
      2. ↑ https://support.microsoft.com/en-us/office/start-the-power-pivot-add-in-for-excel-a891a66d-36e3-43fc-81e8-fc4798f39ea8
      3. ↑ https://support.microsoft.com/en-us/office/hierarchies-in-power-pivot-002cf883-3b5f-497c-bfa1-ab2271cdb73b
      4. ↑ https://support.microsoft.com/en-us/office/hierarchies-in-power-pivot-002cf883-3b5f-497c-bfa1-ab2271cdb73b
      5. ↑ https://support.microsoft.com/en-us/office/create-a-hierarchy-45b915d6-eef0-4722-a7ac-b42f1ffe7c3c
      6. ↑ https://support.microsoft.com/en-us/office/create-a-hierarchy-45b915d6-eef0-4722-a7ac-b42f1ffe7c3c
      7. ↑ https://support.microsoft.com/en-us/office/create-a-hierarchy-45b915d6-eef0-4722-a7ac-b42f1ffe7c3c
      8. ↑ https://support.microsoft.com/en-us/office/create-an-organization-chart-in-office-by-using-smartart-9b51f667-11b7-4971-a757-a08a36684ee6
      9. ↑ https://support.microsoft.com/en-us/office/create-an-organization-chart-in-office-by-using-smartart-9b51f667-11b7-4971-a757-a08a36684ee6
      1. ↑ https://support.microsoft.com/en-us/office/create-a-hierarchy-45b915d6-eef0-4722-a7ac-b42f1ffe7c3c
      2. ↑ https://support.microsoft.com/en-us/office/create-an-organization-chart-in-office-by-using-smartart-9b51f667-11b7-4971-a757-a08a36684ee6
      3. ↑ 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?
      In other languages
      Thanks to all authors for creating a page that has been read 98,193 times.

      Is this article up to date?

      Advertisement