What Are Absolute References in Excel?

If you use formulas in Excel, more than likely, you’re referencing other cells. The way a cell is significantly referenced affects how a formula appears in other cells.

Relative references adjust and change based on their location in a spreadsheet. Absolute references, on the other hand, stay the same regardless of where they are copied.

So if you write a formula in cell C1 that makes an absolute reference to cell A1, you will still refer to cell A1 and B1 even after pasting this formula to cell C2. However, a relative reference refers to cells A2 and B2.

READ MORE  The Essential Microsoft Excel Formulas and Functions Cheat Sheet

Related: How to Find and Remove Circular References in Excel

Absolute References in Excel

By default, every cell reference in Excel is a relative reference. If you refer to cell A1 in a formula as A1, Excel will interpret this to be a relative reference, whereas referring to it as $A$1 will make it an absolute reference.

Absolute references are useful when you want to refer to a constant value in a formula. Let’s check the example.

Absolute references are useful for referring to a constant value.

If you have two scores for each team in a robotics competition, one from their first set, and another from their second set. The total score for each team is the sum of their scores on both sets multiplied by a constant multiplier.

READ MORE  How to get early access to Outlook.com new beta design

Let’s write the formula for the first team with an absolute reference and then copy it for the other teams.

  1. Select cell C3.
  2. In the formula bar, write the formula below and press enter:

    This will add A3 (score from set 1) with B3 (score from set 2), and then multiply the result by B1 (the multiplier). Note that A3 and B3 are relative references, while B1 is an absolute reference.

  3. Grab the fill handle and drag it over the cells below. Excel will now calculate the final score for all teams.

Absolute references stay constant at all times.

Click on each of the cells of the final score and observe the formula in the formula bar. As you can see, the first two reference cells have changed, but the third one has remained constant. This is because the third reference cell is an Absolute Reference.

Related: Excel Autofill Tricks to Build Your Spreadsheets Faster

Control Your Excel References

In Excel, absolute references help keep values constant in formulas. You now know how to use absolute references when you want to copy a formula, but copying formulas in Excel is more than that.

      Select your currency
      Register New Account