How to Group and Outline Excel Data: Easily Collapse Rows of Info

Outlining (grouping) data in Excel is a great way to organize and summarize data. This feature nests your information into up to eight levels. Inner levels have the detailed data for the surrounding outer level. As long as your data has column headings and no blank rows, you can automatically group and outline automatically with Excel. This wikiHow guide teaches you how to group and outline Excel data so you can work with large data sets more efficiently. This works on Windows and Mac!

Things You Should Know

Prepare your data by making column or row headers and getting rid of blank rows and columns.
Outline rows or columns automatically by selecting a cell in the data and going to Data > Group > Auto Outline.
For the manual method, click the Group button and choose “Rows” or “Columns.”

Part 1

Part 1 of 4:

Preparing Your Data

Download Article

1
Organize the data you want to outline. Each column should have a column header in the first row. Make sure the range you’re going to outline doesn’t contain blank rows or columns.^{[1]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}
- For a general spreadsheet guide, check out how to make a spreadsheet in Excel and format it.
- If you’re looking for Excel database info, read our guide on creating a database from an Excel spreadsheet.
2
Optionally, create a summary row. This is also called a subtotal. You have two options for this:
- Select a cell in the data range. Go to the Data tab and click Subtotal in the Outline group.
- Insert summary rows with your own formulas. For example, you could use the SUM function to subtotal information. These can go above or below the data.
- If you place your summary rows above the data, open the dialog box in the Outline group of the Data tab (it’s the right angle with an arrow). Uncheck “Summary rows below detail.”

Part 2

Part 2 of 4:

Outlining Automatically

Download Article

1
Select a cell that’s in the range you’re going to outline. This can be any cell in the data.
- Your data must have column headers and no blank lines for this feature to work.
It's on the left side of the green ribbon that's at the top of the Excel window. Doing so will open a toolbar below the ribbon.
You'll find this option on the far-right side of the Data tab. A drop-down menu will appear.
4
Click Auto Outline. It's in the Group drop-down menu.
- If you receive a pop-up box that says "Cannot create an outline", your data doesn't have an outline-compatible formula in it. You may also have some blank cells in your data or missing column headers. You'll need to manually outline the data.

Part 3

Part 3 of 4:

Outlining Manually

Download Article

Click and drag your cursor from the top-left cell of the data you want to group to the bottom-right cell of the data.
It's in the left side of the green ribbon at the top of Excel.
It's on the right side of the Data toolbar.
Click Rows to minimize your data vertically, or click Columns to minimize horizontally.
It's at the bottom of the pop-up window. Your grouped data is all set!

Part 4

Part 4 of 4:

Minimizing & Clearing

Download Article

Click the [-] button at the top or on the left side of the Excel spreadsheet to hide the grouped data. In most cases, doing this will only display the final line of the data.
Click Ungroup to the right of the Group option, then click Clear Outline... in the drop-down menu. This will ungroup and unhide any data that was minimized or grouped previously.

Community Q&A

Search

Add New Question

Question

How do I reverse the grouping so that the total is at the top line and the collapsed lines fall below?

Community Answer

Click the "Data" tab, then come to the "Outline" section, then click the small arrow on the right bottom corner to "Show the Outline Dialog Box". From the settings, unclick "Summary Rows Below Detail."

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 15 Helpful 29
Question

My data is grouped, but I cannot see the outline symbols along the left side of my spreadsheet. What can I do?

Community Answer

While the document is open, go to "File," "Options," "Advanced," "Display options for this worksheet." Make sure "Show outline symbols if an outline is applied" is selected. This is necessary for every sheet where there are outlines/groupings applied.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 0 Helpful 5
Question

How do I group Rows 23 through 31 and then group Rows 32 through 36? When I do it, Excel groups Rows 23 thru 36.

Community Answer

You need an empty row between your two groups. Otherwise Excel will automatically merge them.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 0 Helpful 4

See more answers

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Video

Tips

You cannot use this function if the sheet is shared.

Thanks
Helpful 0 Not Helpful 0

Submit a Tip

All tip submissions are carefully reviewed before being published

Name

Please provide your name and last initial

Submit

Thanks for submitting a tip for review!

Warnings

Don't use grouping/outlining if you plan to protect the worksheet. If you do, other users won't be able to expand and collapse the rows.

Thanks
Helpful 0 Not Helpful 0