We often need multi-dimensional tables to present our data. For this reason, we need to create sub-categories to our data table. Excel offers some great features to make multi-level tables. In this article, we will show 2 ways how to create a table with subcategories in Excel.
Create a Table with Subcategories in Excel: 2 Useful Ways
In this article, we will talk about 2 handy ways of how to create a table with subcategories in Excel. Firstly, we will use the PivotTable to do the task. Then, we will use the Data Validation feature to create a table with subcategories.
1. Using PivotTable to Create a Table with Subcategories in Excel
The PivotTable is a very powerful tool to present data. It allows users to categorize data according to their needs. In this method, we will manipulate our data using the PivotTable to create subcategories.
Steps:
- Firstly, select any data cell on the dataset.
- Secondly, go to the Insert tab.
- Thirdly, click on the PivotTable tab.
- Finally, from the drop-down list, select From Table/Range.
- As a result, a prompt will pop-up.
- In the prompt, select the data cell range as the Table/Range.
- Then, mark the New Worksheet oval.
- Finally, click OK.
- Consequently, a new sheet will be opened.
- After that, in the PivotTable Fields sidebar, drag the Countries field and place it under Rows.
- After that, drag the World Cup Titles field under Values.
- Afterwards, select some particular data and right-click.
- Next, right-click on any of these data and select Group.
- Consequently, those datasets will be grouped under a category as subcategory.
- Do the same for the rest of the data.
- After that, rename the row labels.
- Rename the group names for clarity.
- As a result, we will have a table with subcategories under some categories.
- You can collapse the subcategories into categories by clicking the minus sign beside the categories.
Read More: How to Make a Table in Excel with Lines
2. Applying Data Validation
In this method, we will create multiple dependent drop-down lists. This will allow us to create subcategories to some particular categories. We will use the Data Validation feature in this case.
Steps:
- To start with, select the cell range B5:B7.
- Then, go to the Data tab.
- From there, select the Data Tools group.
- Finally, click on the Data Validation command.
- As a result, a prompt will be on the screen.
- In the prompt, under the Allow option, select List.
- Then, choose the E4:G4 range as Source.
- Finally, click OK.
- Select the C5 cell.
- Then again, go to the Data tab.
- From the Data Tools group, choose Data Validation.
- In the Data Validation prompt, again choose List under the Allow option.
- Then, write the following formula in the Source box,
=OFFSET($E$4,1,MATCH($B5,$E$4:$G$4,0)-1,COUNTA(OFFSET($E$4,1,MATCH($B5,$E$4:$G$4,0)-1,4,1)))- Finally, click OK.
- As a result, we will have the subcategories to the Laptop category in the C5 cell in a drop-down list.
- After that, select the C5 cell and press Ctrl + C.
- Then, select the C6 and C7 cell as well and right-click.
- From the prevailing option, choose Paste Special.
- As a result, a prompt will appear on the screen.
- From the prompt, choose Validation under the Paste option.
- Then, click on OK.
- As a result, we will find that the C6 and C7 cells too showing the subcategories to different categories.
Read More: Create Table in Excel Using Shortcut
How to Create Multi-Category Chart in Excel
In this instance, we will create a chart with multiple categories. This chart will allow us to show different subcategories under those categories eloquently.
Steps:
- Firstly, select the dataset.
- Secondly, go to the Insert tab.
- Thirdly, choose the Insert Column or Bar Chart command.
- Finally, from the drop-down list, choose the Clustered Column chart.
- As a result, we will have a graph with multiple categories.
Read More: How to Add New Row Automatically in an Excel Table
Download Practice Workbook
You can download the practice workbook here.
Conclusion
In this article, we have discussed 2 ways of how to create a table with subcategories in Excel. These will allow users to present their dataset more eloquently and effectively. If you have any questions regarding this essay, feel free to let us know in the comments.
Related Articles
- Create a Table in Excel Based on Cell Value
- How to Create a Table with Existing Data in Excel
- How to Create a Table Without Data in Excel
- How to Create a Table with Merged Cells in Excel
- How to Create a Table in Excel with Multiple Columns
<< Go Back to Make a Table | Excel Table | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
