Outlines in Excel: How to Collapse Groups of Data in an Outline

Outlines in Excel: How to Collapse Groups of Data in an Outline
Coming up next: How to Insert a Subtotal in an Excel Worksheet

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
Your next lesson will play in 10 seconds
  • 0:01 Outlines in Excel Worksheets
  • 0:45 Quick Review
  • 2:53 Hierarchy or Level Buttons
  • 4:03 Expand and Collapse Controls
  • 5:18 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up


Recommended Lessons and Courses for You

Lesson Transcript
Instructor: Karen Sorensen

Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.

When you add an outline to your worksheet data, a window opens on the left-hand side of your screen with numbered buttons and plus or minus symbols. This lesson will take a detailed look at how to expand and/or collapse data in an outline.

Outlines in Excel Worksheets

Using outlines and grouping data together in Excel worksheets is likely one of my favorite features. One: because it's so easy to use. Two: because it automatically creates groups that allow you to hide or show detailed data. It really comes in handy when you have a lot of data - especially financial data.

In this lesson, we will first quickly review how to create an outline in an Excel worksheet. Next, we will take a more detailed look at the hierarchy or outline level buttons that the outline creates. Finally, we will look at the expand and collapse controls that appear when you create an outline.

Quick Review

Before we learn about the buttons and controls that appear when you create an outline, let's first review the steps to adding an outline to existing worksheet data. There are several ways to create or add an outline to your worksheet data. You can use the Auto Outline command or add the outline manually using the Group or Subtotal commands. In this example, we will use the Subtotal command to add our outline.

We need an example to work with, so imagine you are the proud owner of Mac's MooTime Ice Cream. You have three locations: San Diego, San Francisco and Sacramento. They are small but very successful ice cream shops. You have put together a worksheet for the previous year's revenue results.

The worksheet is detailed by manager, location and the type of ice cream treats that were sold in each quarter: Cones, Shakes, Malts, Sundaes, Popsicles/Bars and Smoothies. The worksheet data looks fairly good and organized, but it will look even better once we apply an outline. Here are the steps:

  1. Select the worksheet data.
  2. Go to the Data menu in the ribbon and look in the Outline group.
  3. Click on the Subtotal command.
  4. Select how you want it subtotaled (in our example, this would be by location and for each of the ice cream treat categories).
  5. Click OK.

Just like that, the data has been grouped by location and revenue numbers are totaled.

Notice the outline window, which opened on the left-hand side of the worksheet:

Outline Window
example spreadsheet with outline window

Buttons with numbers have been added, along with minus (-) and plus (+) signs. Let's take a closer look at how to use the buttons and controls.

Hierarchy or Level Buttons

The outline becomes interactive with buttons and controls to help you navigate the data. Let's start with the hierarchy or level buttons. You will notice that the outline created three levels; therefore, we have three buttons. Each is numbered, 1, 2 and 3.

  • Level One: The button numbered 1 takes you to the first level of data, which shows only the Grand Total row. This level shows the least amount of detailed data.
  • Level Two: The second level, or button number 2, takes you to the next level of data, which is the individual store totals. This level shows more detailed data than level one but not as much as level three.
  • Level Three: This level will show all of the worksheet data.

We don't have an enormous amount of data in our example, but if we did, you could easily see the different levels of data using these buttons.

To unlock this lesson you must be a Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use

Become a member and start learning now.
Become a Member  Back
What teachers are saying about
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account