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.
- Grouping rows in Excel
- Group rows automatically (outline)
- Group rows manually (nested groups)
- How to collapse rows in Excel
- How to expand rows
- How to remove outline and ungroup rows
- Excel grouping tips
- Calculate group subtotals automatically
- Apply default Excel styles to summary rows
- Select and copy only visible rows
- Hide and show outline symbols
- Outline symbols don’t show up in Excel
Grouping rows in Excel
Grouping in Excel works best for structured worksheets that have column headings, no blank rows or columns, and a summary row (subtotal) for each subset of rows. With the data properly organized, use one of the following ways to group it.
How to group rows automatically (create an outline)
If your dataset contains just one level of information, the fastest way would be to let Excel group rows for you automatically. Here’s how:
- Select any cell in one of the rows you want to group.
- Go to the Data tab > Outline group, click the arrow under Group, and select Auto Outline.
That’s all there is to it! Here is an example of what kind of rows Excel can group:
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.
Note. 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. Once the outline is created, you can quickly hide or show details within a certain group by clicking the minus or plus sign for that group. You can also collapse or expand all rows to a particular level by clicking on the level buttons in the top-left corner of the worksheet. For more information, please see How to collapse rows in Excel.
How to group rows manually
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. Note. When creating an outline manually, make sure your dataset does not contain any hidden rows, otherwise your data may be grouped incorrectly.
1. Create outer groups (level 1)
Select one of the larger subsets of data, including all of the intermediate summary rows and their detail rows. In the dataset below, to group all data for row 9 (East Total), we select rows 2 through 8.
On the Data tab, in the Outline group, click the Group button, select Rows, and click OK.
This will add a bar on the left side of the worksheet that spans the selected rows:
In a similar manner, you create as many outer groups as necessary. In this example, we need one more outer group for the North region. For this, we select rows 10 to 16, and click Data tab > Group button > Rows. That set of rows is now grouped too:
Tip. To create a new group faster, press the Shift + Alt + Right Arrow shortcut instead of clicking the Group button on the ribbon.
2. Create nested groups (level 2)
To create a nested (or inner) group, select all detail rows above the related summary row, and click the Group button. For example, to create the Apples group within the East region, select rows 2 and 3, and hit Group. To make the Oranges group, select rows 5 through 7, and press the Group button again. Similarly, we create nested groups for the North regions, and get the following result:
3. 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. To have it done, select all the rows except for the Grand Total row (rows 2 through 17), and click Data tab > Group button > Rows. As shown in the screenshot below, our data is now grouped in 4 levels:
- Level 1: Grand total
- Level 2: Region totals
- Level 3: Item subtotals
- Level 4: Detail rows
Now that we have an outline of rows, let’s see how it makes our data easier to view.
How to collapse rows in Excel
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.
Collapse rows within a group
To collapse the rows in a particular group, just click the minus button at the bottom of that group’s bar. For example, this is how you can quickly hide all detail rows for the East region, including subtotals, and show only the East Total row:
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:
Either way, the group will be minimized to the summary row, and all of the detail rows will be hidden.
Collapse or expand the entire outline to a specific level
To minimize or expand all the groups at a particular level, click the corresponding outline number at the top left corner of your worksheet. Level 1 displays the least amount of data while the highest number expands all the rows. For example, if your outline has 3 levels, you click number 2 to hide the 3rd level (detail rows) while displaying the other two levels (summary rows). In our sample dataset, we have 4 outline levels, which work this way:
- Level 1 shows only Grand total (row 18 ) and hides all other rows.
- Level 2 displays Grand total and Region subtotals (rows 9, 17 and 18).
- Level 3 displays Grand total, Region and Item subtotals (rows 4, 8, 9, 18, 13, 16, 17 and 18).
- Level 4 shows all the rows.
The following screenshot demonstrates the outline collapsed to level 3.
How to expand rows in Excel
To expand the rows within a certain group, click any cell in the visible summary row, and then click the Show Detail button on the Data tab, in the Outline group:
Or click the plus sign for the collapsed group of rows that you want to expand:
How to remove outline in Excel
In case you want to remove all row groups at once, then clear the outline. If you want to remove just some of the row groups (e.g. nested groups), then ungroup the selected rows.
How to remove the entire outline
Go to the Data tab > Outline group, click the arrow under Ungroup, and then click Clear Outline.
- Removing outline in Excel does not delete any data.
- If you remove an outline with some collapsed rows, those rows might remain hidden after the outline is cleared. To display the rows, use any of the methods described in How to unhide rows in Excel.
- Once the outline is removed, you won’t be able to get it back by clicking the Undo button or pressing the Undo shortcut (Ctrl + Z). You will have to recreate the outline from scratch.
How to ungroup a certain group of rows
To remove grouping for certain rows without deleting the whole outline, do the following:
- Select the rows you want to ungroup.
- 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.
For example, here’s how you can ungroup two nested row groups (Apples Subtotal and Oranges Subtotal) while keeping the outer East Total group:
Note. It is not possible to ungroup non-adjacent groups of rows at a time. You will have to repeat the above steps for each group individually.
Excel grouping tips
As you have just seen, it’s pretty easy to group rows in Excel. Below you will find a few useful tricks that will make your work with groups even easier.
How to calculate group subtotals automatically
In all of the above examples, we have inserted our own subtotal rows with SUM formulas. To have subtotals calculated automatically, use the Subtotal command with the summary function of your choice such as SUM, COUNT, AVERAGE, MIN, MAX, etc. The Subtotal command will not only insert summary rows but also create an outline with collapsible and expandable rows, thus completing two tasks at once!
Apply default Excel styles to summary rows
Microsoft Excel has the predefined styles for two levels of summary rows: RowLevel_1 (bold) and RowLevel_2 (italic). You can apply these styles before or after grouping rows. To automatically apply Excel styles to a new outline, go to the Data tab > Outline group, click the Outline dialog box launcher, and then select the Automatic styles check box, and click OK. After that you create an outline as usual.
To apply styles to an existing outline, you also select the Automatic styles box as shown above, but click the Apply Styles button instead of OK. Here’s how an Excel outline with the default styles for summary rows looks like:
How to select and copy only visible rows
After you’ve collapsed irrelevant rows, you may want to copy the displayed relevant data somewhere else. However, when you select the visible rows in the usual way using the mouse, you are actually selecting the hidden rows as well. To select only the visible rows, you’ll need to perform a few extra steps:
- Select visible rows using the mouse.For example, we have collapsed all of the detail rows, and now select the visible summary rows:
- Head to the Home tab >Editing group, and click Find & Select > Go To Special. Or press Ctrl + G (Go To shortcut) and click the Special… button.
- In the Go To Special dialog box, select Visible cells only and click OK.
As the result, only the visible rows are selected (the rows adjacent to hidden rows are marked with a white border):
And now, you simply press Ctrl + C to copy the selected rows and Ctrl + V to paste them wherever you like.
How to hide and show outline symbols
To hide or display the outline bars and level numbers in Excel, use the following keyboard shortcut: Ctrl + 8. Pressing the shortcut for the first time hides the outline symbols, pressing it again redisplays the outline.
The outline symbols don’t show up in Excel
If you can see neither the plus and minus symbols in the group bars nor the numbers at the top of the outline, check the following setting in your Excel:
- Go to the File tab > Options > Advanced category.
- 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. In a similar fashion, you can group columns in your worksheets. I thank you for reading and hope to see you on our blog next week.
You may also be interested in
Excel’s automatic outlining feature makes outlining a worksheet fairly straightforward. Automatic outlining works best with numerical data organized into groups and sub groups by formulas or functions. The following worksheet, for example, contains monthly financial data for a business, organized into quarterly and yearly totals using formulas and the SUM function: It can be difficult to discern quarterly and yearly totals at a glance because these figures are lost in with all of the other data. To automatically outline this worksheet, click the arrow next to the Group button in the Outline group of the Data tab: This action will display a menu with two options: Group and Auto Outline. If you click the Auto Outline button, the spreadsheet will be outlined automatically: Here are the results of Excel’s automatic outline: All of the original data is shown, as well as outline group indicators (the thick black lines that look like large brackets) and collapse buttons (marked with a minus sign). You can see that the quarters (sets of three months) are grouped together and summarized by Quarterly totals, and that each year has been grouped to be summarized by Yearly Totals. Notice also that the columns Supplies, Wages, and Utilities have been grouped under Total Expenses, and that there is an overarching group of all columns under Profit. In the image that follows, you can see the summary results of Excel’s automatic outline. The original information is still available in all of its detail, but it is now presented in a summary view, showing only the yearly profit totals. Of course, automatic outlines will differ from worksheet to worksheet depending on your headings and data and the way they are organized. We’ll look at how to expand and collapse levels in the next lesson. To remove the outlining from your worksheet, click Data → Ungroup → Clear Outline:
Displaying and Collapsing Levels
Here is an outlined spreadsheet: Notice that there are numbered buttons arranged in a row beside the column letters and also in a column above the row numbers. Clicking on one of the numbered buttons arranged in a row will expand the rows in the worksheet to provide a given level of detail. Clicking on one of the numbered buttons arranged in a column will expand the columns in a similar way. The view of the data in the preceding image is provided by the buttons numbered 1. Clicking on the number 2 buttons will expand the worksheet to the second level of detail for the rows, columns, or both as required: This is the same worksheet after both number 2 buttons have been clicked. You can see that the worksheet rows and columns have been expanded to show another, secondary level of detail. You can now see rows with quarterly totals as well as yearly totals. You can also see columns with figures for Income and Total Expenses. If you click on an expand button, marked with a plus sign (+), a specific section of the outlined worksheet corresponding to the button will be shown. Clicking the collapse (–) button will collapse the corresponding expanded section.
If you click the number “3“ button for the rows and columns, all levels of detail will be expanded to expose all of the data, as shown in the original picture
at the beginning of this lesson. Remember that with outlines, only the data that is visible on your screen will be printed. This is a great way to print only the pertinent information from a large or complicated worksheet. You can expand and collapse the outlined worksheet with the numbered outline buttons, or with the expansion (+) and collapse (–) buttons, to reveal the level of detail that you want in your printed copy. Hany Ismael is the founder and CEO of Planning Engineer Est. in Egypt. He has started his career back in 2003 as a site engineer, technical office engineer, planning engineer, planning manager, and finally planning department manager where he has been involved in several mega construction projects in Egypt and Saudi Arabia. In 2016, he established his own company in Egypt “Planning Engineer Est.” Hany gained his MSc degree in project management from Liverpool University-UK 2013-2016, PMP certified from PMI-USA 2010, and BSc Civil Engineer Tanta University-Egypt 2003. Hany provided more than 3,500 hours of planning and project management training on his website planningengineer.net, YouTube channel, and offline courses since 2011. He enjoys teaching project management in simple and practical way, and he developed several planning tools, techniques and courses. It can be tough to organize a lengthy spreadsheet to make your data easier to read. Microsoft Excel offers a useful grouping feature to summarize data using an automatic outline. Here’s how it’s done.
What You Need to Create an Outline in Excel
In Microsoft Excel, you can create an outline of rows, columns, or both. To explain the basics of this topic, we’ll create an outline of rows. You can apply the same principles if you want an outline for columns. For the feature to serve its purpose, there are a few things that you’ll need your data to include:
- Each column must have a header or label in the first row.
- Each column should include similar data.
- The cell range must contain data. You cannot have blank columns or rows.
It’s easiest to have your summary rows located below the data that they summarize. However, there is a way to accommodate this if your summary rows are currently positioned above instead. We’ll describe how to do this first.
Adjust the Outline Settings
Select the cells that you want to outline and go to the Data tab. Click “Outline” on the right side of the ribbon. Then, click the dialog launcher (tiny arrow) on the bottom right of the pop-out window. When the Settings window opens, uncheck the box for “Summary Rows Below Detail.” Before you click “OK,” you can optionally check the box for “Automatic Styles.” This will format the cells in your outline with bold, italics, and similar styles to make them stand out. If you choose not to use Automatic Styles here, we’ll show you how to apply them afterward, too. Click “OK” and get ready to create the outline. If you have your summary rows and other outline requirements set, it’s time to create your outline. Select your cells, go to the Data tab, and click “Outline.” Click the “Group” arrow and choose “Auto Outline” in the drop-down list. You should see your spreadsheet update immediately to display the outline. This includes numbers, corresponding lines, and plus and minus signs in the gray area to the left of the rows or at the top of the columns. The lowest number (1) and the furthest-left buttons below the 1 are for your highest-level view. The next-highest number (2) and the buttons below it are for the second-highest level. The numbers and buttons continue for each level until the final one. You can have up to eight levels in an Excel outline. You can use the numbers, the plus and minus signs, or both to collapse and expand your rows. If you click a number, it will collapse or expand that entire level. If you click a plus sign, it will expand that particular set of rows in the outline. A minus sign will collapse that particular set of rows.
Format Styles After Creating the Outline
As previously mentioned, you can apply styles to your outline to make rows and summary rows stand out. In addition to the outline itself, this helps make the data a bit easier to read and distinguish from the rest. If you choose not to use the Automatic Styles option before creating your outline, you can do so afterward. Select the cells in the outline that you want to format, or select the entire outline if you prefer. Head back to the outline settings window with Data > Outline to open the dialog launcher. In the Settings window, check the box for “Automatic Styles,” and then click “Apply Styles.” You should see the formatting styles applied to your outline. You can now click “OK” to close the window. RELATED: Copy Excel Formatting the Easy Way with Format Painter
Remove an Outline
If you create an outline and decide to remove it later, it’s a simple couple of clicks. Select your outline and head back to that Data tab one more time. Click “Outline,” and then the arrow below “Ungroup.” Pick “Clear Outline,” and you’re set.
Note: If you applied styles to your outline, you’d need to reformat your text manually.
Outlines aren’t just handy for preparing documents. In Excel, an outline gives you a terrific way to organize and more easily analyze your data. The automatic outline takes almost all of the manual work out of the process. RELATED: How to Use Pivot Tables to Analyze Excel Data READ NEXT
- › How to Group and Ungroup Rows and Columns in Google Sheets
- › How to Group Rows in Microsoft Excel
- › How to Group Columns in Microsoft Excel
- › How to Quickly Search the Menus in Microsoft Office
- › How to Add a BCC in Outlook Emails
- › The Best VoIP Services of 2022
- › Google Pixel 7 Pro Review: The Android Smartphone to Beat
- › You’re Tracking Your Sleep Wrong
- How to find an address from the latitude and longitude
- How to add safari to home screen
- How to do high knees
- How to clean parquet floors
- How to etch glass with etching cream