Excel: Data subtotal copying problem

Question

I have a huge data set consisting of 900,000 rows and 11 columns in Excel 2007 format. When I use the Data > Subtotals feature I get what I want: 162 rows grouped as I need. The problem is, when I select and copy those 162 rows and paste in a new sheet, Excel pastes all 900,000 rows. I only want to paste 162 rows in a separate sheet. I tried "Paste special" but to no avail. Any clues?

Answer 1

Have a look at this link.

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