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, you can 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.
You can 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.
Luckily, you have the option to manually group data in Excel. In the workbook image below, there are no formulas or numeric totals, so you will need to group the data manually.
To use the subtotal feature, you first need to select a range of cells that you would like to work with. In the “Add subtotal to” section, you can select the columns you want totals for.
You will see that new subtotals for each region have been created, as well as a grand total that adds all the values together. In addition, Excel has automatically grouped this data together in a numbered outline.
Organizing the large data by combining the subcategory data is the process and it’s called Grouping of Rows in Excel. When the number of items in line is not important then we can choose group rows that are not important but see the subtotal of those rows only.
The number of rows is also lengthy when the worksheet contains detailed information or data. As a report reader of the data, they don’t want to see lengthy rows; instead, they just want to see the detailed view, but at the same time, if they require any other detailed information, they need just a button to expand or collapse the view as required.
In this article, we will show you how to group rows in Excel with expand/collapse to maximize the report viewing technique. In the above data table, we have city and state-related sales and cost data, but when you look at the first two rows of the data, we have “California” state and the city is “Los Angeles,” but sales happened at different dates, so as a report reader everybody prefers to read what the state-wise sales and city-wise sales in a single column, so by grouping the rows we can create a single line summary view.
Follow the below steps to group rows in Excel. Step 2: Now select the first state rows (California City), excluding subtotals.
Step 3: Go to the DATA tab and chose the Group option. Step 6: The moment you click on “Ok,” you can see a joint line on the left-hand side.
Now again, select the city Colorado and click on the Group option. Group by Using Shortcut Key With a simple shortcut in Excel, we can group selected rows or columns very easily.
The tutorial shows how to group rows in Excel to make complicated spreadsheets easier to read. See how you can quickly hide rows within a certain group or collapse the entire outline to a particular level.
Worksheets with a lot of complex and detailed information are difficult to read and analyze. Luckily, Microsoft Excel provides an easy way to organize data in groups allowing you to collapse and expand rows with similar content to create more compact and understandable views.
If your dataset contains just one level of information, the fastest way would be to let Excel group rows for you automatically. As shown in the screenshot below, the rows have been grouped perfectly and the outline bars representing different levels of data organization have been added to the left of column A.
If your summary rows are located above a group of detail rows, before creating an outline, go to the Data tab > Outline group, click the Outline dialog box launcher, and clear the Summary rows below detail checkbox. If your worksheet contains two or more levels of information, Excel's Auto Outline may not group your data correctly.
In such a case, you can group rows manually by performing the steps below. When creating an outline manually, make sure your dataset does not contain any hidden rows, otherwise your data may be grouped incorrectly.
Add more grouping levels if necessary In practice, datasets are seldom complete. If at some point more data is added to your worksheet, you will probably want to create more outline levels.
As an example, let's insert the Grand total row in our table, and then add the outermost outline level. One of the most useful features of Excel grouping is the ability to hide and show the detail rows for a particular group as well as to collapse or expand the entire outline to a certain level in a mouse click.
Another way to collapse rows in Excel is to select any cell in the group and click the Hide Detail button on the Data tab, in the Outline group : To minimize or expand all the groups at a particular level, click the corresponding outline number in the top left corner of your worksheet.
Level 1 displays the least amount of data while the highest number expands all the rows. Level 2 displays Grand total and Region subtotals (rows 9, 17 and 18).
Or click the plus sign for the collapsed group of rows that you want to expand: Go to the Data tab > Outline group, and click the Ungroup button.
Or press Shift + Alt + Left Arrow which is the Ungroup shortcut in Excel. In the Ungroup dialog box, select Rows and click OK.
Below you will find a few useful tricks that will make your work with groups even easier. In all the above examples, we have inserted our own subtotal rows with SUM formulas.
Microsoft Excel has the predefined styles for two levels of summary rows: However_1 (bold) and However_2 (italic). After you've collapsed irrelevant rows, you may want to copy the displayed relevant data somewhere else.
Head to the Home tab > Editing group, and click Find & Select > Go To Special. In the Go-To Special dialog box, select Visible cells only and click OK.
Scroll down to the Display options for this worksheet section, select the worksheet of interest, and make sure the Show outline symbols if an outline is applied box is selected. This is how you group rows in Excel to collapse or expand certain sections of your dataset.