Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create a Table with Subcategories in Excel

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.


Download Practice Workbook

You can download the practice workbook here.


2 Ways to Create a Table with Subcategories in Excel

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.

inserting pivot table to show how to create a table with subcategories in excel

  • 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.

dragging fields into rows to show how to create a table with subcategories in excel

  • After that, drag the World Cup Titles field under Values.

  • Afterward, select some particular data and right-click.
  • Next, right-click on any of these data and select Group.

grouping data to show how to create a table with subcategories in excel

  • 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.

renaming row labels to show how to create a table with subcategories in excel

  • Rename the group names for clarity.

renaming groups to show how to create a table with subcategories in excel

  • 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.

collapsing subcategories to show how to create a table with subcategories in excel

Read More: Excel VBA to Create Table from Range (6 Examples)


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.

applying data validation command to show how to create a table with subcategories in excel

  • In the prompt, under the Allow option, select List.
  • Then, choose the E4:G4 range as Source.
  • Finally, click OK.

inserting list and source to show how to create a table with subcategories in excel

  • 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.

using formula to show how to create a table with subcategories in excel

  • 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.

applying paste special to show how to create a table with subcategories in excel

  • 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: How to Create a Table with Existing Data in Excel


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.


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.Com and unlock a great resource for Excel-related content.


Related Articles

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo