Types of references in Excel

Microsoft Excel is one of the most used tools in businesses, and one of the reasons for this is how easy it makes it to perform both simple and complex calculations.

To make any type of calculation with Excel, we use the included formulas. Such formulas need References to perform the calculations.

These references tell the formula exactly where to find the values(Sheet or workbook) that will be used to perform the calculations.

Basically, there are 2 types of references that can be used in Excel.

  1. Relative A1
  2. Absolute $A$1

First, we’ll talk about the relative references. These make reference to a position in the workbook that can and must change whenever its place in the formula changes.

For instance, if we have a formula in cell A2 to add the values from cells C2 and D2, we would notice that copying this very same formula into cell A3 would add the values of cells C3 and D3.

Like this:

References4

Next, we have the absolute references which come in handy when we need a static value in the formula. For instance, if we want to calculate the interest on a series of loans, we could simply add an absolute reference to have all the interest amounts in a single cell, instead of making it part of a chart.

If we want to calculate the interest on different loans with different amounts that use the same interest rate, an absolute reference will be very useful too.

 

 

 

Here’s an example of a formula with an absolute reference:

References3

References2

In addition to the references we’ve mentioned so far, we need to keep in mind that it is also possible to make references to cells located in different sheets, or even different Excel files .

It is also possible to make references to a group of cells.

References5

In this formula, we find a reference to a group of cells located in Sheet2 of our Excel workbook.

To do this, we add into the formula the cell where we want to start the group, followed by “ : “ and then the cell where we want the group to end.

In our last example, we have the group contained between cells C6 and C25, which is why we have  C6:C25 in our formula.

To make reference to a group of cells located in a different sheet of our workbook, we must type in the name of the sheet, followed by “ ! “  and then the cell or group of cells we want to make reference to.

For our example. Our sheet is called Sheet2, and the group of cells goes from C6 to C25. So the formula looks like this:

Sheet2!C6:C25.