Chapter 4: Referencing Framework in Excel

In the course of your modeling, you will have to deal with a lot of repetitive and time-consuming calculations. Apart from taking up a lot of your time, it can make the practice of modeling boring and unattractive. Fortunately, Excel has a number of tools and features that can speed up your actions and make modeling a much more pleasing exercise. The referencing framework in Excel is one such feature.

At the end of the chapter, you will understand what the referencing framework is all about. You will have learned the different types of referencing and how and when to use them to improve your productivity.

This chapter specifically covers the following topics:

• An introduction to the framework
• Relative referencing
• Absolute referencing
• Mixed referencing

An introduction to the framework

A worksheet in Microsoft Excel is divided into over 1 million rows and over 16,000 columns. The rows are labeled 1, 2, 3, and so on to 1,048,576, and the columns are labeled A, B, C, and so on to XFD. The rows and columns intersect to form over 16 billion cells in one worksheet. However, since a cell is identified by the column and row that intersect to form it, each cell has a unique identification that is conventionally written as the intersecting column and row. Thus, at the intersection of column UV and row 59, we have the cell UV59. There is no other cell UV59 on that worksheet in that workbook on that computer. This feature forms the basis for the referencing framework in Excel. It means that you can use the contents of any cell simply by including its cell reference in a formula.

The following screenshot gives the simplest example of this. By typing =D4 in cell F5, the contents of cell D4, Happy day, have been duplicated in cell F5:

...

Relative referencing

Relative referencing is the default type of referencing in Excel. A reference is relative because when it is included as part of a formula that is copied to another location, the column and row of the reference will change by the same amount, relative to the position of the cell to which the formula is being copied.

Rather than type in values directly into cells, you should type the value in another cell and then enter the cell references of the cells containing the values, as shown in the following screenshot:

Figure 4.3 – The formula with the cell references

Entering the cell references rather than the values in the cells being referred to does not change the result of the formula. The only thing that changes is that you are now seeing cell references rather than values in the cells with formulas. However, when you decide to copy the formula to another location, the rules governing cell referencing come into play. Instead of...

Absolute referencing

Sometimes, you will have a formula containing a reference that you want to remain the same when you copy the formula to another location. For example, if we wanted to calculate the commission on sales for each salesperson, this would be sales x commission.

As we move down the list of salespersons, the row number changes so that the reference to the sales made by the salesperson moves from H5 to H6 to H7 and eventually to H20, the last record in our list. This is what we want so that the salesperson is matched with the correct sales.

However, we are using the same commission percentage that is in cell H2, and when we copy down the list, we want the cell reference to remain as H2. In other words, we need to lock this cell reference or make it absolute. We do this by putting a $ sign before the column and row parts of the reference, so H2 becomes $H$2. From the following screenshot, we can see that the formula has been entered in cell K5 as =H5*$H$2:

...

Mixed referencing

As mentioned earlier, a cell reference is made up of the row and column that intercept to form that cell. Thus, if a cell is in column G, row 59, its cell reference is G59, G being the column part and 59 the row part of the cell reference. No two cells can have the same cell reference on the same worksheet.

Mixed referencing occurs when you need to lock either the column part only, leaving the row part of the reference relative, or lock the row part only, leaving the column part of the reference relative. This is demonstrated in the following example.

You should take note of the following two things:

• Firstly, the referencing framework is only relevant when you want to copy a formula to another location.
• Secondly, its main function is to make it possible for you to enter a formula once and then copy it over a range that contains cells with formulas that have cell references in a similar position to the active cells.

While this framework allows...

Summary

In this chapter, we have learned about the referencing framework in Excel.

We have learned about the three types of referencing, relative, absolute, and mixed referencing, and when to use each of them.

We have understood that these concepts can save us a lot of time in our Excel work, but it is only relevant when we need to copy a cell or range of cells that contain one or more cell references to another location.

We have learned about the F4 shortcut key and how it toggles between the different forms of referencing.

In the next chapter, we will cover the need to understand the purpose of the assignment and how to build assumptions that are necessary to project our actual results for the next 3 to 5 years.