How to copy computed subtotals in Excel

Excel 2016
When you compute subtotals in your spreadsheet, it can be necessary to select only subtotals and copy them to another sheet for future processing. If you select subtotals and copy them using copy-paste (Ctrl+C and Ctrl+V), Excel paste all source data with subtotals. This tip demonstrates the workaround that allows you copy just rows with subtotals.

When you copy subtotals and paste them to another sheet, Excel copies all data:

Copy-paste subtotals in Excel 2016

Sure, you can select just subtotals (by holding Ctrl and clicking each row with subtotals) and copy-paste them. Unfortunately, it is difficult to select all subtotals if you have a lot of data.

To copy and paste all subtotals, do the following:

   1.   Create subtotals for your data:

   1.1.   On the Data tab, in the Outline group, click the Subtotal button:

Outline group in Excel 2016   or   Big Outline group in Excel 2016

   1.2.   In the Subtotal dialog box, customize how to calculate subtotals, for example:

Subtotal dialog box in Excel 2016

   1.3.   Excel will calculate and add subtotals to your spreadsheet, for example:

Example of subtotals in Excel 2016

   2.   Filter your data:

   2.1.   Highlight the first row:

Selected first line in Excel 2016

   2.2.   On the Home tab, in the Editing group, click the Sort & Filter button and then click Filter:

Sort & Filter group in Excel 2016

   3.   Filter subtotals by selecting rows that ends with “Total”:

   3.1.   Click on the filtering arrow and then click Text Filters -> Ends With...:

Text Filters in Excel 2016

   3.2.   In the Custom AutoFilter dialog box, insert Total in the editing field:

Custom AutoFilter in Excel 2016

Note: Some data except subtotals can be filtered too, just collapse unnecessary data (if you have strings that ends with Total). Just hide them.

   4.   Select subtotals and copy them to clipboard by clicking Ctrl+C. The total amounts show zeros, but don’t worry:

Selected subtotals with zeros in Excel 2016

   5.   With selected subtotals, clear filter by selecting all data in the filter - in the filter select the (Select All) checkbox:

(Select All) filter in Excel 2016

Excel will show all data with selected subtotals:

Selected subtotals in Excel 2016

   6.   Position the cursor to the sheet where you want to paste subtotals and then click Ctrl+V:

Pasted subtotals in Excel 2016

Excel will paste subtotals with all correct amounts and no other data!

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.