Preparing To Outline

You must set data up with outlining in mind. The basic idea is that everything is organized in list format with similar pieces of data grouped together. We grouped the Burbank office’s widget sales and its gizmo sales, for example. Use summary rows (or columns, depending on the format) under each subsection.

To set up your outline, highlight blocks of data you want to expand and collapse as a unit. In our example, we grouped the cells that show the breakdown of who sold which widgets and gizmos. We highlighted the salespeople’s names, the column that indicates widgets, and the column that shows how many they sold. We chose Data, Group & Outline, and Group. Click the minus sign (-) to hide the breakdown of widget sales or click the plus sign (+) to see the details. We created similar groups by highlighting the figures for individual gizmo sales and each person’s total product sales.

Outlining worksheet information lets you view the bottom-line results at a glance or click a button to see all the figures behind the results.

In a setup like this, you should group the numbers that add up to the total but not the row that reports the total. Excel puts the plus/minus icon on the row below the last one you highlight. If your groups include the row that shows the totals, it will disappear when you click the minus sign to collapse the section. Notice in our example that the plus and minus signs appear next to the rows that total up the hidden numbers. When you set up groups properly, this lets the labels act as intuitive guides to what’s under each plus sign. Want to see the breakdown of gizmo sales? Click the plus sign beside the row labeled Burbank’s Gizmo Sales.

 Managing The Information

When finished, we have a worksheet that, at its simplest look, shows us the totals for widgets, gizmos, and overall units at the Burbank office. By clicking one of the three plus signs, we can see the breakdown of who sold how many of each item and how each salesperson fared overall.

Grouping sales results for 10 different offices in one spreadsheet may seem handy, but doing it on a single page of nested levels might get messy. You could keep it accessible by separating items into logical groups on different worksheets in the file.

If the outline symbols fail to appear on the left side of your screen, make sure they’re turned on by going to Tools and Options. Toward the bottom of the View tab, make sure the Outline Symbols box is selected.

Look for the small numbers that appear in the upper-left corner of an outlined spreadsheet. Clicking them expands the data at that level and above. If your outline goes three levels deep, clicking the number 2 expands the first and second levels of detail but leaves the third collapsed.

Now that you understand the basics of how outlining works, it’s safe to let you in on a secret: Excel can handle most outline jobs automatically. If you set your data up properly (with summary formulas in the right places, such as at the bottom of the rows they’re totaling), Excel does a pretty good job of figuring out what makes intelligent groups. To outline the entire worksheet, click any cell and choose Data, Group & Outline, and Auto Outline.

To remove outlining at any time, choose Data, Group & Outline, and Clear Outline. To ungroup specific sections of cells, highlight the cells and choose Data, Group & Outline, and Ungroup.