How to create a table with subcategories in WPS Office Excel

Today we'll learn to create a table with subcategories, which is equal to multilevel drop-down lists, to improve the efficiency and accuracy of data entry.

Take this table as an example.The subcategory of student changes with the class we select.

Without further ado, let’s try to make such subcategories step by step. We have prepared the list of students' names.

Note that no spaces are allowed in any cells to reference; otherwise, it returns errors.

· Step 1: To create subcategories, we need to Create Names first.

1. Select the cell range and press the shortcut key Ctrl+G on the keyboard to deselect the empty cells.

2. In the pop-up dialog, check Constants and click Go To.

3. Then we will set theClass1, Class2, Class3 on the top row as the Title of their students.

4. Click the Formulas tab, click Create, and select Toprow in the pop-up dialog.

5. Then we can click Name Manager to check the names for the selected cell range.

· Step 2: Insert level 1 drop-down lists before creating subcategories.

1. Select theClasscolumn, click the Data tab, click Validation, select List in the Allow area, select the cell range of D1:F1 in the Source area, and click OK.

2. Now we can select the classes freely.

· Step 3: Insert the level 2 drop-down list as a subcategory.

Before making the level 2 drop-down list, we should select a class from the level 1 list to avoid leaving the cell empty.

1. Select the Student column, and do as the previous steps. Click Validation, select List, and enter the referencing function INDIRECT in the Source area.

2. Since the choices of the level 2 list correspond to level 1, we need to reference the options of level 1here by clicking the cell.

3. Note that the default reference area is absolute. We need to delete the “$” sign in the middle to change it to a cross-reference.  

(That means we need to input: =INDIRECT($A2) here.)

4. Click OK.

When we select a specific class, the students' names in the level 2 list will correspond to that class.

With these steps, you are able to make a subcategory in a table quickly and freely. The same goes for creating more subcategories in a table, and you can try to make it by yourself!

· Terms Explanation about WPS Spreadsheet

What is WPS Spreadsheet?

WPS Spreadsheet is a module of WPS Office(an acronym for Writer, Presentation and Spreadsheet). WPS Spreadsheet is consistent with Excel document, which helps users to work with high efficiency. Its personal basic version is free to use and it also provides abundant functions and unique features for you to explore.

Learn more advanced skills about subcategories in WPS Spreadsheet:

How can we create a drop-down list | WPS Academy Free Office Courses

Set input conditions and drop-down menus | WPS Academy Free Office Courses

WPS Office Spreadsheet not only covers all of the basic functions of Excel, but also enables advanced techniques for data cleaning and analyzing. WPS Office Academy teaches strategies for free.