Grouping and Ungrouping option

Outlining and grouping, which allow you to make large quantities of data minimize so that complex spreadsheets are easier to navigate. Outlining and grouping rows or columns in Excel makes it easy to organize and declutter spreadsheets that have a ton of data on display. Say you have several rows with the sales figures by month and the last row is a summation of the data, rather than display all months you can group the data (months) and only display the row with the totals. This can be extremely useful when you are working with a spreadsheet that has multiple years of data, think of it, if you had 4 years of data by month – that would amount to 54 rows of months and 1467 with totals rows. Instead of dizzying someone with all that data you can group it and display the totals per year and show off your skills with Excel.


Grouping rows or columns

I will show you the two methods that can be used to add comments to cells in Excel, one involves the right-click menu and the other the Office Ribbon menu.

1. Select a set of rows or columns that you wish to group together.
2. Click on the Data tab located in the Ribbon
3. Click on the Group button located in the Outline Group

select group button

You will notice that Excel created a new margin with a set of dots next to each row, you will also notice that it created a small box with a minus sign indicating that the group is expanded. Click on the minus sign (-) to collapse the group. When you clicked on the minus sign (), it collapsed the group and changed it to a plus sign (+).

Notice how the margin has a 1 and 2. This indicates that there is a single level grouping. If you create additional groups within the one we just created, the margin would show a 1, 2, and 3. This helps in knowing the levels of grouping in a particular sheet. You can have up to 8 levels of grouping.

By default, grouping sign goes to the bottom of selected cells, which create a wrong perception sometimes. To avoid this, you should deselect summary rows below details from the bottom right corner of outline group.

summry button

popup window

So, our grouping file is now looks like:

final grouping


Ungrouping rows or columns

1. Make sure you select the groups that you wish to ungroup, do this by selecting the rows or columns that are grouped.
2. Click on the Data tab located in the Ribbon.
3. Click on the Ungroup button located in the Outline Group

You can Expand a group by clicking on the Show Detail button located in the Outline Group. You can Collapse a group by clicking on the Hide Detail button located in the Outline Group.

Download Documents

Download Excel File

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



clearPost Comment