Grouping data by name is great for complex data analysis, summarization, and visualization. The most common way is to create groups using Excel’s built-in Group feature and then assign a name to each group using the Define Name feature.
Sometimes you also want to assign group names in a separate column for similar data. In this case, you can use Excel’s IF or IFS function to create a dynamic formula.
Steps to create separate groups and name each group:
➤ Select the range of cells you want to group and go to the Data tab >> Outline group >> Group drop-down >> Group.
➤ In the Group dialog box, select whether you want to group by rows or columns. Press Ok. Repeat the process for as many groups as you need.
➤ Now, choose the cells of the first group and click on the Formulas tab >> Defined Names group >> Defined Name drop-down >> Define Name.
➤ Insert a name in the Name field of the New Name dialog box and press Ok. The grouped cells are now named. Repeat the steps for each group.
Apart from this method, we’ll use the Name Manager feature, the Name Box, and Excel formulas to create groups and assign names to them. We’ll also cover how to assign a group name based on the values in cells.
Create Groups and Assign Names Using the Name Box
Our sample dataset has columns for Salesperson‘s names, Products, and Sales figures for January, February, and March.
The easiest way to group columns by name is to use the Name Box. Below are the details:
➤ Select the cells you want to group. If you want to group all the cells of specific rows or columns, you can click on the row number or column header, press and hold the Ctrl key, and select multiple columns.
➤ Now, use the keyboard shortcut Shift + Alt + → .
➤ Or, go to the Data tab and click on the Group drop-down from the Outline group. Select Group from the menu.
➤ Confirm whether you want to group Rows or Columns. Click Ok.
➤ Excel will now create a group with your selected rows or columns.
➤ Repeat the same steps to create multiple groups. We’ve created the following 4 groups (3 with rows and 1 with columns).
➤ Excel treats adjacent groups as one. To separate the groups, add an empty row after creating the first group and then create the second one. You can hide the empty rows later, but not delete them.
➤ To name a group, select the cells of one group and go to the Name Box right over the first row number. Click on it and enter a suitable name for the group without spaces. For better readability, you can use an underscore (Laptop_Sales) or enter a number in the middle (Laptop1Sales).
➤ Repeat the process for all other groups. From now on, when you highlight the cells of a group, Excel will display the assigned name in the Name Box. You can also use the name in formulas or other related commands.
Use the Define Name Feature to Group by Name
Another way to define specific names for a group of cells is to use the Define Name feature in the following way:
➤ Select cells to group and use the keyboard shortcut Shift + Alt + → or Data tab >> Group drop-down >> Group method to create groups of Rows or Columns.
➤ Now, highlight the cells of a group and go to the Formulas tab. From the Defined Names group, click on the Define Name drop-down and select Define Name.
➤ As the New Name window opens, select the Name box and type a name you prefer. Click Ok.
➤ Do the same for all the other groups. You can find the names in the Apply Names option in the Define Name drop-down menu.
Assigning Group Names with Name Manager
Similar to the previous method, you can use the Name Manager to assign a name or edit it whenever needed. Here’s how:
➤ Group your chosen rows or columns using the Group command or keyboard shortcut mentioned above.
➤ Select the cells of a group, go to the Formulas tab >> Defined Names group >> Name Manager.
➤ When the Name Manager dialog box appears, click on New.
➤ Insert a name for the group inside the New Name window. Press Ok to create a named group.
➤ Repeat the steps as needed. You can find all the group names in the Name Manager dialog box.
Using a Formula to Assign Group Names Based on Similar Values
In this method, we’ll assign group names to cells containing similar texts. The group names will be in a new column. You can later sort or filter your data to gather the rows with similar values. Let’s get to the steps:
➤ Here, we’ll create 3 different groups based on the Product column (Column B). To assign group codes (Code L, Code M, and Code D) for each common product (Laptop, Monitor, and Desktop), insert any of the following formulas in the first empty column:
=IF(B2="Laptop","Code L",IF(B2="Monitor","Code M","Code D"))
Or,
=IFS(B2="Laptop", "Code L", B2="Monitor", "Code M", TRUE, "Code D")
➤ In both formulas, B2 is the first cell of the column (without the header) containing the values based on which we want to assign the codes. The following arguments contain the product names and group names for the matching product. Change the cell reference, product names, and group names according to your data sets.
➤ Press Enter and Excel will assign the group names. Here’s the result for the IF function:
➤ When we use the IFS function, we get the following output:
➤ If you want, you can sort your dataset to group rows that are assigned the same names. For this, select your entire dataset (without the headers), go to the Data tab, and click on Sort from the Sort & Filter group.
➤ In the Sort box, click the Sort By drop-down and choose the new column containing the group names. In the Order drop-down, select A to Z or Z to A to sort in ascending or descending order. Press Ok.
➤ Here’s our sorted data:
Frequently Asked Questions
How do I ungroup in Excel?
If you’ve grouped rows or columns using Excel’s Group feature, select the grouped rows or columns. Go to the Data tab and click on the Group drop-down from the Outline group. Click Ungroup and choose Rows or Columns depending on your selection. As a shortcut, press Shift + Alt + ← to ungroup quickly.
How do I separate a group of names in Excel?
To separate a group of names that are combined in one cell (like John Doe, Jane Smith, Alex Lee), select the cells containing the grouped names. Go to the Data tab and click on the Text to Columns icon from the Data Tools group. In Step 1, choose Delimited and click Next. Select the separator (Comma, Space, etc.) in Step 2. Finally, click Finish, and each name will be placed in a separate column.
How to randomize names into groups in Excel?
Let’s say your names are in A2:A10, and you want to randomly divide them into 3 groups: Group A, Group B, and Group C. In an empty cell, enter:
=CHOOSE(RANDBETWEEN(1,3), "Group A", "Group B", "Group C")
Press Enter and drag the formula down to the remaining cells. Now each name in Column A is randomly assigned to one of the three groups. You can copy the results and paste them as Values to lock them. Otherwise, Excel will recalculate the groups every time you make a change in the worksheet.
Concluding Words
When you group cells, you can click on the + or – sign beside the row numbers to expand or collapse a group. To edit a group name, use the Name Box or go to the Name Manager option in the Formulas tab and edit any name or range you want. If you use a formula, use the Sort or Filter feature to rearrange data by group names or display a single group at a time.
