Excel has a handy feature that allows you to automatically add subtotals to a data list. (How to create subtotals is covered in other issues of ExcelTips.) You may be wondering, once the subtotals are in place, how you can copy the subtotals to a different worksheet. This is actually rather easy to do, if you follow these steps:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2647) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Copying Subtotals.
Leave your own comment:
Comments for this tip:
Muhammad Umer 26 Oct 2016, 11:09
Thanks a lot ........
I was fighting with my sheet from an hour and your instructions solved my problem...
Jerry 08 Jun 2016, 13:54
Outstanding, thank you
josef 02 May 2016, 09:42
thank's you helped a lot
jeff 05 Apr 2016, 11:52
I can not use the visible cells only function as I need to keep the subtotals on a new work sheet. When I copy the subtotal over to a new sheet it always expands. Can you assist?
Dipen Desai 11 Feb 2016, 01:06
Thanks a lot Sir....
Really Great Help in making our Work Faster/Smoother...
Praveen K 28 Oct 2015, 07:30
It is an awesome tip.
Premdev 05 Oct 2015, 07:24
Really good tip...:)
Venkat 30 Sep 2015, 03:12
Very good tip. Thanks..
Manar Zamrik 29 Sep 2015, 06:16
I am really greatful
kalpesh 02 Sep 2015, 05:04
excellent Tips, Thank you very much
suzanne Watt 27 Aug 2015, 20:06
What if I wanted to paste these cells into an already subtotalled spreadsheet. Can that be done?
NARAYAN 30 Jul 2015, 01:55
REALLY THANKS FOR THE INFORMATION
Buddhi Raj 23 Jul 2015, 05:59
Anabel Balderas 19 Jun 2015, 17:08
Micky Avidan - Thank you!
Demetris R. 19 May 2015, 09:45
If you don't want the date to add you must change your subtotal function to average.
archana 07 May 2015, 02:45
thanks mickey very easy to do ur steps
Michael (Micky) Avidan 17 Apr 2015, 11:48
Here is a much shorter way that replaces steps: 4,5,6,7
1. Select the range of cells containing the subtotals you want to copy.
2. Press a "2 key shortcut": ALT+;
(I Mean: hold the ALT key and press the 'semi-colon'.
3. Select Copy (Ctrl+C) and Paste it wherever needed (Ctrl+V).
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ST Yu 16 Apr 2015, 06:52
I did the above steps but the months that was copied to turn out only odd months meaning Jan, Mar, May, July n ...... The even months was not copied.
Can I know why? I followed the exact steps.
Hemantha 23 Mar 2015, 08:44
Really helpful one. Thanks a lot
Jim 14 Mar 2015, 21:50
Thanks--exactly what I needed!
laxman bhardwaj 23 Jan 2015, 01:57
i like that
many many thanks
Debra Castro 21 Jan 2015, 16:18
Extremely grateful, I had forgotten how to do it.
Shah 05 Jan 2015, 10:29
Thanks - succinct and helpful
Ava 20 Nov 2014, 07:59
Thanks! very useful information!
Trees W 13 Oct 2014, 10:57
Naveed Ahmad 15 Jan 2014, 05:15
What to do if we want to paste the data at the same place while the data is in filtered form.
Nansi Martin 14 Jan 2014, 12:57
Yes, the F5 shortcut key and subsequent instructions did the job! Thanks much!
Lora S 13 Aug 2013, 18:27
Excellent instructions, thank you! The other instructions online would not work with my version of excel, but since you knew the shortcut key (F5) problem solved! Thanks again!