Outlining Data

Use outlines in Excel to help organize your data into groups and summarize data for easier analysis.

Outlining data

Video: Outlining Data in Excel 2010

Launch video!Watch the video (4:19).

Outlines give you the ability to group data you may want to show or hide from view, as well as to create a quick summary using the Subtotal command. Because outlines rely on grouping data that is related, you must sort before you can outline. For more information, you may want to review our lesson on Sorting Data.

Optional: You can download this example for extra practice.

Outlining data using Subtotal

The Subtotal command can be used to outline your worksheet in several ways. It uses common functions like SUM, COUNT, and AVERAGE to summarize your data and place it in a group. To learn more about functions, visit our Working with Basic Functions lesson.

In this example, we'll use the Subtotal command to count the number of T-shirt sizes that were ordered at a local high school. This will also place each T-shirt size in a group, making it possible to show the count but hide the details that are not crucial to placing the order (such as a student's homeroom number and payment date).

To outline data using Subtotal:

  1. Sort according to the data you want to outline. Outlines rely on grouping data that is related. In this example, we will outline the worksheet by T-Shirt Size, which has been sorted from smallest to largest.
    Sorted by t-shirt sizeSorted by T-shirt size
  2. Select the Data tab, then locate the Outline group.
  3. Click the Subtotal command to open the Subtotal dialog box.
    Opening the Subtotal dialog boxOpening the Subtotal dialog box
  4. In the At each change in field, select the column you want to use to outline your worksheet. In this example, we'll choose T-Shirt Size.
  5. In the Use function field, choose from the list of functions that are available for subtotaling. We'll use the COUNT function to tally the number of each size.
  6. Select the column you want the subtotal to appear in. We'll choose the T-Shirt Size column.
  7. Click OK.
    Clicking OK to subtotalClicking OK to subtotal
  8. The contents of your worksheet will be outlined. Each T-shirt size will be placed in its own group, and the subtotal (count, in this case) will be listed below each group.
    Outlined with subtotalOutlined with subtotal