Tips Excel : Relative versus Absolute Cell References

Imagine that you go to C1 and enter the formula =A1+B1. Your human eyes will define that as the value in A1 added to the value in B1. However, Excel, doesn’t see it that way. Because you entered the formula in cell C1, Excel reads the formula like this:
Take the value in the cell two spaces to the left and add it to the value in the cell one
space to the left.

If you copy the formula =A1+B1 from cell C1 and paste it into cell D1, the formula in D1 will seem different to you. You will see =B1+C1. But to Excel, the formula is exactly the same: Take the value in the cell two spaces to the left and add it to the value in the cell one space to the left.

By default, Excel considers every cell reference used in a formula as a relative reference. That is, it takes no heed of actual column row coordinates. Instead, it evaluates the cell references in terms of where they are relative to the cell the formula resides in.

This behavior is by design and works in situations in which you need the cell references to be adjusted when you copy the formula and paste it to other cells. For instance, the formula shown in cell C1 (see Image 1-2) was copied and pasted down to the rows below. Note how Excel helps by automatically adjusting the cell references to match each row.

Image 1-2: Relative references come in handy when you need to apply the same
operation to values in different rows.

Note :When you copy and paste a formula, Excel automatically adjusts the cell references. However, if you cut and paste a formula, Excel assumes that you want to keep the same cell references and does not adjust them.

If you want to ensure that Excel does not adjust cell references when a formula is copied, you can lock the references down by turning them into absolute references. You turn them into absolute references by adding a dollar symbol ($) before the column and row reference. For instance, you can enter =$A$1+$B$1 to add the value in A1 to the value of B1.

By adding the dollar symbol to any cell reference and making that reference absolute, you can copy the formula anywhere else on the spreadsheet, and the formula will always point to A1 and B1.

Excel gives you the flexibility to make any part of your cell reference absolute. That is, you can specify that only the column part of your cell reference should be locked but the row part can adjust. Alternatively, you can specify that only the row part of your cell reference should be locked but the column part can adjust. These different types of absolute references are commonly called Absolute, Row Absolute, and Column Absolute, and here’s how they work:

• Absolute: When the formula is copied, the cell reference does not adjust at all. Example: $A$1
• Row Absolute: When the formula is copied, the column part adjusts but the row part stays locked. Example: A$1
• Column Absolute: When the formula is copied, the column part stays locked but the row part adjusts. Example: $A1

Note : Instead of manually entering the dollar symbols, you can easily toggle between the possible reference styles by highlighting the cell reference in your formula and pressing the F4 key.