Excel 2003: The Missing Manual

Chapter 14. Grouping and Outlining Data

As you saw in the last chapter, Excel’s data lists are great tools for managing big tables made up of long, uniform columns (like lists of customers or products). But these data lists don’t work as well for tables that show different groups of information, each of which has its own collection of subtotals.

For example, imagine a company sales report that lists a year’s worth of quarterly results for each of its regions around the world. If you try to cram all this data into one long list—including subtotals for each region—you’re likely to end up with a spreadsheet that looks like a numerical version of Twister. You’d be better off breaking out each region separately, in its own table, and then tying everything together separately, in another table that sums everything up.

Excel’s grouping and outlining features are perfect for dealing with multiple tables. They help you quickly and easily expand and collapse big chunks of data and, in the process, make calculating summary information much easier. These tricks are low-tech and remarkably easy to implement, but they rank as one of Excel’s best-kept productivity secrets. In fact, Excel doesn’t even include a toolbar to work with grouping and outlining—which is a shortcoming you’ll learn to overcome in this chapter.

Basic Data Grouping

The starting point for simplifying your worksheets is learning how to group data. Grouping data lets you tie related columns or rows into a single unit. Once you’ve ...