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.

5 comments
DRMoon
DRMoon

Just in case anyone else was wondering why the Grand Total displays "5:10" rather than "29:10," it's not because the subtotal feature isn't working properly, it's because the time is shown in time format type 13:30. To measure and display elapsed time greater than 23:59 hours, the better one to use would be time format type 37:30:55. (In Excel 2003: Format Cells-->Number (tab)-->Category:Time, Type: 37:30:55)

dhays
dhays

How would you do this if your spreadsheet is sorted on something other than the date. I have a spreadsheet sorted by a object name, with date information included, I have within manually subtotalled each month's count and then a running total of each month's subtotal to give me a total (in this case per FY) of activity. Since I maually count due to the fact that a June receipt can have a number that could be listed with September's receipts, I cannot just go with counting the number of rows in th earea. I could rig up a count-if function, I suppose, to go by date received, and that way get an automatic total.

COFGFOR
COFGFOR

Selecting "Visible cells only" then pasting them to a new sheet will create huge "UsedRange" on the new sheet: EVERY visible cell from the old sheet gets copied to the new sheet. Excel 2003 used to force you to delete all unused columns (to the right) and rows (downward) then save the workbook to resize the UsedRange. Excel 2007 appears to force you only to delete the rows below the data, then save, to resize the UsedRange. Do this to shrink the size of the workbook file.

Marshwiggle
Marshwiggle

... use (or create if need be) the Custom Format [h]:mm.

ssharkins
ssharkins

Well, let's work this through. First, what column denotes the group you're subtotaling? Is that the object name or the date? Are you trying to subtotal by object name and then date -- for a nested subtotal?

Editor's Picks