To easily expand and contract sections of a worksheet To minimize schedules or side calculations that other users might not need To keep information organized As a substitute for creating new sheets (tabs) As a superior alternative to hiding cells Suppose we have a schedule in a worksheet that is becoming quite long, and we want to reduce the amount of detail that’s shown.
Once you ’re finished, toucan press the “-” buttons in the margin to collapse the rows or columns. The reason is that Excel does not make it clear to the user of the spreadsheet that cells have been hidden, and thus they may go unnoticed.
Toucan download the Template for free if you wish to use it as an example or starting point for how to group in Excel and apply it to your own work and financial analysis. CFI is the official global provider of the Financial Modeling and Valuation Analyst (FMA)™ FMA CertificationJoin 350,600+ students who work for companies like Amazon, J.P. Morgan, and Ferrari designation, a leading financial analyst certification program.
To continue learning and advancing your career, these additional CFI resources will be helpful: Keyboard shortcuts speed up your modeling skills and save time.
Grouping in Excel is used when we have properly structured data and with the header’s names mentioned in the column. There, grouping allows users to club rows or columns of any number together so that we can hide or in proper words subset the data under the selected columns and rows.
Group : Press Shift + Alt + Right Arrow shortcut, rather than going on the data tab then clicking the group button and then selecting row or column option. Ungroup: Press Shift + Alt + Left Arrow shortcut, rather than going on the data tab then clicking the ungroup button and then selecting the row or column option.
Summary of Example 1: As the user selects the row for the month of March, it is grouped into one. Now the user can hide or show the group using the button that is attached to the bracket created on the left side.
Step 2: As the user has selected row for the month of March and the region is East. Step 4: Now select the April month data for the same region.
Step 6: Similarly select the north region and create a group. Step 7: Now select the entire east region row and the 5th group will be created.
Step 8 : Now select the entire north region row and the 6th group will be created. Summary of Example 2 : As the user selects a row for each month, it is grouped into one.
Now the user can hide or show the group using the button that attached to the bracket on the left side. Summary of Example 3: The Q1, Q2 and Q3 sheets selected by the user will be grouped.
Now a user can perform an edit on multiple sheets at the same time. Let’s take an example where a user wants to update the data for August in the East region from Q3 sheets to $1820.
The user needs to make sure whenever they are going to use a group there should be column header, a summary row or subtotal if it is not there then we can create it with the help of the subtotal button which is available in the same toolbar of group button. Always make sure whichever column a user wants to make a group has some label in the first row itself and there should be no blank row or column in Excel.
Once the user ungroups or groups he can reverse by Undo button (Ctr+Z). It is not possible to ungroup other adjacent groups of columns /rows at the same time; the user needs to do it separately.
For this, I have created on simple example data in a worksheet. Instead of seeing all the country names we can club all the countries into one and make the data very precise or else we can go one step ahead and group products as well and show very little data.
All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings)35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion Step 2: After you click on that arrow you will see below dialogue box.
For example, if you want to group the country CANADA then selects the full range. Toucan also type the Excel shortcut key SHIFT + ALT + RIGHT ARROW (hold Shift & Alt key together and press Right Arrow).
Step 4: Here we need to select on what basis we are adding the subtotals. Under SUBTOTAL, we can add a variety of functions like SUM, AVERAGE, MIN, MAX in Excel, and many other things.
Clear Outline will remove all the grouping from the worksheet. SHIFT + ALT + RIGHT ARROW is the shortcut key to group selected cells.
This has been a guide to Group in Excel (Auto, Manual). When you ’re analyzing a huge amount of data, it’s always convenient to use programs like Excel to help you out.
By putting your data into an Excel worksheet, toucan organize and process all that information as you see fit. This can help you focus on the important figures that are relevant to your analysis.
When you hide rows or columns and you want to print the sheet you ’re working on, hidden items will not appear on the printout. Or, you may have locked your sheet for editing but forgot to inside some rows.
Select the “Data” tab in the Excel menu. In the “Outline” section, click on a small arrow beneath the Group icon.
In the “Outline” section, click on the arrow icon in the lower right corner. Uncheck the box for “Summary rows below detail”.
Notice the change in the grouping levels to the left of column A. Depending on how your data is structured, it may not follow the guidelines presented at the beginning of the previous section.
For example, it may contain blank rows or columns as a design feature. That could prevent the auto-grouping option to properly do its magic on the entire sheet.
Youcangroup just one portion of your sheet and leave the rest ungrouped. Whatever the reason, just make sure that you don’t have any hidden rows or columns before you proceed.
You can also click on the black arrow and select Group …”. Looking at the example used in this article, let’s suppose you only want to see how many items are available in Vancouver and Virginia separately.
Then group them manually, as explained in the previous section. Now, when you collapse these groups, you will see the sum totals for each of the cities, without having to look at the breakdown figures.
To see how many items Vancouver’s small storage holds, select rows 2 to 3 and group them. If you have any tips on this matter, or any other option in Excel, please share in the comments below.