Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 4 down vote accepted

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

share|improve this answer
Does not work... – jhamu Nov 2 '10 at 12:56
The new link provides an answer which works... – jhamu Nov 2 '10 at 13:08
+1. That's it! Thanks a lot. – Mehper C. Palavuzlar Nov 2 '10 at 13:16
+1 That is good to know. I've always used Word (or a new email) as an intermediary clipboard for that. – Xantec Nov 2 '10 at 13:23
Edit made; link rot prevented. – Mehper C. Palavuzlar Jan 7 '11 at 13:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .