Edward asks a question about extending formulas with the keyboard. By extending formulas, I’m referring to copying a formula down a column as far as there is data in another column. Here’s how I do it:
- First, enter the formula and copy it
- Select A2
- Hold down Shift, and press End, then press the Down Arrow
- Hold down Shift and press the Right Arrow
- Press the Tab key to make the ActiveCell B2
- Hold down Shift and press the Right Arrow
- Hold down Control and press V
 |
 |
 |
 |
 |
 |
 |
Edward thinks there’s an easier way, but can’t quite remember it. It seems that if the ActiveCell is in Column B when you press Shift+End+Down, that you go all the way to the last row of the worksheet. I’ve always done it this way, where column A is active. If you know of a simpler way, post is it as a comment.
If you want to use the mouse, you can double click on the fill handle to get the same results. The mouse sure is easier for this operation.
After entering your formula in B1, simply “right doubleclick”. The formula will copy to the end of data in Col. A. Regards, John
ooops…”left doubleclick” the handle.
1. Enter formula and copy it
2. Press the Left arrow (to go to A1), then Control + Down to go to the last cell
3. Press the Right arrow
4. Press Control + Shift + Up
5. Press Control + V
that’s the way I do it.
Years ago, on the Macintosh version of Excel, there was a way to shift the active selection to the left or right by using the keyboard. Does anyone know if this can still be done?
(i.e., the selection is one column by 5 rows. Pressing Cmd-Tab would move the selection over one column, but the selection would still be one column by 5 rows.)
I always found it useful, but I have not seen it in years…
A. Enter formula in B1 in example
B. then follow 2,3,4 of Juan’s Comment
2. Press the Left arrow (to go to A1), then Control + Down to go to the last cell
3. Press the Right arrow
4. Press Control + Shift + Up
C. Then Press Control + D for fill down
1. Select B1. key and arrow down until you arrive at B7 (page down with larger selections)..
2. Hold down the
3. Press F2.
4. Press
I’m a big fan of for filling in cells.
Oops. Let’s try that again.
1. Select B1.
2. Hold down the Shift key and arrow down until you arrive at B7 (page down with larger selections).
3. Press F2.
4. Press Ctrl + Enter.
I’m a big fan of Ctrl + Enter for filling in cells.
For years I have been doing this the same way Juan Pablo does, but I was fiddling and found this:
1. In B1, Ctrl+C to copy.
2. Ctrl+Shift+End to select to end of column.
3. Ctrl+V to paste.
Neat! I am a keyboard-oriented user, so any new shortcuts make me happy!
Select A1
Ctrl+Shift+Down to select the data
Alt+Right arrow to ‘hop’ the selection right, (mapped to a macro that executes Selection.Offset(0,1).Select)
Ctrl + D
You win Dianne. That’s nice.
Dianne, Dick,
I find this really only works well on a ‘clean’ worksheet. Ctrl + Shift + end will select to the end of the used range – which may cause problems if there is (or was) anything below or right of the data you’re filling.
With ‘Transition navigation keys’ turned on (in Tools > Options > Transition): select B1, hold down the shift key and press Left, End, Down, Right, then Ctrl+D to fill down the formula.
Charlie,
Thanks for the info. I knew it was too good to be true!
by the way, I was trying out Dianne’s tip and discovered that Ctrl+Alt+Tab adds left-indentation to a cell.
Minimizing the number of different vulcan neckgrips you need to execute and assuming the formula you want is already in B1 with a column of data starting in A1, copy the formula (Cntrl-C), then hold down shift and execute this dance:
Left, Ctrl-down, right (note all these have a common hold on shift)
You have selected the adjacent row. You may now paste. I find this is pretty easy to groove.
Alchemyst – can you check this again with the example Dick first presented? If column B is empty, the select drops all the way to the 65536 because the activecell in the selection is still B1 and thus Excel will look in column B for the start/end of a data block.