Relative Reference in Excel

Relative Reference in Excel (Table of Contents)

• Relative Reference in Excel
• How to Use Relative Reference in Excel?

Relative Reference in Excel

If asked to mention that one feature of Excel that truly makes the experience magical while working with formulas, it would perhaps be the fact that hard-coding the values into your formulae isn’t necessary (in fact, it is not even recommended).

In Excel, spreadsheets are composed of columns and rows, which constitute ‘cells’. Every cell corresponds to a precise column and precise row. To explain this through an example, the cell B2 refers to the second column (B) and the second row (2). Accordingly, D16 points to the fourth column (D) and the sixteenth row (16). The actual edge of Excel is in the usability of the cell references in other cells while creating formulae.

What is the Relative Reference in Excel?

Relative references refer to a cell or a range of cells in excel. Every time a value is entered into a formula, such as SUMIFS, it is possible to input into Excel a “cell reference” as a substitute for a hard-coded number. A cell reference may come in the form B2, where B corresponds to the column letter of the cell in question and 2 represents the row number. Whenever Excel comes across a cell reference, it visits the particular cell, extracts out its value, and uses that value in whichever formula that you’re writing. When this cell reference is duplicated to a different location, the cell’s relative references correspondingly also changes automatically.

When we reference cells like this, we can achieve it with any of the two “reference types”: absolute and relative. The demarcation between these two distinct reference types is the different inherent behavior when your drag or copy and paste them to different cells. Relative references can alter themselves and adjust as you copy and paste them; absolute references, contrarily, do not. In order to successfully achieve results in Excel, it is critical to be able to use relative and absolute references in the right way.

How to Use Relative Reference in Excel?

This Relative Reference is very simple easy to use. Let us now see how to use the Relative Reference in Excel with the help of some examples.

Example #1

Let us consider a simple example to explain the mechanics of Relative Reference in Excel. If we wish to have the sum of two numbers in two different cells – A1 and A2, and have the result in a third cell A3.

So we apply the formula A1+A2, which would yield the result as 200 in A3.

The Result is 200.

Now suppose, we have a similar scenario in the next column (“B”). Cell B1 and B2 have two numbers and we wish to have the sum in B3.

We can achieve this in two different ways:

Here we physically write the formula to add the two cells B1 and B2 in B3 to get the result as 30.

The result as 30.

Or we could simply copy the formula from cell A3 and paste in cell B3 (it would work if we drag the formula from A3 to B3 also).

So, when we copy the contents of cell A3 and paste in B3 or drag the contents of cell A3 and paste in B3, the formula gets copied, not the result. We could achieve the same result by right clicking on cell A3 and use the Copy option.

And after that, we move to the next cell B3 and right click and select “Formulas (f)”.

What this means is that cell A3 = A1+A2. When we copy A3 and move one cell to the right and paste it onto cell B3, the formula automatically adapts itself and changes to become B3 = B1+B2. It applies the summation formula for B1 and B2 cells instead.

Example #2

Now, let us look at yet another practical scenario which would make the concept quite clearly. Let us assume that we have a data set, which consists of the Unit Price of a product and the Quantity sold for each of them. Now our objective is to calculate the Sale Price, which can be described by the following formula:

Sale Price = Unit Price x Units Sold

To find the Sale Price, we need to now multiply Unit Price with Units Sold for each product. So, we shall now proceed to apply this formula for the first cell in Sale Price i.e. for Product 1.

When we apply the formula, we get the following result for Product 1:

It successfully multiplied the Unit Cost by the Units Sold for Product 1, i.e. cell G2 * cell H2, i.e. 1826.00 * 20, which gives us the result 36520.00.

So now we see that we have 9 other products to go. In real case scenarios, this could go up to hundreds or thousand or rows. It becomes difficult to nearly impossible to simply go about writing the formula for each row.

Hence, we will use the Relative Reference feature of Excel and simply copy the contents of cell I2 and paste in all of the remaining cells in the table for the column Sale Price or simply drag the formula from cell I2 to the rest of the rows in that column and get the results for the whole table in less than 5 seconds.

We can either press Ctrl + D or simply Copy and Paste the cell I2 to all the selected cells.

Things to Remember About Relative Reference in Excel

• While copying the Excel formulae, relative referencing is generally what is desired. This is the reason why this is the default behavior of Excel. But sometimes, the objective might be to apply absolute reference, rather than relative reference. Absolute Reference is making a cell reference fixed to an absolute cell address, due to which, when the formula is copied, it remains unaltered.
• Absolutely no dollar signs are required! With Relative referencing, when we copy the formula from one place to others, the formula will adapt accordingly. So, if we type =B1+B2 into the cell B3, and then drag or copy-paste the same formula into the cell C3, a relative reference would automatically adjust the formula to =C1+C2.
• With Relative referencing, the referred cells automatically adjust itself in the formula as per your movement, either to the right, left, upward or downwards.
• If we were to give a reference to cell D10 and then shift one cell downwards, it would change to D11, if instead, we shift one cell upwards, it would change to D9. If however, we shift one cell to the right, the reference would change to E10, and instead if we shift one the cell to the left, the reference would automatically adjust itself to C10.

You can download this Relative Reference Excel template here –Relative Reference Excel Template

Recommended Articles

This has been a guide to a Relative Reference in Excel. Here we discuss its uses and how to use Relative Reference in Excel with excel examples and downloadable excel templates. You may also look at these useful functions in Excel –

1. How to Use MATCH Function
2. Guide to Scrollbar in Excel
3. SUBSTITUTE Function in Excel – You Must Know
4. POWER Function in MS Excel