Excel is one of the applications in the Office suite that is equally useful in the workplace as well as at home or home office. Excel is capable of storing large amounts of information; sometimes this information becomes too cumbersome, so using the data stored in the Excel worksheet becomes more and more difficult over time as the file grows.
More commonly used in a commercial setting, home users are often unaware that you can group and collapse both rows and columns in an Excel sheet to hide information until you need it. This is especially useful when you are using formulas in a spreadsheet to summarize information and most of the time you are only interested in those totals.
Combine rows and columns in an Excel worksheet
Let’s say you have an Excel worksheet similar to the one below. Note that there are a number of cells containing data and that each dataset is summarized in a separate cell (B6, B13, and B20).
The problem is that the data in cells (B1 to B5, B8 to B12, and B15 to B19) is not used on a regular basis; you are only interested in the total, average and maximum values ??for each dataset respectively.
Using Excel’s grouping function, you can group this data individually and hide it from view. Whenever you need to view or edit data, you can expand the groups and work with them again.
– /
As an example, let’s group rows 8 through 12 together, collapse them and leave only the average in row 13 visible. Start by selecting rows 8 through 12 with the mouse. Click the Data tab on the ribbon and locate the section of the ribbon that says Outline. Click the button labeled “Group” and choose “Group” from the menu.
You will immediately notice a change in your Excel spreadsheet that you may have never seen before. Next to lines 8 through 12, there is a line connecting these lines on the left, and a minus sign next to line 13. This means that cells 8 through 12 are part of a group that is currently expanded.
If you click the minus sign next to line 13, lines 8 through 12 collapse, and the minus sign becomes a plus sign. This also means that lines 8 through 12 are part of the group and that the group is currently collapsed.
Clicking on the plus sign will expand the group again. Also notice that collapsed rows on the sheet go straight from line 7 to line 13, which is a sure sign that some of the rows on the sheet are grouped and are currently collapsed.
If we do the same for rows 1 through 5 and rows 15 through 19, we can see that when these rows are grouped and collapsed, the original data is hidden from view, making it easier to find important cells. Note that the three plus signs in the left binding of the worksheet indicate that there are currently three collapsed row groups.
Grouping and collapsing cells in Excel is not limited to rows; you can also group and collapse columns. You can even create groups within groups to better organize data that has become difficult to work with in a bloated Excel worksheet.
–