We show you how to summarize important data in your Excel 2007 worksheets and reduce the data to more manageable levels with the ultra-efficient outline tool.
When you have a lot of data in an
Excel worksheet it often becomes necessary to summarize the information to get
to the crux of what it is telling you. There are a number of ways to do this in
Excel, one of which is to use Excel's built-in outlining function.
This function allows you to shrink data down to more manageable quantities so
that you can see the bottom-line result rather than all the items and pieces
that go into making it. In this article we'll illustrate and walk through the
process of using the Outline tools in Excel 2007 to summarize worksheet data.
Excel contains an automatic outline tool that works well in most cases, but
if your data doesn't lend itself to automatic outlining you can also create an
outline manually. Let's look first at some data that can be outlined
automatically. This worksheet contains some basic personal spending data and it
has totals for income and expenses in columns D and I of the worksheet.
To outline this, click
somewhere inside the range containing the data and next click the Data tab. From
the Group dropdown list select Auto Outline
. If Excel cannot create an
outline for your data, you will see an error in which case you'll need to
outline it manually as shown below.
In this instance, however, Excel can create an auto-outline, and you will see
the outline symbols appearing above the worksheet. Use the plus (+) and minus
(-) symbols to expand and contract the worksheet data below. You can minimize
the worksheet to display only income and expenses totals.
If you decide that you no longer want the worksheet to be outlined, turn the
outlining off by clicking in a cell inside the outlined area and, from the Data
tab, selecting Ungroup > Clear Outline.
an Outline Manually
If its not possible to
outline a worksheet using the outline tool you can manually create an outline
for your data. To do this, lets take a worksheet containing an inventory list
like the one shown in the illustration on the left. If we want to be able to
hide columns B and C and just see columns A and D, we can add an outline group
that spans columns B and C.
To do this, select columns B
and C and from the Data tab select Group > Group
. This groups these
two columns and lets you display or hide them as desired, so you can now see all
the data or just the Item number and the number of those items in stock.
It is also possible to create an outlined worksheet
automatically when you use the Subtotals command to subtotal data in your
worksheet. Let's go back to the inventory worksheet and remove the current
To add subtotals for the data, click in a cell in the data area and choose
the Data tab and click Subtotal. When the Subtotal dialog appears, select the
column name by which you want to subtotal your data the data needs to be
sorted in order by this column so like values are adjacent to each other. In our
case we'll subtotal by the Color column.
As we want to sum the number of items for each color, choose Sum from the Use
Function list and then, because we need a sum of the Number in Stock for each
color, we'll select the Number in Stock checkbox.
Enable the Replace Current Subtotals checkbox and the Summary Below Data checkbox, but unless you really want a page break between each item, disable the Page Break Between Groups
checkbox. Click OK and Excel will automatically subtotal the data for you, and in doing so it will create an outline based on these subtotals.
It is also possible to group this worksheet data so that you can hide columns B and C in addition to the grouped rows. So, with the outline already in place, manually add the grouping to columns B and C by selecting these columns and group them by choosing Data tab > Group > Group.
» Outlines and Views
When youre working on a worksheet with a lot of data and multilevel outlines you can create the expanded or compressed outlines as Custom Views so that you can return to them anytime. To do this, display your worksheet data as you want to see it think of this as version #1. Now, from the View tab select Custom Views, click Add, type a name for the view, and then click OK.
Now let's change the outlining display to your version #2. Look for the worksheet data by expanding or compressing the grouped items.
Save this layout as a second view with a different name. Then, when youre done you can then select the Custom Views button, click a View by name and click Show to display it.
The worksheet will change to show the outline as it was when you created the view. You can switch between views from the Custom Views menu with two clicks instead of having to reset the outline manually each time.
So, next time you need to summarize the data in your worksheet and reduce it to more manageable levels the outline tool can help you group Excel data in an intelligent way.
Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com
Adapted from winplanet.com.
|Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today! |