Fill formulas until end of adjacent table

Question

I'm trying to fill a column with a simple formula using cells from an adjacent table. I know how to do that by dragging the mouse, but it's a very long column, so I'd like to use AutoFill.

However, if I press Ctrl-Shift-Down, all the rows until line 1048576 are selected. How can I make the selection stop at the last value in the adjacent column (in this case, D196)? I already tried this suggestion (select E2, Shift-Left, Ctrl-Shift-Down, Shift-Right), but at least in Excel 2013 this method doesn't stop at the correct location either.

If I start my selection in column D and press Ctrl-Shift-Down, then the selection stops at the correct row, and I can use Shift-Right to expand the selection to the desired column - but I can't find a way to move the selection to the right instead.

What else could I do?

Answer 1

If you double-click the Fill Handle, it should copy the formula down to the extent of the data in the neighboring columns. The Fill Handle is the tiny black square in the lower-right of the selection border.

        

After double-click:

        

Answer 2

These steps work for me:

• Select the cell containing the formula and press CTRL + SHIFT + DOWN to select the rest of the column ( Or CTRL + SHIFT + END to select the last row where there is data )

• Fill down by pressing CTRL + D.

• Use CTRL + UP to return up.

Answer 3

With the help of this answer, I managed to find a solution:

• Click on the top value in the adjacent column (here: D2)
• Press Ctrl-Shift-Down. Now the adjacent column is selected until the end of the column
• Press Shift-Right to expand the selection
• Press Shift-Tab to move the focus into the next column (E)
• Press Shift-Right to shrink the selection
• Choose Edit-AutoFill to fill the selection with the appropriate formula.

Answer 4

When working with tables of many rows I adopt some tricks.

• add, at table bottom, a row completely filled: this will the "stop row".
• use keyboard: CTRL+arrows let you travel to next border from empty/filled cell ever stopping in a filled cell.
• be sure to have a completely filled column: the "travel col". Here you can move fast from top to bottom of table. If, because of your specific data, doesn't exists make one, only one char is enough and it's not a big waste.
• learn to use the Paste special command.
• sometimes is also useful the use of CTRL-END: go to the last used cell (bottom right corner).

The right sequence to copy a formula (or simply a data) in the entire row is (assuming that you start from the first row):

A) without a stop row

• copy the formula
• go to the "travel col"
• press CTRL-DOWN
• return to your target column
• press SHIFT-CTRL-UP, this will select the require column region
• paste

B) with a stop row

• copy the formula
• press SHIFT-CTRL-DOWN
• paste

Remember that stop row and travel column can be deleted after you have done the editing.