Help, tips, tricks and tutorials for Microsoft Office 2007 - The New Paperclip

Copy and Paste Sub-Totals in Excel 2007

11th February
2009
written by The New Paperclip



So… you have spent hours collating your data in Excel 2007.  You have finally got the information where you want it – with sub-totals and all.  You collapse the outline to only show the sub-totals, because that is all you are interested in.  But when you try to copy those subtotals into another spreadsheet, it copies EVERY row, not just the sub-totals.

So how on Earth are you supposed to copy all those sub-totals in Excel 2007 without copying all the ‘hidden’ rows as well?

The problem is that when you select rows using the sub-total outline view, it is selecting every row, not just those sub-totals.  The way to solve this is to only select the visible cells – which means we have to use a feature of Excel called “Go To Special”.  Here is how to do it:

1) Select all your sub-totals that you want to copy

2) On the “Home” tab of the Ribbon, look for the “Editing” group – usually it is the last one, all the way over at the right hand side of the Ribbon.

3) Click on “Find & Select”

4) On the little menu that appears, click “Go To Special”

5) In the box that appears, make sure you select “Visible Cells Only” in the right hand column

6) Click OK

7) Copy using the menu (or ctrl+c) and then paste your sub-totals in your new spreadsheet

You can stop pulling your hair out now!

‘till next time!
TNP ;)



Excel 2007, Level 400 - Expert

28 Comments

  1. Linda
    14/04/2010

    Thank you very much!!!

  2. Abu Bakir
    22/04/2010

    thanks buddy, it really helped me alot

  3. Aaron
    23/04/2010

    Thanks – this was very helpful

  4. Sarah
    29/04/2010

    THANKS! It took me a few tries, but I got it & it’s EXACTLY what I wanted to do!!!

  5. beechBoy
    24/05/2010

    Brilliant – thank you

  6. Terrance
    08/07/2010

    Lifesaver !!

  7. Dom
    19/08/2010

    Thank you. This was very helpful.

  8. Rich
    18/09/2010

    Thank you!

  9. M A Martin
    09/10/2010

    A very fine article, saves the day for many of us. Thank you.

  10. MJD
    11/11/2010

    Great help, thank you!

  11. Bob
    01/04/2011

    Thanks – saved me a lot of time and frustration.

  12. lassang01
    03/04/2011

    Thank you, was looking fir this trick a long time ago

  13. 1TrickPony
    06/05/2011

    Shortcut: Alt ;

    -Highlight the cells to be copied. Hold down the Alt key and press the semicolon (;) key. Then Paste. Only the visible rows are copied.

  14. sujith nayak
    30/06/2011

    super :-)

  15. Jeanne Androsiglio
    24/08/2011

    Good to know this: You cannot just highlight all the subtotals to copy them; you have hold ‘control’ down and select each subtotal.

  16. Carolin
    25/08/2011

    Brilliant! Always thought this was imposs and had a few good rages at Microsoft over the years because of it. Thank you!

  17. Casey
    25/08/2011

    This post has saved me a hundred times in the past year!

    Here’s an added question/challenge:

    I would like the pasted values to be referenced. What I mean is that I would like the pasted subtotal cell to change if the subtotal cell on the previous sheet changes.

    Normally, I use the ‘paste special’ function so that I don’t get the funky ‘error’ message on reference values as a result of pasting the formula over. But then the new sheet with pasted subtotals has to be manually changed every time I change the subtotals… does that make sense?

    I could, of course, do this by hand, but it would take a lot of time. Suggestions?

  18. david ducharme
    27/10/2011

    How do I disable the copy/paste feature in Excel?

  19. Chris G.
    08/12/2011

    Thank you Soooooooo much helped heaps

  20. AB
    18/01/2012

    Thank you. You’re the best!!!!!!!

  21. Kate
    24/01/2012

    Fabulous. Thanks

  22. Dipesh
    25/01/2012

    Thanks Dude
    that really helped a lot

    :) :):)

  23. nore
    14/02/2012

    any idea of how to paste link the subtotal selection? the row values will be screwed up

  24. Jay Jackson
    16/05/2012

    NICE! I am sure that reading the manual would have lead me down this path but this was much easier. I will share this with our whole audit team.

  25. Tom J
    06/06/2012

    Thanks – it’s great to be able these kind of solutions. fantastic!

  26. Rakesh Bagri
    04/09/2012

    Thank you, Sir/ Ma’am…….

  27. Rishabh Jain
    05/10/2012

    Thanks buddy

  28. hussein
    04/02/2013

    thanks a lot . i am really appriciated

Leave a Reply

-->