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:

CopySubs1.gif

Next, we’ll collapse the subtotals using the outline controls on the left so that only the totals rows are shown:

CopySubs2.gif

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:

CopySubs3.gif

That button brings up the Go To Special dialog box. On that dialog, choose Visible cells only. Then just copy and paste normally.

CopySubs4.gif

57 thoughts on “Copy Subtotals Only

  1. 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.

  2. 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…

  3. 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…

  4. Highlight the area you want then use alt + ; as a keyboard shortcut to highlight only the visible cells.

  5. Paul, That is NIFTY, NIFTY, NIFTY too!!!!
    There were TWO easier ways to do what I have been doing the hard way….

  6. Wonderful tip — it became the “Tip Of The Month” with the technical folks of my company for the western US.

  7. Not only did I learn something new, but it made me look really smart to one of the cutest co-workers!!! Thanks!

  8. 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.

  9. 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!!

  10. 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 :-)

  11. If only I’d found this link sooner!!

    What a great tip! Thank You. This will save me loads of time from now on.

  12. 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

  13. 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!

  14. 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.

  15. 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.

  16. 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.

  17. I love this webpage. Thanks to everyone who posted. I just noticed my bosses eyes roll when she saw me do this. POMOTION!!! YAYYYYYYY!!!!!!

  18. 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”.

  19. 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.

  20. 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.

  21. This does not work in EXCEL 2007 on Vista. Cannot paste only the visible cells – it pastes everything. Any solution out there?

  22. 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!

  23. 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

  24. OK.. Here is a much easier way to first select visible cells as in SUB totals or when copying only visible cells where rows and columns are hidden or similar. You just don’t want the ordinary cut and paste command that results in to much in the between after the paste ;)

    1. First filter out what you want to select by hiding rows, colums or filtering them down to a visible you want to copy.
    2. Then select the visible you want to copy by dragging the desired area out with the mouse or use the keyboard shift/&CTRL – arrow select functions.
    3. Now, hold down the ALT key an press semicolon (english/american keyboards). On my keyboard that is Norwegian I have to press ALT + Shift + semicolon. You will now see that the the black arounding disappears and the first upper left cell is white. Move you fingers away from the keyboard :)
    4. Now you can use the CTRL + C command. And you will se the dancing ants just around the wanted selected cells. Great.
    5. Now you can go to you preferred spot and paste the visible cells with CTRL + V

    Thats it…

    The shot version goes like this.

    1. Select the area
    2. ALT + semicolon or SHIFT + ALT + semicolon (depends on your keyboard language layout)
    3. CTRL + C
    4. CTRL + V

    DONE :)

  25. In Office 7 Hi-light cells, ALT/E,G,Click Special, Click Visible Cells only, click OK, make sure cells are still Hi-lighted and click Ctrl/C. The cells will be framed by some strange looking lines. Select your target and hit Ctrl/v.

  26. Thank you all for the tips. I use this to make my subtotals bold all the time now.

    I’m currently using Excel 2003 and would like to know if there’s an easy way to show some of the information from the row above the subtotal as well.

    For example, I’m working with purchase totals where some are single items and others are multiple items in the subtotal. It’s actually the items on each receipt.

    If I just collapse to the subtotals I lose all the information that identifies where the receipt is from, the date, the cardholder’s name, etc. Only one column will have varying information and I could hide that column if necessary.

    Here is an example:

    I’d like to make this –

    LOWES #01764*BRJOHN1.00 9/15$6.98
    LOWES #01764*BRJOHN1.00 9/15$39.98
    LOWES #01764*BRJOHN8.00 9/15$18.72
    LOWES #01764*BRJOHN2.00 9/15$53.96
    LOWES #01764*BRJOHN1.00 9/15$26.98
    $146.62

    become this –

    LOWES #01764*BRJOHN1.00 9/15$146.62

    The only way I know to do it is to copy and paste it onto the subtotal row for each receipt. I don’t have time to do 1000’s of those so I’m stuck printing out many pages that are unnecessary.

    Anyone have any advice for me?

    Thank you.

  27. This was a great tip; We have had to use a long work-around for quite some time.
    This has saved many, many hours of labor.
    Thank you so much!

  28. Finding this tip was nothing short of a life-changing experience. Wish I’d looked it up years ago. Would’ve saved me tons of time and aggravation. A million thanks!


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.