Software

Quickly isolate summary values in an Excel sheet

You can use Excel's Subtotals feature to summary data, but how to you separate the summary data from the detail data?

Reducing a sheet of detail records to just a few lines of summary information doesn't sound all that difficult. There's definitely more than one way to do so. Perhaps the easiest way is to use Excel's Subtotals feature to create the summary rows. This feature automatically calculates subtotal and grand total values in a list. For instance, the simple sheet below tracks time. Some of the time periods belong to the same day (same 24-hour period).

Using Subtotals, you can quickly subtotal the total time for each full day, as follows:

  1. Select the data, including the headings (A1:D8).
  2. From the Data menu, choose Subtotals. Excel does a good job of guessing your needs. In this case, Excel's default settings will sum the values in the Elapsed Time column, but will enter a new subtotal every time the value in Date changes, which is exactly what you want. (So, be sure to sort your original data if necessary.)
  3. Click OK without changing any of the default settings. As you can see below, Excel adds a subtotal row for each unique date and a grand total for all of the dates at the bottom of the sheet.

So far, so good—you have five rows of summary information, but you still have all the detail records. When you add subtotals to a list, Excel outlines the list. The trick is to pull the summary rows out of the sheet. At the top of the outline pane, you can see three numbers, 1, 2, and 3. Click 1 to collapse the outline to just one row, the Grand Total row. Click 2 to collapse the entire sheet to just the Subtotal and Grand Total rows. Clicking 3 will display all of the records. You can use this behavior to isolate the summary row, as follows:

  1. Click 2 to display just the summary rows, A1:D13, although you can only see just a few rows.
  2. Press [F5] or choose Go To from the Edit menu. Click the Special button at the bottom of the resulting dialog box. Then, select Visible Cells Only, and press OK. Or, simply press [Alt]+; (semi-colon character).
  3. Press [Ctrl]+C or click Copy on the Standard toolbar. If you just copy the summary rows, skipping step 2, Excel will copy the detail data too.
  4. Move to another sheet and press [Ctrl]+V or click Paste on the Standard toolbar. Excel pastes just the visible values.

This isn't the only way to isolate summary values—how would you do it?

TechRepublic's Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox