Microsoft Excel logo

It can be tough to organize a lengthy spreadsheet to make your data easier to read. Microsoft Excel offers a useful grouping feature to summarize data using an automatic outline. Here’s how it’s done.

What You Need to Create an Outline in Excel

In Microsoft Excel, you can create an outline of rows, columns, or both. To explain the basics of this topic, we’ll create an outline of rows. You can apply the same principles if you want an outline for columns.

For the feature to serve its purpose, there are a few things that you’ll need your data to include:

  • Each column must have a header or label in the first row.
  • Each column should include similar data.
  • The cell range must contain data. You cannot have blank columns or rows.

It’s easiest to have your summary rows located below the data that they summarize. However, there is a way to accommodate this if your summary rows are currently positioned above instead. We’ll describe how to do this first.

Adjust the Outline Settings

Select the cells that you want to outline and go to the Data tab.

Advertisement

Click “Outline” on the right side of the ribbon. Then, click the dialog launcher (tiny arrow) on the bottom right of the pop-out window.

Go to Data, Outline, and click the dialog launcher

When the Settings window opens, uncheck the box for “Summary Rows Below Detail.”

Uncheck Summary Rows Below Detail

Before you click “OK,” you can optionally check the box for “Automatic Styles.” This will format the cells in your outline with bold, italics, and similar styles to make them stand out. If you choose not to use Automatic Styles here, we’ll show you how to apply them afterward, too.

Optionally check Automatic Styles

Click “OK” and get ready to create the outline.

Create the Automatic Outline

If you have your summary rows and other outline requirements set, it’s time to create your outline.

Select your cells, go to the Data tab, and click “Outline.”

Go to Data and click Outline

Click the “Group” arrow and choose “Auto Outline” in the drop-down list.

Click Group and then Auto Outline

Advertisement

You should see your spreadsheet update immediately to display the outline. This includes numbers, corresponding lines, and plus and minus signs in the gray area to the left of the rows or at the top of the columns.

Auto Outline created in Excel

The lowest number (1) and the furthest-left buttons below the 1 are for your highest-level view.

Excel Outline Level 1

The next-highest number (2) and the buttons below it are for the second-highest level.

Excel Outline Level 2

The numbers and buttons continue for each level until the final one. You can have up to eight levels in an Excel outline.

Excel Outline Level 3

You can use the numbers, the plus and minus signs, or both to collapse and expand your rows. If you click a number, it will collapse or expand that entire level. If you click a plus sign, it will expand that particular set of rows in the outline. A minus sign will collapse that particular set of rows.

Excel Outline Levels

Format Styles After Creating the Outline

As previously mentioned, you can apply styles to your outline to make rows and summary rows stand out. In addition to the outline itself, this helps make the data a bit easier to read and distinguish from the rest.

Advertisement

If you choose not to use the Automatic Styles option before creating your outline, you can do so afterward.

Select the cells in the outline that you want to format, or select the entire outline if you prefer. Head back to the outline settings window with Data > Outline to open the dialog launcher.

In the Settings window, check the box for “Automatic Styles,” and then click “Apply Styles.”

Apply Styles to an Excel Outline

You should see the formatting styles applied to your outline. You can now click “OK” to close the window.

Excel Outline with Styles

RELATED: Copy Excel Formatting the Easy Way with Format Painter

Remove an Outline

If you create an outline and decide to remove it later, it’s a simple couple of clicks.

Advertisement

Select your outline and head back to that Data tab one more time. Click “Outline,” and then the arrow below “Ungroup.” Pick “Clear Outline,” and you’re set.

Click Outline, Ungroup, Clear Outline

Note: If you applied styles to your outline, you’d need to reformat your text manually.

Outlines aren’t just handy for preparing documents. In Excel, an outline gives you a terrific way to organize and more easily analyze your data. The automatic outline takes almost all of the manual work out of the process.

RELATED: How to Use Pivot Tables to Analyze Excel Data

Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »

The above article may contain affiliate links, which help support How-To Geek.