Excel 2013: The Missing Manual

Chapter 15. Grouping and Outlining Data

As you saw in the previous chapter, Excel’s tables are great tools for managing big collections of data made up of long, uniform columns (like lists of customers or products). But tables don’t work so well if you need to show more complex information, especially if that information is split into separate groups, each with its own subtotals.

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 that 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 into a separate group of cells, and then tying everything together with another batch of cells that sums up your sales across all regions.

Excel’s grouping and outlining features are perfect for dealing with multilayered information like this. 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 remarkably easy to implement, but they rank as one of Excel’s best-kept productivity secrets.

Basic Data Grouping

When you want to simplify your worksheets, you first have to learn how to group data. Grouping data lets you tie related columns or rows into a single unit. Once you put columns or rows into a group, you can collapse the group, temporarily hiding it and leaving ...