How to Create a Table with Subcategories in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

Create a Table with Subcategories in Excel: 2 Useful Ways

1. Using PivotTable to Create a Table with Subcategories in Excel

2. Applying Data Validation

How to Create Multi-Category Chart in Excel

Conclusion

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.

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.

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.

Download Practice Workbook

You can download the practice workbook here.

Create Table with Subcategories.xlsx

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. Also, if you want to see more Excel content like this, please visit our website Exceldemy and unlock a great resource for Excel-related content.

How to Create a Table with Subcategories in Excel

Create a Table with Subcategories in Excel: 2 Useful Ways

1. Using PivotTable to Create a Table with Subcategories in Excel

2. Applying Data Validation

How to Create Multi-Category Chart in Excel

Conclusion

Related Articles

Leave a reply Cancel reply