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 pastes all source data with subtotals. This tip demonstrates the workaround that allows you to copy just rows with subtotals.

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

Copy-paste subtotals in Excel for Microsoft 365

Sure, you can select just subtotals (by holding Ctrl and clicking each row with subtotals) and copy-paste them. Unfortunately, it isn't easy 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:

Subtotal button in Excel 365   or   Subtotal button in Excel 365

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

Subtotal dialog box in Excel for Microsoft 365

See more about creating subtotals.

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

Example of subtotals in Excel for Microsoft 365

   2.   Filter your data:

   2.1.   Highlight the row or just position the curson on it.

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

Filter in Excel 365

   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 365

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

Custom AutoFilter in Excel 365

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 365

   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 365

Excel will show all data with selected subtotals:

Selected subtotals in Excel 365

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

Pasted subtotals in Excel 365

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

See also this tip in French: Comment copier des sous-totaux calculés dans Excel.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.