UseExcel: Relative versus Absolute References

Introduction

Cell formulas are the backbone of Excel. Formulas must start with the equals sign (=) and can contain mathematical expressions such as =1+1 or functions such as =SUM(78+22) or references to other cells such as =A1+B1. It's the last type that we want to explore on this page.

Building a Company Picnic Budget

The Picnic Budget example uses both absolute and relative cell references. In the Variable Cost Expenses design, we determine that for our first item, Food, Unit Cost X Quantity = Total Cost. This is shown in Figure 1. Absence of dollar signs ($) in the cell D12 formula =B12*C12 indicates that we are using relative references.
Figure 1: Cell D12 - using relative references.

Next, we enter the expense line for Beverages and stop at cell D13. Rather than rewriting the formula for Total Cost in cell D13 and taking a chance that we might enter the wrong formula, we instead fill down the formula in cell D12 above. We do this because we know that Excel will use relative references when we fill cells. (If you are not familiar with filling cells, please have a look at UseExcel: Using Edit, Fill features.)
Figure 2: Cell D13 - After Fill, Down - using relative references.

We start using absolute references when we refer to the Quantities for Variable Cost Expenses in cell C12. The formula is =$G$8. When we go to fill down the Quantity formula for Beverages from cell C12 to cell C13 the row number does not increment from 8 to 9.
Figure 3: Cell C12 - absolute reference to cell G8.

How the Quantities for Variable Cost Expenses in cells C12 to C14 are referring absolutely back to cell G8 is illustrated in Figure 4 using the Formula Auditing toolbar.
Figure 4: 3 Quantities referring to $G$8.

Varying degrees of Absoluteness

• =G8 of course, has no absolute reference and therefore is relative. When you fill down to other cells it will look like =G9, =G10, and so on. If you fill right to other cells it will look like =H8, =I8, and so on.
• =$G8 refers to column G absolutely and so when you fill down to other cells it will look like =$G9, =$G10, and so on. If you fill right to other cells it will look like =$G8, =$G8, and so on.
• =G$8 refers to row 8 absolutely and so when you fill down to other cells it will look like =G$8, =G$8, and so on. If you fill right to other cells it will look like =H$8, =I$8, and so on.
• Finally, =$G$8 refers to column G and row 8 absolutely and so when you fill down to other cells it will look like =$G8, =$G8, and so on. If you fill right to other cells it will look like =G$8, =G$8, and so on.

The animated image shows what the formula looks like as you edit the formula in the formula bar and repeatedly press F4.
Figure 5: Using F4 to cycle through degrees of absolute references (plays 3 times). To replay, press F5