How to quickly paste almost same formula to set rows?

[cardingtr]

Let's say I have a long formula I don't want to rewrite on each cell.
I want to paste it to other cells but also the formula should change to
reflect its new location.

Heres the formula:
=(B3="mild")*1+(B3="moderate")*2+(B3="mod-severe")*3+(B3="severe")*4+(B3="intolerable")*5

The above formula is in C3 referring to B3.

I want to paste it down starting in C4 but how will the fomula
automatically change from B3 to B4, B5, B6.....without typing it
manually?

Thanks.

 

[Don Guillett]

The easy way is to grab the fill handle (lower right corner of source cell)
and drag down.

 

[Ken Johnson]

Hi,
another way is single click on C3, then while holding down the Shift
key single click on the lowest column C cell that you want to have your
formula, then press Ctrl + the d key to fill down your formula.
If Don's way did not work then the "Allow cell drag and drop" option is
probably unchecked. Tools>Options>Edit then click on "Allow cell drag
and drop" will turn it back on.
Another shortcut is to double click the fill handle. Excel detects the
values in column B and fills your formula down as far as the column B
values.

Ken Johnson

 

[David McRitchie]

Or you can select the cells in the address box C3:C200
before using Ctrl+D with the formula in C3.


More on use of fill handle and similar mouse and keyboard shortcuts.
http://www.mvps.org/dmcritchie/excel/fillhand.htm