Contents
Data structuring is a necessary condition for working with spreadsheets if a person wants to process large amounts of information efficiently. One way to do this is to use grouping. It not only allows you to organize a large amount of information, but also hide those elements that are not needed at the moment. As a result, a person can fully focus only on significant fragments, without being distracted by what is not so important at the moment. What steps need to be taken to group information in Excel?
How to set up grouping
Setting up is a very important preparatory stage. Without it, it is impossible to make the final result suit a person. To properly set up grouping, follow these instructions exactly:
- Expand the “Data” tab in the document in which you want to group information.
- Open the “Structure” section, in which we expand the set of tools.
- A window will pop up in which you need to set the grouping settings. The preset parameters are as follows: results for columns are written directly to the right of the columns, and for rows – at the bottom. Often people don’t like this idea because they think putting the name on top saves a lot of time. To do this, you need to uncheck the checkbox, which is located directly next to the corresponding parameter. In general, all users can customize the grouping in such a way that it suits them. But you can also make the style generation automatic by enabling the corresponding item.
- We confirm our actions after the necessary changes are made to the parameters by pressing the OK button.
At this preparatory stage can be considered completed.
Grouping by Rows in Excel
Let’s group the information by rows.
First, we need to add an extra row above or below the column group. It all depends on where we want to place the name and conclusions. After that, we move to a new cell in which we assign a name to the group. It is recommended to choose the name that will be most suitable for the context.
Next, the rows that need to be combined, select and open the “Data” tab. In this case, the total row is not included in the range that should be grouped.
Now let’s go to the “Structure” section. To do this, click on the corresponding button located on the right side of the ribbon. Click on the “Group” button. There will appear a special menu in which we press this button.
After that, the program will offer a grouping method – by rows or columns. You need to choose the one that is most suitable for a particular situation. That is, in our case, line by line. We confirm our actions by pressing the “OK” button. After that, the process of creating a group can be considered complete. You can collapse it with the minus sign.
After the group hides, the minus sign will be replaced with a plus. Clicking on it will expand the group.
Grouping by columns in Excel
Grouping by columns is carried out in the same way as by rows, only the type of grouping must be selected in the corresponding dialog box. But still, let’s go in order.
- We decide what data we need to group. After that, we insert a new column in which the group header will be written.
- Next, select the cells to be grouped. Similar to the previous example, do not highlight the title.
- Go to “Data” and there we find the item “Structure”. After clicking on this button, a menu will open, in which we select “Group”.
- In the dialog box that appears, select “Columns”. We confirm our actions.
This completes the grouping of data. In the same way as in the previous case, you can collapse and expand the elements of the group using the minus or plus key, respectively.
Create nested groups
Grouping does not end at the menu items described above. The user may well create nested groups that allow you to more clearly structure information. The logic of actions, in general, is the same. Only all the steps described above must be performed within the framework of the parent group. The sequence of actions is as follows:
- If the parent group has been collapsed, it must be expanded.
- Those cells that need to be assigned to a subgroup must be selected.
- After that, open the “Data” tab, go to the “Structure” section and click “Group” there.
- Then we choose the grouping method – by rows or by columns, after which we confirm our actions.
After that, we will have an additional nested group. How many subgroups can a user create? The quantity is unlimited. We can see that numbers have appeared above the minus icons. They allow you to quickly switch between different levels of nesting.
If the grouping is carried out by rows, not columns, not rows, then these numbers will be located to the left of the sheet (or minus / plus signs).
Ungrouping in Excel
Suppose we no longer need a group. What to do in such a situation? Is it possible to remove it? Oh sure. This operation is called ungrouping. First, select all the elements of the group that you want to disband. After that, go to the “Data” tab and look for the “Structure” section there. After we click on the appropriate button, and in this menu, the “Ungroup” button will appear. We click on it.
After that, a dialog box will appear in which there will be a similar set of settings to the one that was when we wanted to group the cells. Only this time, Excel will ask what exactly we need to ungroup: columns or rows. Select what you need and press the “OK” button. After that, the sheet will look the same as before the corresponding group was made.
Multilevel grouping – step by step instructions
Let’s now systematize the information obtained above a little and give ready-made step-by-step instructions on how to carry out multi-level grouping of data:
- First, we carry out the configuration corresponding to the first section of this article.
- After that, we create a group header. To do this, we make an additional row or column, depending on what data we want to group.
- Assign the name of the group in this column or line.
- Select the cells that you want to combine into one group.
- Go to the “Data” tab, and there we find the “Structure” group of tools.
- Then click on the “Group” button, select the type of grouping that is needed in a particular situation and click on the OK button.
- After that, we determine how the second group should be called and create an additional line with this data.
- Select the cells of the subgroup to be selected. The title, as in the previous example, is not included in the group.
- Repeat steps 5 and 6 for the second level of the group.
- Repeat steps 7-9 for each next level of the group.
As you can see, this instruction is quite simple to understand, and fits in 10 points. Once you feel it intuitively, there will be no problem immediately in order to perform all these actions in practice. Now let’s look at some important aspects of data grouping.
Automatic structuring in Excel
The entire method described above is an example of manual grouping of spreadsheet data. But there is a way to automate the division of information into groups. True, the program itself will create a sample for grouping. But in many situations, Excel structures the information correctly.
The biggest advantage of automatic data structuring in Excel is time saving. Although you should always be prepared for the fact that you will have to make some changes yourself. Unfortunately, robots have not yet learned to read minds.
When is the best time to use automatic grouping? It works best when the user is working with formulas. In this case, errors usually do not occur.
What should be done in order to group the data automatically? You need to open the “Group” section and select the “Create structure” item there. Excel will do the rest for the user.
Cancellation of automatic grouping is carried out in a slightly different way. You need to select the “Ungroup” item, but click on the “Clear structure” menu. After that, the table will return to its original form.
How to sort table data
By sorting data, you can be especially masterful in sorting the information that is contained in a spreadsheet. It can be carried out according to a whole set of criteria. Let’s describe the most key points that you need to pay attention to.
color division
One way to sort data is to color information that belongs to the same group with a special color. To do this, open the “Data” tab and find the “Sort and Filter” section there. Further actions depend on the version of the office suite you are using. The function we need can be called either “Custom” or “Sort”. After we make a click on the corresponding item, we should have a pop-up window.
Then go to the “Column” section. There we find the “Sort by” group, and then select the column that is needed in our situation. Next, we set the criterion by which the data should be sorted. For example, they can be the color of the cells. If we plan to use it, we must first color the appropriate cells with the appropriate color.
To determine the color, you need to click on the arrow image in the “Order” section. Nearby there will be an item with which you can determine where the data that the program sorted automatically will be sent. If you need them to be placed on top, you must click the appropriate button. If you want the shift to be carried out in the direction along the line, you must select the “Left” item.
The same actions can be performed with other criteria by creating another level. To do this, find the “Add Level” item in the dialog box. Next, we save the document, after which we can merge the data again by clicking on the “Apply again” button.
Combining Values
Excel gives you the ability to group values based on the value contained in a particular cell. This is necessary, for example, when you need to search for fields that contain certain names or data of a different type (for example, codes). How to do it? The first two steps are the same as in the previous section, but instead of a color criterion, you need to set the value by which the grouping will be performed.
We are interested in the “Custom list” item in the “Order” group, by clicking on which, the user can either make his own list of conditions for sorting, or use the pre-installed one in Excel. So you can combine data by any criterion, including date, days of the week, and so on.
How to simplify a large Excel spreadsheet
Excel makes it possible to carry out several types of grouping in Excel at once, based on different criteria. To do this, you need to follow these instructions:
- Each range must have a heading at the beginning and must not contain empty cells. Headers must include the same type of data.
- It is recommended to sort the data in alphabetical order first.
- Let’s use the “Subtotals” command, which is located in the same section – “Group”.
After that, we group the columns in the same way as described earlier. The result should be a multi-level table.