Level 3 displays Grand total, Region and Item subtotals (rows 4, 8, 9, 18, 13, 16, 17 and 18). Level 2 displays Grand total and Region subtotals (rows 9, 17 and 18). Level 1 shows only Grand total (row 18 ) and hides all other rows. In our sample dataset, we have 4 outline levels, which work this way: 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). Level 1 displays the least amount of data while the highest number expands all the rows. To minimize or expand all the groups at a particular level, click the corresponding outline number at the top left corner of your worksheet. Collapse or expand the entire outline to a specific level To collapse the rows in a particular group, just click the minus button at the bottom of that group's bar.įor example, this is how you can quickly hide all detail rows for the East region, including subtotals, and show only the East Total row:Īnother 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:Įither way, the group will be minimized to the summary row, and all of the detail rows will be hidden. 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. Now that we have an outline of rows, let's see how it makes our data easier to view. 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.Īs shown in the screenshot below, our data is now grouped in 4 levels: If at some point more data is added to your worksheet, you will probably want to create more outline levels.Īs an example, let's insert the Grand total row in our table, and then add the outermost outline level. In practice, datasets are seldom complete. Similarly, we create nested groups for the North regions, and get the following result: To make the Oranges group, select rows 5 through 7, and press the Group button again. To create a nested (or inner) group, select all detail rows above the related summary row, and click the Group button.įor example, to create the Apples group within the East region, select rows 2 and 3, and hit Group. To create a new group faster, press the Shift + Alt + Right Arrow shortcut instead of clicking the Group button on the ribbon. Here is an example of what kind of rows Excel can group:Īs 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. 
Go to the Data tab > Outline group, click the arrow under Group, and select Auto Outline. Select any cell in one of the rows you want to group. If your dataset contains just one level of information, the fastest way would be to let Excel group rows for you automatically. Group rows automatically (create an outline) With the data properly organized, use one of the following ways to group it. 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. 
Apply default Excel styles to summary rows. 
Calculate group subtotals automatically.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. Worksheets with a lot of complex and detailed information are difficult to read and analyze. See how you can quickly hide rows within a certain group or collapse the entire outline to a particular level.
#CREATE REPEATING ROWS IN EXCEL 2011 FOR MAC HOW TO#
The tutorial shows how to group rows in Excel to make complicated spreadsheets easier to read.
0 kommentar(er)
