Group and Ungroup in Excel
Many times, you will have to group or ungroup rows or columns in Excel so that you can have an organized view of data. For example, you store the sales details by month and you wish to group data of a specific month. Here, grouping can be extremely useful to organize your data and bring in more clarity. Grouping and ungrouping becomes more relevant in case of Excel spreadsheets with a ton of data.
Let’s explain the concept with the help of data given below.
Here, we have sales details of a company for the months of January, February and March for the year 2012. Now, you want to see the details for the month January alone. So, select cells from A2 to A4 which contains data for January and go to Data -> Outline -> Group. You will get a window that asks whether to group rows or columns. As you have to group rows here, click Rows and then click OK.
You will find a new margin with a number of dots next to each row as shown below.
If you click on the minus sign inside the box, the group will get collapsed and a plus sign appears. Similarly, if you click the plus sign, then the group will be expanded. You can see a 1 and 2 at the margin which indicates that a single level grouping exists. If you add one more level of grouping, then the margin would display 1, 2 and 3. Excel allows you to add up to 8 levels of grouping.
To ungroup data, select rows or columns that are already grouped. Go to Data -> Outline -> Ungroup and select Rows from the new window. Your data would be ungrouped. In the Outline group in Data ribbon, you can find a + sign in green color and a – sign in red color. Instead of pressing the plus and minus on the margin, you can also use the + and – in Data ribbon to show and hide details.
Subtotal is a useful function to calculate subtotals for a group of data. In our example, we have calculated the total sales happened during 2012 using SUM formula and we get the value 3806 in the cell C13. Suppose you wish to calculate the total sales for January, February and March separately. Instead of using Sum formula three times, we can make use of Subtotal function. The data should be sorted before calculating subtotal. Here, we have the data already sorted with respect to months. Otherwise, you need to use Sort function under Data -> Sort & Filter to sort the data. Now, we can directly use the Subtotal function. Select all cells including labels (A1:E11) and go to Data -> Outline -> Subtotal. We have to calculate subtotal of amount for each month. So, select month from “At each change in” dropdown, sum from “Use function” drop down and check “Sales” from “Add subtotal to”. After entering values, your Subtotal window should be as follows.
Click OK and your spreadsheet will look as given below.
From this, you get the subtotal for months January, February and March as 1414, 1145 and 1247 respectively and the Grand Total as 3806.
Read More about Excel here