Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Typically, in Excel, one can autofill a column by drag a cell down. However, since a lot of 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

share|improve this question

5 Answers 5

up vote 9 down vote accepted

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.

share|improve this answer
    
i use the 6-keystroke way all the time :) –  wilson Aug 25 '10 at 4:27
  • 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?

share|improve this answer
    
no, it doesn't work. See my updated question. –  Graviton Aug 25 '10 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 '10 at 15:37
    
BTW: you should probably type F1:F20000 without the "$" signs. –  JanC Aug 25 '10 at 15:42

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.
share|improve this answer

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

share|improve this answer
  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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.