Copy Subtotals Only
Copying data that has subtotals applied to it will copy all of the data, even the rows that are hidden. Sometimes you just want to copy the totals, and here’s how you do it. Let’s start with some data that has subtotals applied:
Next, we’ll collapse the subtotals using the outline controls on the left so that only the totals rows are shown:
To copy just these rows and omit the rows that are hidden, we must select only the visisble rows. Excel provides us with that function under Edit>Goto (F5). The Goto dialog has a Special button on the bottom left:
That button brings up the Go To Special dialog box. On that dialog, choose Visible cells only. Then just copy and paste normally.
Wow, thanks for the tip. I can’t tell you the number of times I wanted only the subtotals and had to come up with some nasty way to get them.
I guess you learn something new every day.
That tip is NIFTY, NIFTY, NIFTY……….It made my day! Like getting a new toy!
Glad to see that I wasn’t the only one impressed with this trick. People at work keep asking how I learn all this stuff. I smile and change the subject…
Glad to see that I wasn’t the only one impressed with this trick. People at work keep asking how I learn all this stuff. I smile and change the subject…
Highlight the area you want then use alt + ; as a keyboard shortcut to highlight only the visible cells.
Paul, That is NIFTY, NIFTY, NIFTY too!!!!
There were TWO easier ways to do what I have been doing the hard way….
very useful tip, very useful site
thanks
Thank you, thank you, thank you.
Not even the Excel Help mentions this…
Wonderful Tip. Congratulations.
Thanks so much, saved many hours!!!!!
Thanks so much, saved many hours!!!!!
Wonderful tip — it became the “Tip Of The Month” with the technical folks of my company for the western US.
Not only did I learn something new, but it made me look really smart to one of the cutest co-workers!!! Thanks!
Is it possible to format only the subtotals on a report ie paint them all at once with this or a similar method??
We are using access 97 so there may be some limitations.
shortcut Alt + :
Very useful indeed
WHew!!! Your tip was a Godsend!!!
Duuude!!!!!
thANK you sooooooooooooooooooooooo much!!!
this is just ammmmmmmmazing !! u can’t imagine how much you’v saved me my time and depression in monotonously clicking on each cell usint Ctrl.
GOD BLESS YOU man !!!
thanks again!!
Wow! Thank you so much! The internet is awesome!
You are a lifesaver! Thanks.
Wow thanks…I was trying to Highlight sub-totals so they are easy to spot when I print them out. Works great with this method!!!!!! Thanks you saved me a lot of time
Thanks man!
If only I’d found this link sooner!!
What a great tip! Thank You. This will save me loads of time from now on.
I wasted several hours with excel help. Today, I was determined to find another way. Google helped me locate this page and excel info. I knocked in out in a minute. Thanks for saving me additional hrs. Now I can go back to watching the game. This is awedsome
I can’t believe I spent so much time with Excel help. I should have looked here first. Thank you so much for fixing a huge problem for me! This is amazing!
Thanks for the tip. Could not have done it on my own.
Awesome tip. I will appear much smarter now that I have found your website. Thanks.
Many thanks, did the trick, and first link in the google search.
You are awesome…. Was doing it by hand the last few days because I couldn’t find anything in the Excel help. I decided I was not going to do one more with out do a google search. Thanks for the tip.
Well done. Great Tip
Thank you so much! You would thinkn this stuff would be included in teh help section of Excel. Once you subtotal, you can’t do anything with it, especially sort and manipulate.
Wow!! You rule!! That tip saved me at least 30 minutes a week!!
This is an excellent tip - I was struggling on how to get the subtotal information extracted from 55000 rows, and managed to copy out the 3000 subtotals - great stuff.
Excellent and very useful tip. Thanks a lot.
I love this webpage. Thanks to everyone who posted. I just noticed my bosses eyes roll when she saw me do this. POMOTION!!! YAYYYYYYY!!!!!!
PROMOTION EVEN!
Thanks a lot for the excellent tip.
An EXTREMELY helpful tip.
Thank you very much.
Thanks Pal, this has really helped me. Please help me in this; after copy subtotals I am not able to display multiple items with auto filter.
I am realy grateful for your support, Nice way to share the information.Thanks a lot.
Interesting tip, I stumbled on it looking for different info.
FYI, there’s a little known button that accomplishes this same task. It’s not on any standard excel toolbar but can be found by going in and customizing your “edit” toolbar. It’s called “select visible cells” and when you highlight the visible cells of your collapsed subtotal then click on it it automatically chooses only the visible cells in your highlighted range and you can copy/paste/format/etc. them from there. It’s been so handy to me that I refer to it as the “magic button”.
I can’t believe after all this time, this is all I had to do. I feel like I needed this tip every day. Thank you so much for posting this! Very easy.
I had copied and pasted visible cells only for years in 2003.
But my 2007 keeps pasting the collapsed rows, but not the hidden rows.
I can not get copied visible cells only to paste without pasting the collapsed rows.
This does not work in EXCEL 2007 on Vista. Cannot paste only the visible cells - it pastes everything. Any solution out there?
Retract previous submission - it is now working perfectly - helps to know what you are doing?
CXF, would you please share your solution? I had the same trouble you did, but I don’t see the menu option to do it… Thanks!
The trick is to make sure you have selected only the sub-total cells first. Click on the 2 box on the extreme left hand side of the excel sheet after you have done the sub-totalling. This will show only the sub-totals. Select all the cells and then do the following from the Excel help
On the Home tab, in the Editing group, click Find & Select, and then click Go To.
In the Go To dialog box, click Special.
Under Select, click Visible cells only, and then click OK.
On the Home tab, in the Clipboard group, click Copy .
Keyboard shortcut You can also press CTRL+C.
Select the upper-left cell of the paste area.
Tip To move or copy a selection to a different worksheet or workbook, click another worksheet tab or switch to another workbook, and then select the upper-left cell of the paste area.
On the Home tab, in the Clipboard group, click Paste .
Keyboard shortcut You can also press CTRL+V.
Notes
Excel pastes the copied data into consecutive rows or columns. If the paste area contains hidden rows or columns, you might need to unhide the paste area to see all of the copied cells.
If you click the arrow below Paste , you can choose from several paste op
The selected cells should shimmer when selected (outlined with moving tilde’s). If this happens it is working for you.
R’s
CXF
Awesome, thank you so much!!!
WOOOOOOOOOOOOOOOOOOOOOOW!:))))))))))))))