Excel Relative and Absolute Cell References

When working with formulas in Excel, it's crucial to understand the difference between relative and absolute cell references. These two types of cell references behave differently when copied or filled into other cells.

Relative cell references change based on their position when a formula is copied to another cell, making them highly useful for repetitive calculations across different rows or columns. Absolute cell references, on the other hand, remain constant regardless of where they are copied, ensuring that a specific cell reference does not change.

By mastering both relative and absolute references, you can enhance your Excel skills, making your data management and formula application more efficient. Whether you're a beginner or an experienced user, knowing how to use these references correctly is essential for accurate and effective spreadsheet work.

What is a Cell Reference in Excel?

A Cell reference is the address or name of a cell or a range of cells. It is the combination of column name and row number. It helps the software identify the cell from where the data/value is used in the formula. We can reference the cell of other worksheets and also of other programs.

• Referencing the cell of other worksheets is known as External referencing.
• Referencing the cell of other programs is known as Remote referencing.

Types of Cell References in Excel

There are two types of cell references in Excel:  

• Relative reference
• Absolute reference

Relative Reference in Excel

Relative reference is the default cell reference in Excel. It is simply the combination of column name and row number without any dollar ($) sign. When you copy the formula from one cell to another the relative cell address changes depending on the relative position of column and row. C1, D2, E4, etc are examples of relative cell references. Relative references are used when we want to perform a similar operation on multiple cells and the formula must change according to the relative address of column and row.

Absolute Reference in Excel

An absolute reference is the cell reference in which the row and column are made constant by adding the dollar ($) sign before the column name and row number. The absolute reference does not change as you copy the formula from one cell to another. If either the row or the column is made constant then it is known as a mixed reference. You can also press the F4 key to make any cell reference constant. $A$1, $B$3 are examples of absolute cell reference.

How to Use Excel's Relative Reference - Example

We want to add the marks of two subjects entered in column A and column B and display the result in column C. Here, we will use relative reference so that the same rows of columnsExcel A and B are added.

Step 1: Open Excel and Enter Data

Open your Excel Sheet and enter the data into the Excel sheet. In this example, we are entering marks 1 and 2 in columns A and B respectively with total marks in column C.

Step 2: Enter the Formula

Now we need to enter the formula to add marks 1 and 2. We write the formula in any cell and press enter so that it is calculated. In this example, we write the formula(= B2 + A2) in cell C2 and press enter to calculate the formula.

Step 3: Click on Fill Handle

Now click on the Fill handle at the corner of the cell which contains the formula(C2).

Step 4: Drag the Handle

Drag the Fill handle up to the cells you want to fill. In our example, we will drag it to cell C10.

Now we can see that the addition operation is performed between the cells A2 and B2, A3 and B3, and so on.

Step 5: Check the Performed Action

You can double-click on any cell to check that the operation is performed in between which cells.

Thus, in the above example, we see that the relative address of cell A2 changes to A3, A4, and so on, similarly the relative address changes for column B, depending on the relative position of the row.

How to Use Absolute Reference: Example

For example, We want to multiply the sum of marks of two subjects, entered in column A and column B, with the percentage entered in cell C2 and display the result in column D. Here, we will use absolute reference so that the address of cell C2 remains constant and does not change with the relative position of column and rows.

Step 1: Open Excel and Enter Data

Open your Excel Sheet and enter the data into the Excel sheet. In this example, we are entering marks 1 and 2 in columns A and B respectively with total marks in column C.

Step 2: Enter the Formula

We write the formula in any cell and press enter so that it is calculated. In this example, we write the formula(=(A2+B2)*$C$2) in cell D2 and press enter to calculate the formula

Step 3: Click on Fill Handle

Now click on the Fill handle at the corner of the cell which contains the formula(D2).

Step 4: Drag the Handle

Drag the Fill handle up to the cells you want to fill. In our example, we will drag it to cell D10.

Now we can see that the percentage is calculated in column D.

Step 5: Check the Performed Action

You can double-click on any cell to check that the operation is performed in between which cells, and we see that the address of cell C2 does not change.

Thus, in the above example, we see that the address of cell C2 is not changed whereas the address of columns A and B changes with the relative position of the row and column, this happened because we used the absolute address of cell C2.

How to use Cell References with Multiple Worksheets in Excel

Step 1: Open Excel and Enter Data

Open your Excel Sheet and enter the data into the Excel sheet. In this example, we are entering marks 1 and 2 in columns A and B respectively with total marks in column C.

Step 2: Locate the Cell for Reference

Locate the Cell for Reference and note down its worksheet name.

Step 3: Access Second Worksheet

Go to the second worksheet of your Choice and Select it

Step 4: Locate Cell

Go to the Cell where you want to store the value.

Step 5: Enter Equals To followed by Sheet Name and Exclamation

Enter the equal to sign in the selected cell followed by the worksheet name followed by! sign. Hit Enter.

Difference Between Absolute and Relative cell Reference in Excel

Also Read: Relative, Absolute ,and Mixed Cell Reference in Excel

Conclusion

Knowing about relative and absolute cell references in Excel helps manage data easily. These concepts let you create dynamic spreadsheets and make your work faster. By mastering them, you can automate tasks and get more done. Keep using these tools to become an Excel pro and make your work easier.

What are the advantages of relative reference in Excel?

Relative references in Excel mean that when you copy a formula to other cells, the program automatically changes the column and row numbers to match where the formula is copied. It adjusts based on how many columns or rows the formula moves.

Which type of referencing occurs in the formula =$ F $12 /$ G $1 +$ M $2?

This is an example of Absolute Referencing.