How to Create a Hierarchy in Excel Using Different Methods

Overview to Hierarchy in Excel


What Is Hierarchy?

  1. Hierarchical Chart:
    • The term “hierarchy” has a dual meaning. First, it refers to a specific type of chart used to visualize hierarchical structures, such as organizational charts. These charts help represent relationships within an organization or other structured systems.
  2. Structured Data Arrangement:
    • Beyond charts, hierarchy also denotes a structured arrangement of data. In this context, items are organized into levels or categories based on their relationships. For example, you might organize data by department, region, or product category.
  3. Excel and Hierarchies:
    • Excel leverages hierarchies in conjunction with pivot tables and Power Pivot. These features allow you to analyze and summarize data hierarchically, making it easier to understand complex relationships and make informed decisions.

Method 1 – Using SmartArt Feature

  • Select Your Raw Data:
    • Highlight the range of cells that you want to include in your hierarchy. For example, let’s say your data is in cells B5 to B11.
  • Insert SmartArt:
    • Click on the Insert tab in Excel.
    • Navigate to Illustrations and choose SmartArt.

Launching SmartArt Feature

    • In the dialog box that appears, select Hierarchy.
    • Choose a hierarchy style that suits your needs and press OK.

Choosing a SmartArt Graphic

  • Customize the Hierarchy:
    • Click on the arrow sign within the SmartArt graphic.
    • Place the cursor inside the hierarchy chart and press CTRL+A to select all default text.

Selecting All the Attributes

    • Press Delete to remove the default text.

Deleting All the Attributes

  • Organize the Hierarchy:
    • Copy the range B5:B11 (your raw data) and paste it onto the SmartArt graphic.
    • Arrange the hierarchy by placing managers below the CEO. Select each manager individually and press Tab once for each.
    • For executives, press Tab twice for each of them.

Creating Hierarchy in Excel

    • If you need a more detailed organogram, repeat the process by pressing Tab thrice or more for additional levels below executives.
  • Customize Appearance:
    • Choose a layout from the available options.
    • Select a style from SmartArt Styles to give your hierarchy a customized look.

Fixing Layout and SmartArt Styles of Hierarchy

Method 2 – Using Pivot Table

  • Select Your Data Range:
    • Highlight the range B4:D10 (or any relevant data).
  • Insert a Pivot Table:
    • Click on the Insert tab.
    • Choose Pivot Table.

Inserting Pivot Table for Excel Hierarchy

    • In the dialog box, ensure that the Add this data to the Data Model option is checked and press OK.

Confirming Table or Range for Pivot Table

  • Configure the Pivot Table:
    • A new worksheet will be created.
    • Under PivotTable Fields, check the table attributes you want to include. For example, select Team and Executives as rows, and Revenue as the Sum of Revenue.

Organizing Pivot Table for Excel Hierarchy

Read More: Create Hierarchy in Excel Pivot Table

Method 3 – Using Power Pivot

  • Create an Excel Table:
    • Select the range B4:D10 and press CTRL+T to turn it into an Excel table.

Inserting Table for Excel Hierarchy with Power Pivot

  • Add to Data Model:
    • Select the newly created table.
    • Click on Power Pivot (if available) and choose Add to Data Model.

Adding Table to Data Model

Note: If Power Pivot is not visible, manually add the Power Pivot tab to your Excel Ribbon.
  • Create a Hierarchy:
    • In the Power Pivot window, select the three columns under the table name.
    • Right-click and choose Create Hierarchy.

Creating Hierarchy with Power Pivot

  • Generate a Pivot Table:
    • Click on Home and select PivotTable.

Inserting Pivot Table with Power Pivot

    • In the new worksheet, check Hierarchy1 under Table1 and also select Revenue under More Fields.

Organizing Pivot Table with Power Pivot

Read More: Create Hierarchy in Excel

How to Customize SmartArt Hierarchy in Excel

Example 1 – Adding or Deleting Boxes

  • Adding Extra Boxes:
    • To add extra boxes to your existing SmartArt hierarchy:
      • Click on the box after which you want to add the new box(es).
      • Navigate to the SmartArt Design tab.
      • Click on Add Shape and choose Add Shape After.

Adding Hierarchy Box

      • By following this method, you can create additional hierarchy branches with attributes.

New Hierarchy Branch in Excel

  • Deleting Boxes:
    • To delete a box:
      • Select the box you want to remove.
      • Press the Delete key on your keyboard.
      • The box is gone.

Example 2 – Changing Colors or Hierarchy Styles

  • Changing Hierarchy Style:
    • To modify the current hierarchy style:
      • Select the entire hierarchy flowchart.
      • Go to the SmartArt Design tab.
      • From the SmartArt Styles group, choose a style that suits your preference.

Exploring SmartArt Styles

  • Customizing Colors:
    • To change colors for specific objects (boxes or flowchart lines):
      • Right-click on the object.
      • Select Format Shape.
      • Under Fill & Line, explore customization options such as color, transparency, and width.
      • Use these options to tweak the appearance of your hierarchy flowchart.

Changing Line Color of Excel Hierarchy

Different Features in Excel Power Pivot Hierarchy

  • Changing Hierarchy Order:
    • If you’ve created a hierarchy using Excel’s Power Pivot feature:
      • Right-click on the element you want to reposition.
      • Choose Move Up or Move Down to adjust the hierarchy order.

Changing Order of Excel Hierarchy

Hiding or Showing Source Column Names in Hierarchy

  • By default, source column names appear in brackets next to each column name.
    • To hide them:
      • Right-click on any column.
      • Select Hide Source Column Name.
      • Repeat for other columns.

Hiding Source Column

    • To show them again:
      • Right-click on any hidden column.
      • Select Show Source Column Name.

Showing Source Column

Drill Up and Drill Down in Hierarchy

Suppose you want to focus on specific attributes within a hierarchy Pivot Table:

  • To drill down:
    • Select the desired row.
    • Click on PivotTable Analyze >> Drill Down under the Active Field group.

Selecting Drill Down for Marketing Team

The Marketing Team is now drilled down.

Marketing Team Drilled Down

  • To drill up:
    • Select the first attribute in the drilled-down list.
    • Click on PivotTable Analyze >> Drill Up.

Selecting Drill Up for Marketing Team

The Marketing Team is now drilled up as before.

Marketing Team Drilled Up

Things to Remember

  • Arrange data logically within each level for easier navigation and analysis.
  • Update hierarchies as your data changes (additions, modifications, removals) to maintain accuracy.

Frequently Asked Questions

  • How do I add a hierarchy slicer in Excel?
  1. Select Your Data:
    • Highlight the range of cells that contains your data, including the headers.
  2. Insert a Slicer:
    • Go to the “Insert” tab in the Excel ribbon.
    • In the “Filters” group, click on the “Slicer” button.
    • In the “Insert Slicers” dialog box:
      • Select the column that represents the top-level of your hierarchy (e.g., department or category).
      • Click on the “OK” button.
      • A slicer box will be added to your worksheet, which you can resize and reposition as needed.
  3. Create the Hierarchy:
    • Right-click on the slicer.
    • Choose “Slicer Settings.”
    • In the “Slicer Settings” dialog box:
      • Click on the “Report Connections” button.
      • Check the boxes for the columns that represent the lower levels of your hierarchy (e.g., subcategories or regions).
      • Click on the “OK” button.
  4. Filter and Navigate:
    • Your hierarchy slicer is now ready!
    • Use it to filter and navigate through your data by selecting different levels of the hierarchy.

Creating a Hierarchy Tree (Treemap) in Excel:

  1. Select Your Data:
    • Highlight the relevant data range.
  2. Insert a Treemap Chart:
    • Go to the “Insert” tab.
    • Choose “Hierarchy Chart” and then select “Treemap.”
    • Alternatively, you can use Recommended Charts:
      • Go to “Insert” >> “Recommended Charts” >> “All Charts.”
      • Look for the treemap chart option.
  3. Customize the Treemap:
    • The treemap chart will display your hierarchy.
    • You can further customize it by adjusting labels, colors, and other formatting options.

Download Practice Book

You can download the practice workbook from here:

Hierarchy in Excel: Knowledge Hub

<< Go Back to SmartArt in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Nafis Soumik
Md. Nafis Soumik

Md. Nafis Soumik graduated from Bangladesh University of Engineering & Technology, Dhaka, with a BSc.Engg in Naval Architecture & Marine Engineering. In January 2023, he joined Softeko as an Excel and VBA content developer, contributing 50+ articles on topics including Data Analysis, Visualization, Pivot Tables, Power Query, and VBA. Soumik participated in 2 specialized training programs on VBA and Chart & Dashboard designing in Excel. During leisure, he enjoys music, travel, and science documentaries, reflecting a diverse range... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF