MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Copy Subtotals


February 12, 2002 - by Bill Jelen

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.

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.