Learn about absolute cell references and the importance of maintaining a constant source value in Excel. Change and move data without compromising your entire spreadsheet.
How to "lock" cells to keep a certain cell constant in formulas
Assume C1 = A1*B1
When this formula is copied or Autofilled, the row number in the formula adjusts. The formula in the next row in cell C2 would be A2*B2. By default, when you refer to a cell in a formula or a function, you are using a relative reference to the contents of that cell. When creating formulas, you just select a cell and it will be relative.
This is fine in most circumstances, but there are occasions when a cell reference is fixed and shouldn’t adjust.
When you create a formula, Excel doesn’t keep track of the EXACT cell addresses but rather the location of the cells as relative to the calculation.
Absolute references are a way to refer to a cell that doesn’t move, when dragging or copying a formula. Example: If you wanted to multiple a whole column by one number (like a tax % or commission rate), the date in the heading area of the worksheet, a penetration rate, etc. In these cases, you want to “lock” the cell reference to a fixed location by making it “absolute.”
Why would you use an absolute reference?
- For efficiency and not having to enter a formula every time
- Do not have to duplicate
- No need to embed a value such as a commission percentage
To make a value an Absolute reference, add $'s to the cell reference
- Use the “F4” key as a shortcut for this
- For MAC, use CMD + T
Master Mircosoft Excel
Become a pro and use Excel with confidence and ease through our robust Microsoft office classes in NYC and Excel classes in NYC. We use hands-on methods and real-world examples. Grow your marketable skills today!