This guide will explain how to create a multi-level hierarchy in Excel using two easy and efficient methods.
Excel is a popular tool for presenting and organizing data in a visually pleasing and concise way. And one method we can perform in Excel to present our data is by creating a multi-level hierarchy.
A multi-level hierarchy presents data in an organized, concise, and simple way. Additionally, it can easily show the relationship between data and values. And Excel makes this entire process of creating a multi-level hierarchy quite easy.
We can utilize two simple and efficient methods to create a multi-level hierarchy in Excel. And the first method is through the use of an existing feature in Excel that is data validation. Furthermore, data validation is one of the simplest methods to create a multi-level hierarchy in Excel.
Secondly, we have the PivotTable feature in Excel as our other simple and efficient method. Essentially, a PivotTable in Excel is a powerful tool that summarizes and calculates the data set. And it will display the comparison, trends, or patterns in the data set.
So these two simple and efficient methods will help us create a multi-level hierarchy in Excel.
Let’s take an example wherein we can see the need for creating a multi-level hierarchy.
Suppose you have the sales report of the stores located in the different states and cities in the United States. And you want to present the data in an organized and concise way by creating a multi-level hierarchy.
Awesome! Now let’s dive into a real example of creating a multi-level hierarchy in Excel.
A Real Example of Creating Multi-Level Hierarchy in Excel
Let’s say we want to create a multi-level hierarchy in Excel with our data set containing the schedule of a movie showing. Additionally, we also have a data set containing the different states and cities located in the USA with their profit.
So there are two methods we can use to create a multi-level hierarchy in Excel. Firstly, we can utilize the data validation feature in Excel. Essentially, data validation restricts data entry in selected cells.
Secondly, we can also use the PivotTable to create a multi-level hierarchy in Excel. Furthermore, a PivotTable is a powerful feature in Excel that easily lets us summarize, analyze, and organize data.
You can make your own copy of the spreadsheet above using the link attached below.
Great! Now let’s move on and learn the steps of how to create a multi-level hierarchy in Excel using the two methods.
How to Create Multi-Level Hierarchy in Excel using Data Validation
In this section, we will discuss the step-by-step process of how to create a multi-level hierarchy in Excel using the Data Validation feature.
1. Firstly, we need to create a new table to input our hierarchy. Then, select cell A14 and go to the Data tab. Next, click on Data Validation.
2. Secondly, the Data Validation window will appear. In the Settings tab, select List from the dropdown menu of Allow.
3. Thirdly, input the formula “=$A$2:$A$4” in the Source text box. So this will input the list of movies listed in the given range. Lastly, click on OK to apply the data validation.
4. And this will apply a dropdown menu to cell A14 containing the list of movies showing in our data set. So select a movie to input into the cell.
5. Next, simply drag down A14 to the rest of the rows to apply the same data validation dropdown menu.
6. Then, we will do a similar process for the Schedule column. So select cell B14 and go to the Data tab. Next, click on Data Validation.
7. Similarly, select List. Then, input the formula “=$A$8:$A$10” which references the schedule of the Frozen movie. Lastly, click OK to apply.
8. And now we have a dropdown menu of the scheduled time for the movie Frozen. Afterward, perform the same steps in the rest of the movies.
9. And tada! We have created a multi-level hierarchy in Excel using the Data Validation feature.
How to Create Multi-Level Hierarchy in Excel using PivotTable
In this section, we will discuss the step-by-step process of how to use the PivotTable tool to make a multi-level hierarchy.
1. Firstly, select the entire table. Then, go to the Insert tab and select PivotTable.
2. Secondly, the PivotTable window will appear. Because we selected our data set in advance, the Table/Range already contains our data set. Next, select Existing Worksheet if you want the PivotTable in the same sheet.
If not, you can select New Worksheet for the PivotTable to appear in a different one. Then, input the specific cell you want to place the PivotTable. In this case, we will input A8 in the Location textbox. Lastly, click OK to apply.
3. Afterward, an empty PivotTable will appear. To display our data set, head to the PivotTable Fields. Then, check all the boxes of the data you want to display in the PivotTable.
Additionally, you can remove certain parts of the PivotTable in this window.
4. And tada! We have successfully created a multi-level hierarchy in Excel using the PivotTable tool.
And that’s pretty much it! We have discussed two simple and efficient methods of how to create a multi-level hierarchy in Excel. Firstly, you learned the Data Validation method. And you also learned how to create it using a PivotTable.
Now you can create a hierarchy table whenever you need them using these two simple methods.
Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
