The Computer Oracle

Fill formulas until end of adjacent table

--------------------------------------------------
Rise to the top 3% as a developer or hire one of them at Toptal: https://topt.al/25cXVn
--------------------------------------------------

Music by Eric Matyas
https://www.soundimage.org
Track title: Popsicle Puzzles

--

Chapters
00:00 Fill Formulas Until End Of Adjacent Table
00:58 Answer 1 Score 5
01:26 Accepted Answer Score 18
01:46 Answer 3 Score 0
03:05 Answer 4 Score 6
03:27 Thank you

--

Full question
https://superuser.com/questions/859501/f...

--

Content licensed under CC BY-SA
https://meta.stackexchange.com/help/lice...

--

Tags
#microsoftexcel #keyboardshortcuts #microsoftexcel2013 #selection

#avk47


ACCEPTED ANSWER

Score 18


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.

        double-click Fill Handle

After double-click:

        double-click Fill Handle



ANSWER 2

Score 6


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

Score 5


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

Score 0


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.