32

Typically, in Excel, one can autofill a column by drag a cell down. However, with a lot of cells to fill up, manually dragging and scrolling the spreadsheet is time consuming.

Is there a much better way to fill up a column with formulas?

Edit: I tried to input the $F$1:$F$20000 at the highlighted place, but the values inside column F is not populated.

alt text

9 Answers 9

41

Instead of clicking and dragging the square in the lower right corner of the cell, you should try instead to double click it. This will autofill it down.

Also, you should get used to using "ctrl" and "shift". When I use excel I go extremely fast because I use lots of keyboard shortcuts. Let me walk you through an example and let you know exactly what I would type. Lets say you have data in Columns A:E with 20000 rows, and want to create a formula for F. Follow these steps:

  • First put your formula in F1.
  • Now hit ctrl+C to copy your formula.
  • Hit left, so E1 is selected.
  • Now hit Ctrl+Down. This will travel all the way down until a blank is hit, in this example its E20000.
  • Now hit right so F20000 is selected.
  • Now hit ctrl+shift+up. Now you will be selecting F1:F20000.
  • Finally either hit ctrl+V or just hit enter to fill the cells.

Once you get used to doing things like this, these 6 keystrokes happen very fast in a fraction of a second.

2
  • Can't you just do ctrl+c, ctrl-shift-down, ctrl-v? Oct 18, 2017 at 8:53
  • @Worse_Username Try it -- you'll end up at the very last row, not the bottom of the data. You can do it, but your filesize will shoot up.
    – anon
    Feb 2, 2018 at 18:10
24
+100

Actually there is a better way

  • Copy your formula Ctrl + C
  • press
  • Ctrl + Shift + selects to the last nonblank cell
  • paste Ctrl + V

4 strokes. Discovered it just now

4
  • As a note for others who find this, you can also do the same thing across rows (such as for headers), and do ctrl+shift+right
    – Shawn
    Oct 31, 2017 at 19:45
  • you can skip step #2. You can paste a formula into the cell from which it came without any negative consequences. but double-click is quicker most of the time.
    – mpag
    Apr 2, 2018 at 22:52
  • For some reason @Shawn's suggestion didn't work for me for one instance of Excel. Restarting Excel fixed that. But I also "discovered" that highlighting the left column of the range, then pressing the End key, then doing Shift+right arrow, then either pasting in the formula or doing a Control+R copies the formulas from the left column rightward.
    – mpag
    Apr 3, 2018 at 17:11
  • @mpag Actually, if you skip step 2 and the formula has been array-entered, you get a "You cannot change part of an array." error.
    – robinCTS
    Jun 11, 2018 at 8:51
3
  • Copy the cell that contains the original formula to the clipboard.
  • Click into the "cell name" box (in the upper left) and enter the range that you want to select, followed by Enter.
  • Paste to the selection.

Does that work?

3
  • no, it doesn't work. See my updated question.
    – Graviton
    Aug 25, 2010 at 3:59
  • Weird, it works in OpenOffice.org, and at least one Excel tutorial I found mentioned it too (but it looked like Excel 2000 or such, so maybe Microsoft removed this feature...).
    – JanC
    Aug 25, 2010 at 15:37
  • BTW: you should probably type F1:F20000 without the "$" signs.
    – JanC
    Aug 25, 2010 at 15:42
1

When selecting a whole column you can just enter F:F

This is particularly useful when adding or doing some operation with a whole range and where you may want to add data to the range.

1
  • note that you can do this in formulae as well (e.g. SUM(F:F)), as long as your destination cell is not within F.
    – mpag
    Apr 2, 2018 at 22:50
0

I had to do this just the other day.

  • Copy the formula with relative referencing, i.e. no $ signs.
  • Select the range you want to fill.
    I was grabbing the bottom cell, then pressing End-Shift-UpArrow.
  • Paste the formula in the selection.
    Excel will change the relative referencing for each cell.
0
  1. ctrl + C to copy the formula you wanted
  2. for 2003 excel click edit-> go to
  3. in go to window put your ranges in; such as A2:A238
  4. then ctrl + V to paste formula to exact cell range you selected.

This way you don't have #DIV/0! in other cells; sometimes #DIV/0! affect other calculations;

If you use count function you will know how many rows of data you have.

0

Select the range where you want the formula, create the formula and hit Ctrl + Enter
it will fill all the selected range with relative reference also

0

Another easier way is to select the formula cells, and double click the dot as shown in the image, it will apply to all the fields below, in the sample shown, it applied to D4:D3602, E4:E3602, F4:F3602. As 3602 was the last row in my sheet. enter image description here

0
  • Enter the formula into the first cell.
  • Select the range of cells.
    • It might be fastest to go to the last cell by entering its coordinate in the Name Box on the top left and pressing Enter, then scrolling to the first cell using the scroll bar, holding Shift and clicking on the first cell.
  • Ribbon menu ⟶ Home tab ⟶ Editing section ⟶ Fill ⟶ Series ⟶ AutoFill ⟶ OK.

You must log in to answer this question.

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