Method 1 – Using a Pivot Table to Create a Table with Subcategories in Excel
Steps:
- Select a cell in the dataset.
- Go to the Insert tab.
- Click Pivot Table.
- Select From Table/Range.
- Enter the cell range in Table/Range.
- Select New Worksheet oval.
- Click OK.
- In Pivot Table Fields, drag Countries to Rows.
- Drag World Cup Titles to Values.
- Select data and right-click.
- Select Group.
The datasets will be grouped by subcategory.
- Follow the same for the rest of the data.
- Rename the row labels.
- Rename the group names.
The table displays categories and subcategories.
- You can collapse the subcategories by clicking the minus sign.
Read More: How to Make a Table in Excel with Lines
Method 2 – Applying Data Validation
Steps:
- Select B5:B7.
- Go to the Data tab.
- Select Data Tools and choose Data Validation.
- In Allow, select List.
- Choose E4:G4 as Source.
- Click OK.
- Select C5.
- Go to the Data tab.
- In Data Tools, choose Data Validation.
- Choose List in Allow.
- Enter the following formula in Source.
=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)))- Click OK.
Subcategories will be displayed in C5, in a drop-down list.
- Select C5 and press Ctrl + C.
- Select C6 and C7 and right-click.
- Choose Paste Special.
- Choose Validation in Paste.
- Click OK.
C6 and C7 also display subcategories.
Read More: Create Table in Excel Using Shortcut
How to Create a Multi-Category Chart in Excel
Steps:
- Select the dataset.
- Go to the Insert tab.
- Choose Column or Bar Chart.
- Choose Clustered Column chart.
A graph is displayed with multiple categories.
Read More: How to Add New Row Automatically in an Excel Table
Download Practice Workbook
Download the practice workbook here.
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 Excel Table | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
