MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel Copy Subtotals

February 12, 2002 - by

Peter from Cincinnati offers this weeks tip:

The Excel Subtotals command allows you to quickly summarize a data set. Press the #2 Group and Outline button in Excel to show only the subtotal rows. But then - how can you copy only the subtotal rows to a new Excel range?

Use the Goto - Special command to copy just the subtotals to a new area.

Go To Special Dialog

You can use the Data - Subtotals command to automatically subtotal a range of data as shown above left.

After creating subtotals, Excel will show the outline symbols to the left of your worksheet. By clicking the "2" outline symbol, Excel will display only the subtotal lines.

This is really cool, but many people have trouble when they try to copy this information to a new worksheet. It looks like there are just five rows of data, but when you copy and paste elsewhere, you will find that Excel copied the hidden rows as well.

The solution lies in the obscure but powerful GoTo Special functions. Here are the steps:

  • Highlight the range you want to copy.
  • Click on Edit, then Goto.
  • On the right side of the Go To dialog is a button marked "Special". Click "Special" to see the Go To Special dialog box.
  • Click the button for "Visible Cells Only" and click OK. This changes the selection from all 14 rows to just the five visible rows.
  • Now that you have just the 5 visible rows selected, you can do Edit - Copy (or Ctrl + C), click in a new cell in a blank range of spreadsheet, and Edit - Paste (Ctrl + V) to paste just the subtotals.