When creating a formula in Excel or most other spreadsheet programs we sometimes need one of the values to be dynamic (e.g. we can change it in one place and effect everywhere it’s referenced) but stay constant when we replicate that formula across rows or columns.

## To keep a constant value in Excel use the following steps:

- Create a cell with the constant value you want to reference
- Create a formula in a cell that performs your calculation
- In the formula where you reference the value you created in step 1, add a
**“$”**before the letter (representing the column) and number (representing the row).

## Example of Keeping A Constant Value in Excel

We have a cell, let’s say cell A1 which just contains the text “Interest Rate” which is simply a label so we know what we’re dealing with.

Next, we have a cell B1 which contains the actual interest rate, we’ll represent our percentage as a decimal so 10% becomes .1.

Now below that we have three columns:

- Principal Amount
- Simple Interest
- Total (Principal Amount + Simple Interest)

in A3 we put $1000, in A4 we put $2000 then highlight both cells and grab the bottom right corner and drag down for 8 more rows so you now have values $1000 – $10,000.

Next in B3 we enter a formula to calculate interest by multiplying our first value ($1000 in cell A3) by the interest rate (.1 or 10% in B1).

=(A3*B1)

The cell should show $100.

Matthew McGarity says

This stuff is slick — I could spend *all* day talking about cool Excel stuff. Keep it up!

Tom says

Didn’t even notice you were on a Mac… I believe the Mac Shortcut for F4 is Cmd + T but I don’t do Excel on a Mac

Daniel says

You could also name the cell.

In your exemple, if you change the name of the cell from B1 to INTEREST

Your formula would then be =(A3*INTEREST) wich has the same effect.

Scott Ellis says

Daniel,

Excellent tip! Thank you!

Tom says

No mention of the F4 key to toggle the $ sign, what’s up with that?

Scott Ellis says

Tom, I’m guessing that’s PC only. Doesn’t seem to work on my Mac unless it’s a different key stroke. Good suggestion though.