How To Use Relative References in Microsoft Excel?

Posted on June 22, 2021 Updated on June 22, 2021

When we use an Absolute Reference when filling Cells, it keeps the Row and Column constant.

When we use a Mixed Reference when filling Cells, either the Row or the Column will remain constant.

A plain Cell Reference though is a Relative Reference. Meaning that the Reference is relative to the location of the Cell. By default, all Cell References are Relative References. When we copy them across multiple Cells, they change according on the relative position of Rows and Columns.

In the following example, we want to create a Formula that will multiply each item’s Unit Price with the Quantity. Instead of us creating a new Formula for each Row, we can just create one Formula in the Cell D2 and then copy it to the area of Cells D3:D7. Here we just use Relative References so the Formula will calculate the Total for each Unit Item correctly.

So, in the Cell D2, I want to create a Formula that will multiply the contents of the Cell B2 which is the Unit Price with he contents of the Cell C2 which is the Quantity.

We just use a Relative Reference as shown in the example below. Therefore, in the Cell D2 we type in the Formula =B2*C2.

Once we have a Result in the Cell D2, which contains the Formula =B2*C2, we just copy the Formula or in other words fill it to the area of Cells D3 up to D7. This way, by using a Relative Reference we have results in the rest of the Cells. We have successfully calculated the Unit Price of each Liquor with the Quantity of each Liquor, as we can see by activating the Cell D6 which contains the Formula =B6*C6.

The same stands in the following example. We want to create a Formula that will multiply the Hours of each Employee with the current Rate so we can find out how much we must pay them. Instead of us creating a new Formula for each Row, we can create one Formula in the Cell D11 and then copy it to the area of Cells D11: D15.

So, in the Cell D11, I want to create a Formula that will multiply the contents of the Cell B11 which is the Hours with the contents of the Cell C11 which is the Rate. So, we type in the Formula =B11*C11

Once we have a Result in the Cell D11, which contains the Formula =B11*C11, we just copy the Formula or in other words fill it to the area of Cells D11 up to D15. This way, by using a Relative Reference we have results in the rest of the Cells. We have successfully calculated how much money we should pay our Employees, as we can see by activating the Cell D13 which contains the Formula =B13*C13.