2

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  

4 Answers 4

3

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

6
  • 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, 2013 at 16:56
  • Woops, fixed now. It should have just been C-DA to get to end of Column A. Mar 9, 2013 at 17:00
  • Awesome! Or "Enter" to paste as well.
    – WhiskerBiscuit
    Mar 9, 2013 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. Mar 9, 2013 at 17:11
  • Forget Ctrl-C and Ctrl-V. Do everything else and finish with Ctrl-D for Fill Down.
    – dkusleika
    Mar 10, 2013 at 0:16
1

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.

0
0

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.

2
  • 1
    Double-click is not a key board command.
    – James Jenkins
    Mar 9, 2013 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, 2013 at 17:04
0

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)

2
  • 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, 2013 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, 2013 at 17:43

You must log in to answer this question.

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