Excel - Relative Cell References

What is a Relative Reference in Excel?

Microsoft Excel allows you to calculate values directly or indirectly using an inbuilt function. The data written in the dataset has its unique address. When you give a cell address in a designated cell and drag/copy the formula horizontally or vertically, the reference will automatically be changed across the consecutive cells, known as a relative reference. By default, Microsoft Excel uses the relative reference.

For example, the relative reference G4 means G column and 4th row in a worksheet. If you write the formula =G4 to the H2 cell and copy this formula to the consecutive cell H3 cell, the relative reference will automatically be changed to the =G5 in the next cell.

Benefits of Using Relative References

• If you wish to reuse the same formula, either row-wise or column-wise.
• It increases flexibility as relative reference changes quickly across the data set while doing calculations.
• It improves productivity by seamlessly filling the formula either downward or sidewards.
• It allows you to auto-adjust the defined formula while inserting or removing the values.

How to use Relative Reference in Excel?

Step 1 − Consider the sample dataset, which consists of four columns titled Electronic Device, Number of Orders, Individual Product Cost, and Total Price.

Step 2 − After that, the main task is calculating the total price of the PS 4. In the D2 cell, enter the formula =B2*C2 and hit the Enter button.

Step 3 − Moreover, when you drag the fill handle at the right bottom of the D2 cell and place it into the D5 cell, the same formula will be evaluated in the range D3:D5. This process is known as relative reference, as the cell references automatically change when you drag the formula into the selected cell ranges. For example, the formula for D3 cell is =B3*C3 and so on.

Note − If the relative reference of the cells does not change after using the fill handle, you need to change the Manual setting to Automatic in the Calculation options under the Formula tab.

When to Avoid Using Relative References in Excel

If you intend to provide a fixed reference during calculation, you should not use a relative reference in this scenario. If you require both absolute and relative references while computing formulas, then, in this case, you can employ mixed references.