Create Auto Outline in Excel to Group Rows & Columns

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on July 28, 2023

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

auto outline intro

Excel has grouping functionality that combines rows and/or columns into logical groups. If 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 anywhere 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.

auto outline data

  1. In the Ribbon, go to Data > Outline > Group > Auto Outline.

auto outline ribbon

The data is automatically organized into groups based on the subtotals found in the data (the total of each city and the total of each quarter).

auto outline outlined

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

auto outline collapse buttons

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

 auto outline collapse 1 2

    1. Then, use the expand buttons to show all rows once again.

auto outline expand

To expand all groups at once, click the 2 buttons in the top-left corner of the screen.

auto outline collapse 2

Remove Auto Outline

  1. To remove an existing outline, first select any cell within the outlined data.
  2. Then in the Ribbon, go to Data > Outline > Ungroup > Clear Outline.

auto outline clear outline

None of the data changes, but the outline buttons are gone, and anything that was hidden (collapsed) is visible.

auto outline data

AI Formula Generator

Try for Free

See all How-To Articles