Hide or show rows or columnsExcel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 More...Less Show
Hide or unhide columns in your spreadsheet to show just the data that you need to see or print.
 Hide columns
Note: The double line between two columns is an indicator that you've hidden a column. Unhide columns
Or double-click the double line between the two columns where hidden columns exist. Need more help?You can always ask an expert in the Excel Tech Community or get support in the Answers community. See AlsoUnhide the first column or row in a worksheet Need more help?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 ExcelGrouping 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:
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 How to group rows manuallyIf 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 necessaryIn 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:
Now that we have an outline of rows, let's see how it makes our data easier to view. How to collapse rows in ExcelOne 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 groupTo collapse the rows in a particular group, just click the minus button 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 levelTo 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:
The following screenshot demonstrates the outline collapsed to level 3. How to expand rows in ExcelTo 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 How to remove outline in ExcelIn 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 outlineGo to the Data tab > Outline group, click the arrow under Ungroup, and then click Clear Outline. Notes:
How to ungroup a certain group of rowsTo remove grouping for certain rows without deleting the whole outline, do the following:
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 tipsAs 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 automaticallyIn 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 rowsMicrosoft 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 rowsAfter 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:
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 symbolsTo 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 ExcelIf 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:
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 inWhich Excel feature allows you to hide rows or column?Hide columns
Select one or more columns, and then press Ctrl to select additional columns that aren't adjacent. Right-click the selected columns, and then select Hide.
How do I hide an expand collapse column in Excel?In Excel 2016 and Excel 2013: On the Analyze tab, in the Show group, click +/- Buttons to show or hide the expand and collapse buttons. Note: Expand and collapse buttons are available only for fields that have detail data.
How do you hide rows and columns in Excel with expand collapse?The shortcut for hiding columns in Excel is Ctrl + 0. For the sake of clarity, the last key is zero, not the uppercase letter "O". To hide a single column, select any cell within it, then use the shortcut. To hide multiple columns, select one or more cells in each column, and then press the key combination.
How do I hide and collapse rows in Excel?How to Use the Excel Collapse Rows Feature?. Click on the arrow icon in the Outline group of the Data tab.. + and – buttons will appear on the left border to indicate collapsed or expanded rows.. Use the + and – buttons to toggle on/off the Excel collapse feature.. Add multiple layers of nested collapsed rows if required.. |
Which excel feature allows you to hide rows or columns with an easily visible expand collapse?
Related Posts
Copyright © 2024 ShotOnMac Inc.
