Excel Relative References

Cell references are an integral part of excel use. In financial modeling, there is usually a need to refer to cells for various things like completing a function, data validation list, conditional formatting, named ranges and so on, and without knowing which type of reference you require, you may end up spending a lot of time making corrections to references initially selected. In this post, we would walk you through what relative references are and how to go about it. Before we begin however, Let’s touch on the three main types of cell references:

Types of Cell references in Excel

There are three main types of cell references in excel namely:

  • Relative references: This is the default referencing style of excel when you select cells within the same workbook. In this, excel automatically calculates the movement from left or right and top or bottom from the originating cell (where you copy the cell containing the reference) to the destination cell (final destination you paste the formula copied). Confused? Don’t worry, we would treat in detail in the next section.
  • Absolute references: They are references to a particular cell or range of cells that do not change regardless of where you copy from and paste to. They remain fixed and are denoted by a dollar ($) sign appearing before and after the column letter. Please see our post on Absolute references for more details.
  • Mxed references: These are a combination of both relative and absolute references. In this case, the columnn could be fixed and row relative or the opposite could be the case. This could be suitable when you have all inputs on a particular column and you need to copy and paste formulas on adjacent columns. Please see our post on mixed references for more details

Relative reference example

In our example for this section, we have information on data offers of a Telecommunications company. The unit price and sales count have been provided and our task is to calculate revenue for each plan using a simple multiplication formula. We are however not going to manually type in the formula on each row, as we would be relying on excels relative referencing to copy and paste the formula to other cells.

Relative References 1

Let’s see how we can create a formula in cell D3 to multiply the price and sales count, and then copy it across from cells D4:D10 using relative referencing.

Step 1: Type in the multiplication formula B3 X C3

Relative References 2

Step 2: Because the input cells are not anchored (locked in), we can copy and paste with CTRL + C and CTRL + V respectively.

Relative References 3

Result: The other plans automatically pick the price and sales count of their respective rows.

=B3*C3
=B4*C4
=B5*C5
=B6*C6
=B7*C7
=B8*C8
=B9*C9
=B10*C10
Relative References 4

We have come to the end of this tutorial, remember that there is no need to do anything when usng relative references. It is the default of excel, so you can just copy and paste. If however you desire to keep a cell, column or row fixed, then you would need to use absolute references for the first and mixed references for the latter two (column or row fixed).

Leave a Comment