Expert Guide: Building a Dynamic Summary Chart with Microsoft Excel

Quick Links

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.

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.

When the Settings window opens, uncheck the box for “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.

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.”

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

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.

Glarysoft File Recovery Pro - Helps to recover your lost file/data, even permanently deleted data.

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

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

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

vMix 4K - Software based live production. vMix 4K includes everything in vMix HD plus 4K support, PTZ control, External/Fullscreen output, 4 Virtual Outputs, 1 Replay, 4 vMix Call, and 2 Recorders.
This bundle includes Studio 200 for vMix from Virtualsetworks, HTTP Matrix 1.0 automation scheduler, and 4 introductory training videos from the Udemy vMix Basic to Amazing course.

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.

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.

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.”

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

Remove an Outline

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

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.

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.

Win Able

Expert Guide: Building a Dynamic Summary Chart with Microsoft Excel