How to use Relative and Absolute Cell Reference in Excel?

Relative and absolute cell reference are the two ways to refer to a cell in excel. Both of them function differently when they are copied to the other cells of the spreadsheet.

In this context, we will discuss both relative and absolute references. We will learn, how to use them with the help of examples.

Relative Cell Reference

The relative cell reference is the one that changes as you copy other cells. Whenever you copy a relative reference to the other cell it changes according to the relative position of the rows and columns.

The relative reference of the cells is most commonly used when you have to apply the same formula across multiple rows and columns.

Absolute Cell Reference

The absolute cell reference does not change when you copy them to other cells. When you copy an absolute cell reference to another cell, the corresponding row and column of the cell remain constant.

To specify that we have referred a cell in an absolute reference mode then we have to apply a dollar sign before the row and column of the referred cell.

Steps to use Relative and Absolute Cell Reference in Excel:

Use Relative Cell Reference

We write a formula using the relative cell reference in cell C1.

For Example:

=A1+B1

Now if we copy fill this formula to cells C2, C3, C4, C5.

The formula changes accordingly:

=A2+B2   … in cell C2

=A3+B3   … in cell C3

=A4+B4   … in cell C4

=A5+B5   … in cell C5

Now let us discuss an example that we have listed some fruits in column C i.e., from C4 to C8. We have the quantity of the corresponding fruit in column D i.e., from D4 to D8. And we have the rate or price of the corresponding fruits in column E i.e., from E4 to E8.

To calculate the total amount of each fruit we have to create a formula. So, we will create the formula for the first fruit that is orange in cell F4.

Now just press Enter to get the result of this formula. Further, we want the total amount of the remaining fruit. So, we need to fill or copy the same formula over the desired cells.

For this locate the fill handle at the lower right corner of the cell and click-drag it down up to the desired number f cell in this case up to F8.

The moment you release the mouse the formula is copied to the selected cells and the result is calculated automatically.

Observe that the formula has been copied to the other cells with relative references.

 

The relative references of cells in the formula change as the row changes.

Use Absolute Cell Reference

When you copy a cell containing the absolute references, the references do not change where you copy the cell. To denote absolute reference, we have to specify the dollar sign ‘$’ in front of the row and column of the cell.

In case the ‘$’ sign precedes both rows as well a column we refer to its as mixed references.

• $A$2 -> This indicates that column A and row 2 will remain constant.
• $A2 -> This indicates column A will remain constant but row 2 can change.
• A$2 -> This indicates column A will change but row 2 will remain constant.

Consider the above example and now we have to compute the sales tax on each fruit item. Let us say there 2.4% of sales tax on each fruit item. We have placed this sales tax rate in cell D11.

Now we will create a formula to calculate sales tax on orange in cell G4.

To calculate the sales tax on the rest of the fruits we can click-drag the formula up to G8.

Notice that as we have doesn’t mention the absolute reference of cell D11. It got changed at each cell where we have copied it.

This leads to an incorrect result.

To rectify this, we will now mention the absolute reference of cell D11 i.e., $D$11.

This time when we copy the formula to other cells the reference $D$11 doesn’t change. And we get the correct result.

So in this way, we can use the relative and absolute cell reference in excel.