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: In case you want to remove all row groups at once, then clear the outline.
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.
Below you will find a few useful tricks that will make your work with groups even easier. Microsoft Excel has the predefined styles for two levels of summary rows: However_1 (bold) and However_2 (italic).
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.
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.
Step 1: Select the range of rows you want to group. For example, if you want to group the country CANADA then selects the full range.
You can also type the Excel shortcut key SHIFT + ALT + RIGHT ARROW (hold Shift & Alt key together and press Right Arrow). 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). If you’re editing multiple worksheets in Microsoft Excel, it might be helpful to group them together.
When you press and hold Ctrl, you can select multiple individual worksheets and group them together. After you’ve finished making changes to multiple worksheets, you can ungroup them in two ways.
The quickest method is to right-click a selected worksheet at the bottom of the Excel window, and then click “Ungroup Sheets.” Just press and hold Ctrl, and then select the sheets you want to remove from the group.
In a past life, he was a UK college lecturer, training teens and adults. Since leaving the classroom, he's been a tech writer, writing how-to articles and tutorials for MakeUseOf, MakeTechEasier, and Cloud wards.net.
Click on the tiny arrow in the bottom corner of the outline section. Set the ticks according to your desired direction, e.g. “Summary rows below detail”.
When grouping you can often use the Ctrl or Shift keys to make multiple selections. The advantage with using grouping to do the hiding is that icons are added to the sheet to make it obvious that rows or columns are hidden.
Those icons also make it easy to hide and inside the rows and columns. See a previous blog post on grouping rows and columns here.
You can right-click the icon to add it to the Quick Access Toolbar. This is especially useful for groups of tasks in a Gantt chart and other types of hierarchical lists where you may want to toggle certain rows on/off.
In this article, I'll explain how to use the Group and Outline feature using a simple Gantt chart as an example. Step 1: Select the rows that you want to hide when the button is clicked.
To Ungroup, you can use the keyboard shortcut Shift+Alt+ (Left Arrow Key). I find that it makes more sense to have the toggle buttons at the top of each group.
Step 2: Uncheck the “Summary rows below detail” box. If you could end up with more than 9 items in a given level within your outline, the formula gets a lot more complicated.
But you can switch things and have the icon at the top of the grouped rows. These are the rows that make the final formula easier to create or shorter.
The image below using grouping to hide rows 1 to 5. In the Data ribbon, on the right-hand side, there is an Outline section.
Click the little arrow icon in the bottom right-hand corner of the Outline section. The shortcut keys to apply and remove grouping are shown below.
Lesson 10: Sorting, Grouping, and Filtering Cells A Microsoft Excel spreadsheet can contain a great deal of information. With more rows and columns than previous versions, Excel 2007 gives you the ability to analyze and work with an enormous amount of data.
To most effectively use this data, you may need to manipulate it in different ways. In this lesson, you will learn how to sort, group, and filter data in various ways that will enable you to most effectively and efficiently use spreadsheets to locate and analyze information.
A Microsoft Excel spreadsheet can contain a great deal of information. Sorting lists is a common spreadsheet task that allows you to easily reorder your data.
To sort in alphabetical order: Select a cell in the column you want to sort (In this example, we choose a cell in column A). Click the Sort & Filter command in the Editing group on the Home tab.
Now the information in the Category column is organized in alphabetical order. Click the Sort & Filter command in the Editing group on the Home tab.
Now the information is organized from the smallest to the largest amount. You can sort in reverse numerical order by choosing From Largest to Smallest in the list.
In this section, we will learn how to create groups using the Subtotal command. To create groups with subtotals: Select any cell with information in it.
Click the Subtotal command on the Data tab. The information in your spreadsheet is automatically selected, and the Subtotal dialog box appears.
The selected cells are organized into groups with subtotals. To collapse or display the group: Click the black minus sign, which is the hide detail icon, to collapse the group.
Click the black plus sign, which is the show detail icon, to expand the group. To ungroup the entire worksheet: Select all cells with grouping.
Filtering, or temporarily hiding, data in a spreadsheet is simple. For example, if you would like to only view data regarding Flavors, click the drop-down arrow next to Category.
Just click the drop-down arrow next to Item, then select Text Filters. From the menu, choose Contains because you want to find any entry that has the word vanilla in it.
Details Last Updated: 28 October 2020 In this tutorial, we are going to cover the following topics. Let's assume you are recording student exam marks and you know the minimum is 0 and the maximum is 100.
You can take advantage of validation features to ensure that only values between 0 and 100 are entered. Add a new sheet in your workbook by clicking on the plus button at the bottom of the worksheet.
Let's say we want to show the results of all the students whose names start with “JA” or get scores that are less than, greater than or equal to a certain value, we can use filters to get such data. Enter “JA” and click on “OK” button You should be able to see only the results for Jane and James.
Groups allow us to view easily and hide unnecessary details from either columns or rows. In addition to that, we can also use groups to analyze data that belongs to a common category.
Click on Subtotal drop button under DATA tab The command for adding images is found under the INSERT tab on the ribbon.
In this article, we have learned how to perform basic arithmetic operations using Excel, format the data, and apply validation rules, filter data and how to take advantage of groups to further analyze data and improve presentation.