Relative and absolute Cell reference in Excel

There are two types of Cell references in Excel; relative Cell reference and absolute Cell reference. In this lesson, we will learn the difference between relative and absolute Cell references in Excel.

Relative reference is the default type of reference in Excel. When you are using relative reference, if you copy and paste an Excel formula from one Cell to another Cell, the Cell references used in Excel formula are automatically changed.

What is relative Cell reference in Excel

To understand the concept of relative Cell reference and absolute Cell reference in Excel, refer below images.

Below image shows as simple Excel formula, which uses the multiplication mathematical operator (*) to multiply the numeric values stored in Cell C2 and D2.

Now, what I am trying to do is to copy the formula from Cell E2 and paste it in Cell E3. Refer two images below.

The formula is now pasted in Cell E3. Have you noticed any change in the original formula which was copied from Cell E2? The original Cell references used in the formula inside Cell E2 were Cells C2 and D2. Those are changed to C3 and D3 when I pasted the formula in Cell E3.

Excel automatically updates the formula with new Cell references whenever you copy and paste the formula to a different Cell.

Refer below image.

The Cell references used in Excel formula are automatically changed when I pasted the Excel formula to a different Cell. This type of reference is called as relative reference. The relative reference is the default Cell reference in Excel and this the behaviour we need in most cases.

What is absolute Cell reference in Excel

Sometimes you may need to write Excel formulas by using the value from just one Cell on many Rows.

For example, refer below image. In below image the unit price of one sack of cement (50 kgs sack) is 6.25 Dollars. We need to find the total price of different batches of cement purchased by paying the same price. In this case, we need to just multiply the quantity stored in different rows with unit price stored in a single Excel Cell. In this case, if we write an Excel formula, using relative Cell reference, the formula will fail.

Here we always want the Excel formula to use the numeric value stored in Cell D2 (unit price), where ever we use the formula. In similar situations, we need to use Excel absolute Cell reference in formulas.

To create Excel absolute cell reference, the $ (Dollar) symbol is used in Cell references. Instead of typing the $ (Dollar) symbol, directly inside Cells, you may press F4 key once, when the Cell is in "Enter mode".

When an absolute Cell reference is used in a formula, it is also called as "anchoring" that Cell reference.

Note below points when using the F4 key to create absolute Cell references in Excel.

• The Cell where you are trying to write the formula must be in the "Enter mode".
• You need to type the "=" symbol as the first character of the formula, to tell Excel that you are trying to write a formula.
• You need to keep the cursor at the beginning of the Cell address at the middle of the Cell address or at the end of the Cell address.
• Press the F4 key only once to create absolute Cell reference.

Now, try again to copy the formula and paste the formula in a new Cell. Please refer below image.

You can see that the absolute Cell reference is not changed as in the case of relative Cell reference, when we pasted the formula in a new Cell. Please refer below image.