How to Use a Fixed Reference in Excel

Use the dollar sign in your formula to create fixed references.

Excel's most powerful feature may be the ability it gives you to create formulas, from the simplest to the most complex. When you create a formula, by default, Excel assumes the cell references in the formula are relative. This is so that when you copy a formula to another cell, all the references adjust relative to the original. In some cases, you may want to create a formula where the reference is fixed, rather than relative. Oftentimes, you need a fixed reference if you need to multiply a column of numbers by a single number in a given cell.

Instructions

1. Open the Excel spreadsheet you want to enter formulas into.

2. Click an empty cell where you want to build your formula.

3. Enter an equal sign followed by the formula you wish to create. For example:

   =A1*B1

   The value in cell A1 will be multiplied by the value in B1. Note that this will be a relative reference at this point; if you copy this formula to other cells, the cell numbers will change accordingly.

4. Click the cell where you created the formula, then click the formula bar. Add the "$" character in front of the column letter, row number, or both, depending on which aspect you want to fix in place. For example, if you want to multiply all the cells in column A by cell B1, you would enter "$B$1" instead of "B1" in your formula. For example:

   =A1*$B$1

5. Copy your formula to other cells. For example, if you want to multiply values in column A by the value in cell B1 and display the result in column C, copy the formula to cells in column C. With the absolute reference in place, cell C1 will show the result for A1*$B$1, C2 will show A2*$B$1, and so on.

References

• Microsoft: Switch Between Relative, Absolute, and Mixed References