Paste Only Subtotals in Excel

A useful technique

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.

Subtotaled listFirst 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.

Visible Cells 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.

Subtotals 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.

Go To Special

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.
Visible Only QAT





Please note: I reserve the right to delete comments that are offensive or off-topic.