When writing formulas in Microsoft Excel, it’s always a good idea to consider if the formula will be copied to other cells. Generally, when you copy a formula to other cells using the fill handle (small box in the lower right of the cell), you are using relative references. In other words, Excel adjusts the row or column references in the different cells relative to the formula’s original position. Most of the time, this is what you want to do.
But what if you want to copy your formula to other cells while consistently referring to only one specific cell within your formula? Use an absolute reference instead.
For example, in our spreadsheet example below, we own a small Mom and Pop restaurant. Each of our employees earns at least the Missouri minimum wage, but we want to increase their hourly rate by 15 cents an hour to be more competitive with other stores in the area. Keeping in mind that we want to use cell references when writing formulas, we’ve put .15 in cell E2 rather than type a literal .15 as part of our formula.
In cell C4, if we write =B4 + E2 and then copy the formula, we can soon see that only the first formula in C4 is correct, and none of the rest of the cells are correct. Why?
Let’s look at cell C5 to understand. “B5” refers to cell B5 correctly, but “E3” should refer to E2. In other words, when we copied the formula, we used relative references. What we need to do is use an absolute reference instead.
Let’s fix our original formula in C4 by pressing the F4 key on the keyboard, which adds a $ in front of the E and a $ in front of the 2. This means that no matter how the formula is copied, the reference to E2 stays the same and does not adjust. In other words, the reference is absolute and does not change.
When we copy this formula that uses absolute references down the column, the answers are now correct.
Be sure to watch the Excel Formulas: Two Key Concepts video on Facebook for a demonstration of how you can use absolute references in your formulas.
Consumer Technology Specialist