Fill formulas until end of adjacent table
Solution 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:
Solution 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.
Solution 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.
Solution 4
once the number or formula is in the cell, use your mouse to hover over bottom right corner of the cell until the cursor turns to a small + sign and then double left click on it and all following cells have the number or the formula in it.
Related videos on Youtube
03 : 36
15 : 11
10 : 18
05 : 45
09 : 20
03 : 39
Tim Pietzcker
Python aficionado (both Monty's and Guido's versions) Regex hobbyist Avid musician (piano, drums and percussion, currently playing with the Symphony Orchestra and the Jazz Band of the University of Applied Sciences in Ulm) My professional life so far: Studied medicine in Freiburg (Germany), Zürich (Switzerland) and Seattle, WA Worked as a doctor in Internal and Intensive Care Medicine, then switched to Microbiology, Virology and Epidemiology Always loved chasing bugs (microbial and logical) Back to school a few years ago for an MBA in Hospital Management Since 2010: Working as a Professor of Medicine and Health Technologies at the University of Applied Sciences in Ulm, teaching in the faculty of Computer Science (yeah, sounds weird, I know) If you're interested in a (German) BSc program in Health Information Management, where you'll learn about the intricate connections between IT, management, and medicine, check out our homepage! Funny StackOverflow achievements: Winner of the gold Java badge despite never having written a single program in this language. (Being a regex one-trick pony pays off in unexpected ways :)) Winner of the Populist badge for an answer that outscored another answer by myself Sole owner (at least from May 2013 to November 2016) of the regex-negation badge
Updated on September 18, 2022Comments
-
Tim Pietzcker almost 2 years
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?
-
Admin over 9 yearsThis works well for small regions. When you start to cross the screen keyboard is better then mouse. -
Tim Pietzcker about 5 yearsThanks, this worked for me as well (after I found out that the correct key combination in a German version of Excel is Ctrl+U).