Excel’s SUBTOTAL function is my function of choice for all my subtotaling requirements. See my earlier post here on its features.

Excel’s automated subtotal feature can do most of the work for you and leave you with a nicely summarised list.

What if you want to copy and paste only the subtotals without all the detail?

To do that you need to use the Visible Cells Only option before you copy.

The image below has a subtotaled and summarised list. Grouping was used to hide the detail rows.

First select the range A1:B22. Then hold the Alt key and press the ; (semi-colon) key this is the shortcut for selecting Visible Cells Only. The image below shows the result. You can see the different areas selected.

You can press Ctr + c and Ctrl + v to copy and paste. The image below has the result.

Note: only the values are pasted.

Visible Cells Only can also be accessed via the Go To Special dialog. Press the F5 function key and click the Special button to see the dialog below.

There is also a Quick Access Toolbar icon you can use. See image below for the Options dialog. Press Alt f t q in sequence to see this dialog. Click the Add button to include the icon on the Quick Access Toolbar. Click OK to finish.