In this article, we are going to learn about hierarchy in Excel. We shall use the SmartArt feature to create and customize a hierarchical structure such as an organizational chart.
We’ll also use the PivotTable and Power Pivot features of Excel to create and customize a structured arrangement of hierarchical data.
Excel Hierarchy finds application in representing organizational structures, product categorization, geographical hierarchies, financial analysis, project management, human resources, decision trees, and academic structures.
It facilitates clear visualization, effective reporting, and informed decision-making by representing complex relationships and enabling drill-down capabilities.
Download Practice Book
You can download the free Excel workbook here and practice on your own.
What Is Hierarchy?
The term “hierarchy” has two distinct meanings. The first, and simpler definition refers to a particular kind of chart that aids in visualizing a hierarchical structure, such as an organizational chart.
On the other hand, a hierarchy refers to a structured arrangement of data where items are organized into levels or categories based on their relationships.
In Excel, hierarchies are often used in conjunction with pivot tables and Power Pivot.
How to Create Hierarchy in Excel
Method 1: Using SmartArt Feature
First, select the raw data table for the hierarchy (B5:B11). Click on Insert >> Illustrations >> SmartArt.
A dialogue box will appear. From the left side, select Hierarchy. Choose a hierarchy style and press OK.
Click on the marked arrow sign. Place the cursor inside the hierarchy chart and press CTRL+A.
Press Delete. All the default text will disappear.
Then, copy range B5:B11 and paste them on the Organization Chart… Now, place managers below the CEO by selecting them individually and pressing Tab once for each. And as for the executives, you need to press Tab twice for each of them.
If your desired organogram is bigger and more detailed, follow the same procedure. That means you have to press Tab thrice, four times, or more if you want to add more levels below executives.
Lastly, you can choose one layout from the Layouts options and one style from SmartArt Styles, to give your hierarchy a customized look.
Method 2: Using Pivot Table
First, select the range B4:D10. Click on Insert >> Pivot Table.
You will see that a dialogue box will appear and Table/Range: field is filled with your previously selected range. Press OK.
A new worksheet will be created. Then, under PivotTable Fields, check the table attributes. Team and Executives are selected as rows and Revenue is placed under Values as the Sum of Revenue. Your Pivot Table hierarchy is now complete.
Read More: Create Hierarchy in Excel Pivot Table
Method 3: Using Power Pivot
You can also create a hierarchy using the Power Pivot feature in Excel. First, select the range B4:D10 and press CTRL+T. Your dataset is now turned into an Excel table.
Again select the newly created table and click on Power Pivot >> Add to Data Model.
A new window named Power Pivot for Excel will be opened. Select the three columns that appear inside the window under the table name and right-click on them. Select Create Hierarchy.
After that, select the table and click on Home >> PivotTable.
A new worksheet will be created containing a Pivot Table. Under PivotTable Fields, check Hierarchy1 under Table1 and check Revenue under More Fields. Your Power Pivot hierarchy is now complete.
Read More: Create Hierarchy in Excel
How to Customize SmartArt Hierarchy in Excel
Example 1: Add or Delete Boxes
Now, if you want to add extra boxes to your previously generated SmartArt hierarchy, first, click on the box after which you want to add the extra box(s) and click on SmartArt Design >> Add Shape >> Add Shape After.
Following this method, I have added a whole extra hierarchy branch with three extra attributes.
Deleting a box is simpler. Just select the box you want to delete and press Delete on the keyboard. The job is done.
Example 2: Change Colors or Hierarchy Styles
If you want to change the current hierarchy style, all you need to do is to select the hierarchy flowchart, then click on SmartArt Design, and from the SmartArt Styles group select a style of your preference.
In case of changing colors, select an object (box or flowchart lines) and right-click on it. From the options list, select Format Shape.
Under Fill & Line, there is a number of customization options for that particular object such as Color, Transparency, Width, etc. You can use them to change the color or appearance of your hierarchy flowchart.
Different Features in Excel Power Pivot Hierarchy
Changing Hierarchy Order
Suppose, you have created a hierarchy using the Excel Power Pivot feature. Now, if you want to change the order of that hierarchy, simply right-click on the element that you want to change the position of and select Move Up or Move Down.
The order of the hierarchy will be adjusted according to your need.
Hiding or Showing Source Column Name in Hierarchy
The source column name is by default visible inside a bracket beside every column name. But if you want to hide them, simply right-click on any of the columns and select Hide Source Column Name.
Repeat the process for the rest of the columns.
Now, if you want again to show the hidden source column names, just right-click on any of them and select Show Source Column Name.
Repeat this process for the rest of the column names and the source column names will be visible again.
Drill Up and Drill Down in Hierarchy
Suppose, after creating a hierarchy Pivot Table using Power Pivot, you want to show only the attributes inside a specific row and hide all the other rows and attributes.
You just need to select that particular row and click on PivotTable Analyze >> Drill Down under Active Field group.
The Marketing Team is now drilled down.
Again if you want to drill up, select the first attribute of the drilled-down list and click on PivotTable Analyze >> Drill Up under Active Field group.
The Marketing Team is now drilled up as before.
Things to Remember
- Arrange your data in a logical order within each level. Sorting the data within each level can make it easier to navigate and analyze.
- Hierarchies may require updates as your data changes. If new items are added or existing ones are modified or removed, ensure that your hierarchy accurately reflects these changes to maintain data integrity.
Frequently Asked Questions
- How do I add a hierarchy slicer in Excel?
To add a hierarchy slicer in Excel, follow these steps:
- Select the range of cells that contains your data, including the headers.
- 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.
- Click on the “OK” button.
- A slicer box will be added to your worksheet. You can resize and reposition it as needed.
- To create the hierarchy, right-click on the slicer and 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.
- Click on the “OK” button.
- Your hierarchy slicer is now ready. You can use it to filter and navigate through your data by selecting different levels of the hierarchy.
- How do you create a hierarchy tree in Excel?
Select your data. Go to the Insert tab >> Insert Hierarchy Chart >> Treemap. You can also use Recommended Charts to create a treemap chart by going to Insert >> Recommended Charts >> All Charts.
Conclusion
By going through this article, I hope you can now effectively manage and analyze data using hierarchy in Excel, ensuring accurate insights and meaningful representations of your data structure.
To further expand your knowledge and expertise in Excel, I encourage you to visit Exceldemy, a valuable resource for in-depth information on various uses of Microsoft Excel. There, you can explore a wide range of tutorials, tips, and tricks that will empower you to leverage the full potential of this powerful spreadsheet software.
Hierarchy in Excel: Knowledge Hub
- How to Create Multi Level Hierarchy in Excel
- Make Hierarchy Chart in Excel
- Create Date Hierarchy in Excel Pivot Table
- Create a Hierarchy of the State City and Zip Code
- How to Add Row Hierarchy in Excel
<< Go Back to SmartArt in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
