Large pastes

[Jim Doornek]

I am analyzing data in a large spreadsheet - 29000 rows and 125 columns.
In doing so, I frequently add new columns with formulas by adding the formula into row 1, testing to make sure it works, then copying and pasting it to the other 28999 rows. I do this by selecting the cell with the proven formula, and then drag and paste it to the bottom of my spreadsheet. This is very tedious and takes forever. Sometimes I overshoot my last row and need to carefully hang on to the left mouse button and drag the cursor back up to the correct cell.
There must be a better way?

 

[Smitty]

Welcome to the Board!

Are you familiar with working without the mouse?

Let's say you're on row 1, column A and you want to copy that down (even the entire row) to the last row in A. You can highlight your copy area and hit CTRL+C (Copy), then SHIFT+END+Arrow Down (to select the entire used range beneath it)-->CTRL+V (paste).

That will take all of 3-4 seconds vs. trying to autofill the formula with the mouse (noting that I rarely ever use the mouse).

Hope that helps,

 

[Jim Doornek]

Smitty-
Thanks for the advice. It worked just as you described. However, I noticed that the copy/paste was applied to all of the rows in the spreadsheet - even the rows lower than the ones that I have actual data in. So I'm wondering if there is a way to control the paste to limit it only to the rows already containing data.

For example, if my spreadsheet contains data in cells a1:a300, and I want to copy/paste data to cells b1:b:300 only, can I use your suggestion to do so?

Thanks again.
Jim

 

[Smitty]

even the rows lower than the ones that I have actual data in.

Sounds like Excel's catching the used range, in which case it will paste down to what it sees as used, even if you've deleted data.

The only way to get rid of the used range is by deleting the inactive rows (select the complete rows with Shift-->End-->Down, then /EDR (Edit-->Delete-->Rows).

Make sure to delete extra columns next, then save the file. I'll bet you find it to be a lot smaller.

This is just one of those things you play with and catch.

As for the second question, if you specifically select a range, you'll only paste that same range; it won't expand like in my first example.

(OK, I'm pretty much outta' here for the night).

 

[Jim Doornek]

Smitty-
When I do a ctrl/end, the cursor moves to the lower right corner of my actual data. However, when I follow your instuctions using shift/end/arrowdown method, I get the same results. The entire column (all 65,000 some rows) are pasted with the copy.
Maybe I'm doing something wrong?
This just seems like such functionality. I'm surprised that you cannot just type in a "destination range".
Thanks much for your help so far. i really appreciate it.
Jim

 

[Smitty]

If the entire column is being selected, then it generally means that Excel is seeing the entire thing as used, so you need to delete the unused rows.