How to copy computed subtotals in Excel
When you copy subtotals and paste them to another sheet, Excel copies all data:
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:
1.2. In the Subtotal dialog box, customize how to calculate subtotals, for example:
1.3. Excel will calculate and add subtotals to your spreadsheet, for example:
2. Filter your data:
2.1. Highlight the first row:
2.2. On the Home tab, in the Editing group, click the Sort & Filter button and then click Filter:
3. Filter subtotals by selecting rows that ends with “Total”:
3.1. Click on the filtering arrow and then click Text Filters -> Ends With...:
3.2. In the Custom AutoFilter dialog box, insert Total in the editing field:
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:
5. With selected subtotals, clear filter by selecting all data in the filter - in the filter select the (Select All) checkbox:
Excel will show all data with selected subtotals:
6. Position the cursor to the sheet where you want to paste subtotals and then click Ctrl+V:
Excel will paste subtotals with all correct amounts and no other data!