Excel 2007 : Copying Formulas

**Cutiesusiederkins** · 12-20-2011, 10:53 PM

Hello!

I'm new to the forum, and really to Excel, so I need a little help. I'm using Excel 2007.

I'm trying to create a spreadsheet that totals the win/lose/draw records for a number of teams. After much experimentation, I've figured out the formulas to get the totals so that each team had a seperate row. So in column A I have the name, column B I have the win percentage, column C has wins, D has loses, E has draws and F has total games played, so B is C divided by F. I have those formulas.

However, since there are so many teams, I'm wondering if there is an easy way to copy the formulas for each of those cells into the cells below them, but get it to automatically change the row I am refering to. For example, my cell B2 has the formula "=C2/F2", thus giving me the total number of wins divided by the total number of games. Is there a way I can copy that several times, but have the formula change the row for me? So I could copy it to row three and have it automatically know it needs to be "=C3/F3", instead of giving me the same result as "+C2/F2?"

I just don't want to have to go into each one individually and change a number several hundred times.

Thanks!

Susie

**mardyl** · 12-20-2011, 11:16 PM

Hi,
try: right click on cell B2, from drop down menu choose "copy".
using mouse select cells you want to,copy formulas to,
right click on selected field
from drop down menu choose " paste special"
from next menu choose formulas.
formulas should have been coppied to destination cells and row numbers adjusted accordingly
regards

**Cutiesusiederkins** · 12-20-2011, 11:29 PM

Thank you so much! I never would have found that! That saved me a ton of time. Thanks again!

**mardyl** · 12-20-2011, 11:43 PM

I am glad I was able to help,
if you found my advice useful please click on small asterix in the bottom left corner of my post
regards

**abousetta** · 12-20-2011, 11:53 PM

Hi Susie,

May I suggest a simpler method: copy the cell you want (either right click--> copy or Ctrl + C) the go to the place you want the formula to be placed and paste (either right click --> paste or Ctrl + V). You formulas will update automatically as long as they are not bound to the row or column using the dollar sign. Therefore "=C2/F2" will automatically change to "=C4/F4" if you paste it in row 4, etc. Now if you use "=$C$2/$F$2" then the references will always refer to C2 and F2 regardless of where you paste the formula. These are called constant references as opposed to relative references...

Now if the rows are right below each other they you can drag the cell contants (left click on the lower right edge of the cell that contains the formula once the arrow turns into a plus sign) and the formula will be copied and updated accordingly.

Hope this helps.

Good luck.

abousetta

**mardyl** · 12-21-2011, 12:11 AM

I agree that is faster, but it copies entire cell, including format.
so if you have cell b2 yellow, cell b3 green, cell b4 red - after dragging you will have all cells yellow
brgds

**abousetta** · 12-21-2011, 03:04 AM

Hi @mardyl,

Here's the beauty of it all... once you finish dragging the cell cell contents over a range and let go Excel realizes that maybe you don't want to copy everything (e.g. no formatting) and so an autofill options tag appears allowing you to only: copy cells, fill formatting only, fill without formatting.

abousetta