Copying formulas

[BarbM]

I have a column of figures and I want to multiply each row by a percentage. I put the percentage in cell #1 and my figures follow in the cells below. That percentage in cell #1 will change which is why I have it in a separate cell. I set the formula in cell #2 to multiply by cell #1 and it works fine. I then copy cell #2 and paste it to all of the cells below. This is where it goes wrong...

The problem is that cell #3 is now multiplying by cell #2, then cell #4 is multiplying by cell #3....it descends the formula and I don't want that to happen. I want cells 3 through 20 to multiply by cell #1 (without having to change the formula manually in each cell) so that when I change the percentage figure in cell #1 each row will adjust accordingly. This doesn't seem like much since I only have 17 cells, but that's just for the purpose of explaining the problem. I actually have hundreds to do so I thought by putting the percentage in a separate cell it would save me time. I don't want to have to adjust each cell formula when the percentage changes.

I'm sure it's something very simple, but I don't know how to make that happen.

Thank you in advance!

 

[Jonmo1]

Welcome to the board.

Use absolute references to cell #1

=A2*A$1

As dragged down that will change to
=A3*A$1
=A4*A$1
=A5*A$1
etc.

 

[sheetspread]

Hit F4 to put dollar signs around the reference, then it won't change.

 

[MarcelBeug]

Use $ to block the cell row and/or column when dragging up/down or left/right:

A1 will change when dragging in either direction
A$1 will always refer to row 1 when dragging up/down; column will change when dragging left/right
$A1 will Always refer to column A when dragging left/right; row will change when dragging up/down.
$A$1 will Always refer to $A$1, no matter in which direction the formula will be dragged

When editing a formula, you can toggle over the 4 possibilities by using F4 when your cursor is on a cell reference.

 

[BarbM]

Thank you to everyone who replied. My experience with excel is limited but boy the time I could have saved over the years if I had known about absolute references! This worked perfectly!

 

[Jonmo1]

You're welcome.