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.
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.
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. The following screenshot demonstrates the outline collapsed to level 3. 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:. 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. For example, here's how you can ungroup two nested row groups Apples Subtotal and Oranges Subtotal while keeping the outer East Total group:.
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. In all of the above examples, we have inserted our own subtotal rows with SUM formulas.
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! You can apply these styles before or after grouping rows. You may also look at these useful functions in excel —. Download Group Rows Excel Template.
Forgot Password? Free Excel Course. Login details for this Free course will be emailed to you. Article by Jeevan A Y.
Follow the below steps to group rows in excel. First create a subtotal like the below one. Now select the first state rows California City , excluding subtotals. Password Reset. Please enter the email address for your account. A verification code will be sent to you. Once you have received the verification code, you will be able to choose a new password for your account.
About Us Our team. How to expand all collapsed columns or rows in Excel? Group and Ungroup Filter and Nonfilter Hide and Unhide Unhide all range with one click Group and Ungroup In the first case, if your collapse columns and rows are displayed as below screenshot shown, you can use the Group and Ungroup functions.
Method 1 Click at the plus sign to change it to minus sign to display the collapse columns or rows. Filter and Nonfilter If the data is collapsed as below screenshot shown, you can clear the filter to display all. Hide and Unhide If the data is hidden as this screenshot shown, you can use the Unhide function to show cells.
0コメント