See all How-To Articles

Create Auto Outline in Excel to Group Rows & Columns

This tutorial will demonstrate how to automatically create an outline to group rows and columns in Excel.

 

autooutline intro

 

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

  1. 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.

 

autooutline data

 

  1. In the Ribbon, select Data > Outline > Group > Auto Outline.

 

autooutline ribbon

 

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).

 

autooutline outlined

 

  1. To collapse each group, you can click on the little collapse buttons (minus signs) at the end of each group.

 

auto outline collapse buttons

 

OR
To collapse all groups, click on each of the 1 buttons in the top left-hand corner of the screen.

 

 auto outline collapse 1 2

 

  1. Once the data has been collapsed, you can then use the expand buttons to expand the data once again.

 

autooutline expand

 

OR
To expand all the groups, you can click on the 2 buttons in the top left-hand corner of the screen.

 

autooutline collapse 2

 

Remove Auto Outline

Click in the data that contains the outline, and then, in the Ribbon, select Data > Outline > Ungroup > Clear Outline.

 

autooutline clearoutline

 

The outline is removed.

 

autooutline data