Grouping of rows and columns

When we work with large tables containing the subtotals according to categories (article classification, sales program, type of partner, etc.), we can perform a grouping by to one of the given criteria. Grouping can be done by rows or columns, and the goal is to make the report more transparent. That is, if necessary, we can display or hide the analytical elements of the report in order for the data to take up less space.

Imagine that we have a report in which we track sales of articles classified into several categories. After we finish the report, we will have sums of sales by category, as well as the sum of all categories. Although the report we made concise, it might be a little more transparent. We achieve this by grouping rows by categories.

Grouping is done by first selecting the rows or columns we want to group, and then in the Data ribbon, within the group of Outline options, open the menu menu where we select the option with the same name. We can repeat the procedure several times, it is only necessary to re-select the rows or columns we want to Group each time.

After grouping rows, a menu that resembles a tree with icons marked “+” or “-” appears on the left side of the table. The “+” sign indicates that in the rows to the right of the icon there is some detail. Clicking on “+” expands the tree, or reveals hidden rows and we can see the detail of the report. When a detail has been detected, instead of the icon with the “+” sign, a “-” icon appears. By clicking on “-” we collapse the tree, or display less details. In addition to these icons, at the top of the menu are icons with numbers. They indicate the levels of the hierarchy of reports, and there are as many levels as we have done by grouping. Clicking on the appropriate icon is displaying a certain level, or collecting or spreading a report table.

We were able to do the grouping automatically. This is done by first selecting the rows and columns that are grouped, then selecting the Group menu and the Outline option in the Data ribbon.

Removing a grouping is done by selecting the rows or columns that are grouped, and further in the Data ribbon clicking on Ungroup/Ungroup. Excel will ask us whether we want to ungroup rows or columns and, according to the answer, we will do so. By selecting the Clear Outline option, all groupings of rows or columns are removed.