This tutorial will demonstrate how to automatically create an outline to group rows and columns in Excel.
Excel has a grouping functionality that enables you to combine rows and/or columns into logical groups. If the data in your worksheet is organized in a way that is compatible with Excel’s grouping functionality, you can use the Auto Outline feature. You would need to have the information correctly organized in a logical manner where there are subtotals for each level of information you wish to group on. The data cannot have any blank rows or columns, or spaces.
Create Auto Outline
- First, organize your data logically with subtotals and then click somewhere within the data.
In the example below, going down the rows, the data is organized into cities (London, Paris, and New York), and in each of these cities there are categories for values which are totaled at the top of each city. Going across the columns, the data is separated into months which are then totaled after each quarter.
- In the Ribbon, select Data > Outline > Group > Auto Outline.
The data will automatically be organized into groups based on the subtotals found in the data (the total of each city and the total of each quarter).
- To collapse each group, you can click on the little collapse buttons (minus signs) at the end of each group.
To collapse all groups, click on each of the 1 buttons in the top left-hand corner of the screen.
- Once the data has been collapsed, you can then use the expand buttons to expand the data once again.
To expand all the groups, you can click on the 2 buttons in the top left-hand corner of the screen.
Remove Auto Outline
Click in the data that contains the outline, and then, in the Ribbon, select Data > Outline > Ungroup > Clear Outline.
The outline is removed.