How to copy computed subtotals in Excel

Excel 365
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 2016

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:

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

See more about creating subtotals.

   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:

Filter 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!

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

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

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more