Excel.Tips.Net ExcelTips (Menu Interface)

Copying Subtotals

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Copying Subtotals.

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:

  1. Add your worksheet subtotals as you normally would.
  2. Collapse the information in the list so that only the subtotals are showing. (Click on the small 2 in the outline levels shown at the top of the gray area at the left of the worksheet.)
  3. Select the range of cells containing the subtotals you want to copy. (Do not select complete rows or columns; just select the range of cells.)
  4. Press F5. Excel displays the Go To dialog box.
  5. Click on Special. Excel displays the Go To Special dialog box. (See Figure 1.)
  6. Figure 1. The Go To Special dialog box.

  7. Make sure the Visible Cells Only option is selected.
  8. Click on OK. Excel selects just the visible information from the range you specified in step 3.
  9. Press Ctrl+C to copy the rows to the Clipboard.
  10. Select the cell where you want to paste the subtotals.
  11. Press Ctrl+V to paste the information.

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.

Related Tips:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

Gianfranco    12 Jan 2017, 06:52
Thanks for yor time, very useful.
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...
again thanks
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
Buddhi Raj    23 Jul 2015, 05:59
Excellent tip
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!

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2017 Sharon Parq Associates, Inc.