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