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 run into this situation a lot. I have a column of data (A) and I created a formula in B1 that I wish to copy from B2 to B5. This is just a tiny example, because usually I'm dealing with thousands of rows.

What I want to do is to select B1, press ctrl-C to copy and then somehow select the range B2-B5 by using column A as some sort of reference point. IIRC, a long time ago with Excel (Lotus 1-2-3?) that you could do this all with they keyboard using ctrl/arrow/end/home key combinations.

Thanks!

    A   B
1   10  =2*A1
2   20  
3   30  
4   40  
5   50  
share|improve this question

migrated from stackoverflow.com Mar 9 '13 at 20:22

This question came from our site for professional and enthusiast programmers.

up vote 3 down vote accepted

Remnant's answer is a good one. This one allows you to do the same thing to the left or if there are empty columns in between.

As you say, in B1 do Ctrl-C then

Left-Arrow Select A1

Ctrl-Down Arrow to select down to the last filled cell in Column A

Right-Arrow to get to corresponding Column B cell

Ctrl-Shift-Up Arrow to select Column B back to original selection

Ctrl-V

share|improve this answer
    
Use keyboard to select B1. Ctrl-C, left arrow, C-S-DA, RA at this point cell B1 is selected (not the correspnding Col B cell) – WhiskerBiscuit Mar 9 '13 at 16:56
    
Woops, fixed now. It should have just been C-DA to get to end of Column A. – Doug Glancy Mar 9 '13 at 17:00
    
Awesome! Or "Enter" to paste as well. – WhiskerBiscuit Mar 9 '13 at 17:07
    
You know I've read the message "Select Destination and Press Enter ..." about a million times, but until you said that, it never actually registered in my brain :). Glad I could help. – Doug Glancy Mar 9 '13 at 17:11
    
Forget Ctrl-C and Ctrl-V. Do everything else and finish with Ctrl-D for Fill Down. – dkusleika Mar 10 '13 at 0:16

Try Ctrl-C to copy cell B2 then navigate with arrow key to select B3. Hold shift then Page Down to go down quickly in column B to the desired point (fine tune with arrow key). Ctrl-V to paste.

share|improve this answer

Perhaps the easiest and most convenient way is:

  1. Select cell B1
  2. Double-click the little square in the bottom-right corner

This will auto-fill your formula down to cell B5.

Note that if there is a missing row in column A then it will only auto-fill down to the missing row.

share|improve this answer
1  
Double-click is not a key board command. – James Jenkins Mar 9 '13 at 16:51
    
I agree. Just thought I'd offer it up as it seems relevant in this instance and not everybody knows about that shortcut – Remnant Mar 9 '13 at 17:04

Sara almost has it, but there are some modifications

Copy cell B2 with Ctrl+C, while holding the shift key arrow down to cell B5 (selects all the cells). Ctrl+V to paste (fills the cells with the modified formula)

share|improve this answer
    
My method does the same as yours for the example, James. The formula updates correctly if the OP requires 2* the adjacent cell. – Sara44 Mar 9 '13 at 16:50
    
On second look your right it does, I got lost when you copied cell B2 then navigated to it. – James Jenkins Mar 9 '13 at 17:43

You must log in to answer this question.

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