Copy and Paste Sub-Totals in Excel 2007

Who else wants to know the secret to Excel Formulas?

Invest in yourself in 2015 and reduce your frustration with formulas in Excel for just $19.95 - Click here to enroll today

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, link 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!

31 thoughts on “Copy and Paste Sub-Totals in Excel 2007

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

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

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

  4. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *