Returns: Book Drops Only. Holds Pick-Up: Curbside or Drive-Up Service. In-Branch: Computers, printing, fax, and copiers. Virtual Branch is available 24/7. Learn more about MCPL's pandemic response.

Back to top

Using Absolute References in Writing Excel Formulas

Published on Thu, 07/09/2020 - 08:20am
Using Absolute References in Writing Excel Formulas

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.

Fill handle.png

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.

E5.png

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?

Incorrect Formula.png

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.

Relative Reference in formula.png

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.

Fix original formula.jpg

When we copy this formula that uses absolute references down the column, the answers are now correct.

Correct Formulas.jpg

 

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.

Terri M.
Consumer Technology Specialist

Add new comment

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.

Was this page helpful? Yes No