Same formula in different rows

[hfriedberg]

How can I apply the same formula to each row of my spreadsheet, varying
only the row number in the formula, without handing to enter every one
by hand. In earlier versions of Excel all I had to do with enter
return. Thanks in advance.

 

[Geoff Lilley]

Your best bet would be to put the formula in the first row of the
sheet. Then, use this trick (will work if every row to the LEFT of the
formula column has data): Put your cursor in the lower-right hand
corner of the cell, so that your cursor changes to a black plus sign.
Double-click. Then, the formula will get populated to all of the rows
that have data, as measured by the column directly to the left.

HTH.
Cheers,
Geoff Lilley
Microsoft Office Master Instructor (2000/XP)
Apple Certified HelpDesk Specialist (OS X 10.4)

 

[hfriedberg]

Geoff,

Thanks for the help. Unfortunately I can't get the white plus sign to
change to black, even if I enter data in each cell. Any suggestions?

Regards,

Harris

 

[Harvey Waxman]

Select a cell, edit the formula by putting a "$" in front of the Column letter
(cmd-T with the cursor on the cell reference and the $ will cycle so it's
before both column and row, row only, column only, none) the column will remain
constant when you copy the formula. =$C3 for example will retain column C but
the rows will change as it is copied down.

 

[Mike Middleton]

Harris -

... I can't get the white plus sign to change to black, even if I enter
data in each cell. Any suggestions? <

Excel | Preferences | Edit | Allow cell drag and drop

and it may change from a white plus sign to some other symbol that is not a
plus sign.

- Mike
http://www.mikemiddleton.com

 

[CyberTaz]

Hi Harris -

A few alternatives:

Select the cells you want the formulas in, type the formula in the active
(first) cell, finish with Control+D (to Fill Down)... or use Control+R to
Fill Right in a row.

If the formula is already in the first cell;

Select it plus the additional cells & use the same keystroke as above,

Or

Copy the cell containing the formula, then select the additional cells &
paste.

HTH |:>)
Bob Jones
[MVP] Office:Mac