This is the last in a series of tips related to the Excel’s Subtotal feature which automatically inserts subtotals and grand totals into a list and groups the rows into a collapsible outline. Auto sum only works in certain situations. When it doesn’t work you can still accomplish the same thing with just a little more effort. Last month I explained how to write subtotal formulas. Now I’ll show you how to create the collapsible outline. If you have a long report in Excel that already has subtotals in it, and you want to make it more manageable, that’s a perfect time to manually set up grouping. Simply select all of the rows that you want to be able to hide (collapse) but not the row totaling them. Then click the Group button, which is located on the Data tab of the ribbon. In the left margin you’ll see a line appear next to the rows you just grouped. At the bottom will be a small box with a minus sign in it. Click that box to collapse or roll up those rows. The box now change to a plus sign. Click it to expand that section. Now simply repeat that process for each section you want to be able to collapse. You can even select a larger group of rows that already have groups set up within them. Then you can choose which level of detail you want to be able to see. As you add levels, numbered boxes will appear at the top of the left margin allowing you to expand or collapse everything to the corresponding detail level. To undo a grouping, select those rows again and click the Ungroup button.To get rid of all of your groupings, simply click the down arrow below the Ungroup button and choose Clear Outline. One additional option is called Auto Outline. To have Excel automatically create groups, click the down arrow below the Group button and choose Auto Outline. If you don’t like the result, clear the outline and manually create the groups any way you want.
Excel for Microsoft 365 Excel for the web Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 More...Less If you have a list of data you want to group and summarize, you can create an outline of up to eight levels. Each inner level, represented by a higher number in the outline symbols, displays detail data for the preceding outer level, represented by a lower number in the outline symbols. Use an outline to quickly display summary rows or columns, or to reveal the detail data for each group. You can create an outline of rows (as shown in the example below), an outline of columns, or an outline of both rows and columns.
Make sure that each column of the data that you want to outline has a label in the first row (e.g., Region), contains similar facts in each column, and that the range you want to outline has no blank rows or columns. If you want, your grouped detail rows can have a corresponding summary row—a subtotal. To create these, do one of the following: Insert summary rows by using the Subtotal command Use the Subtotal command, which inserts the SUBTOTAL function immediately below or above each group of detail rows and automatically creates the outline for you. For more information about using the Subtotal function, see SUBTOTAL function. Insert your own summary rows Insert your own summary rows, with formulas, immediately below or above each group of detail rows. For example, under (or above) the rows of sales data for March and April, use the SUM function to subtotal the sales for those months. The table later in this topic shows you an example of this. By default, Excel looks for summary rows below the details they summarize, but it's possible to create them above the detail rows. If you created the summary rows below the details, skip to the next step (step 4). If you created your summary rows above your detail rows, on the Data tab, in the Outline group, click the dialog box launcher. The Settings dialog box opens.
Then in the Settings dialog box, clear the Summary rows below detail checkbox, and then click OK. Outline your data. Do one of the following: Outline the data automatically
Outline the data manually
Important: When you manually group outline levels, it's best to have all data displayed to avoid grouping the rows incorrectly.
You can also ungroup sections of the outline without removing the entire level. Hold down SHIFT while you click the or for the group, and then on the Data tab, in the Outline group, click Ungroup.If you ungroup an outline while the detail data is hidden, the detail columns may remain hidden. To display the data, drag across the visible column letters adjacent to the hidden columns. On the Home tab, in the Cells group, click Format, point to Hide & Unhide, and then click Unhide Columns
For outlined rows, Microsoft Excel uses styles such as RowLevel_1 and RowLevel_2 . For outlined columns, Excel uses styles such as ColLevel_1 and ColLevel_2. These styles use bold, italic, and other text formats to differentiate the summary rows or columns in your data. By changing the way each of these styles is defined, you can apply different text and cell formats to customize the appearance of your outline. You can apply a style to an outline either when you create the outline or after you create it. Do one or more of the following: Automatically apply a style to new summary rows or columns
Apply a style to an existing summary row or column
You can also use autoformats to format outlined data.
Imagine that you want to create a summary report of your data that only displays totals accompanied by a chart of those totals. In general, you can do the following:
You can group (or outline) rows and columns in Excel for the web.
Note: Although you can add summary rows or columns to your data (by using functions such as SUM or SUBTOTAL), you cannot apply styles or set a position for summary rows and columns in Excel for the web.
Do one or more of the following: Show or hide the detail data for a group
Expand or collapse the entire outline to a particular level
Show or hide all of the outlined detail data
You can always ask an expert in the Excel Tech Community or get support in the Answers community. Group or ungroup data in a PivotTable |