We are going to learn about cell referencing. Cell referencing in Excel is when you go to a cell and type in a formula that refers to data in other cells, which makes it calculate the result automatically. One of the main reasons cell referencing is important to learn is because it directly connects to a concept called circular referencing; circular referencing is a problem caused by referencing cells in the wrong way.

First, let's suppose that we have ten different shapes. The shapes could either be a rectangle or a triangle. We want to know the area of the shape if it’s a rectangle and the area of a shape if it's a triangle. We are given the base and the height for each shape. (See Figure 3.1)

Area of a Rectangle

We start by calculating the area of the rectangle, and a triangle as well. To get the area of a rectangle, it’s simply the base multiplied by the height.

1. Select an empty cell and type an equals symbol ( = ) to start the formula.
2. Select the reference cell for the Base data (B4 in Figure 3.2).
3. Type an asterisk symbol ( * ) for multiplication.
4. Select the reference cell for the Height data (C4 in Figure 3.2).
5. Press Enter to complete the cell’s formula (=B4*C4).
   

   

    

Area of a Triangle

1. Select an empty cell and type an equals symbol ( = ) to start the formula.
2. Select the reference cell for the Base data.
3. Type an asterisk symbol ( * ) for multiplication.
4. Select the reference cell for the Height data.
5. Type a forward slash ( / ) for division, and type 2 to divide the height by half.
6. Press Enter to complete the cell’s formula (=B4*C4/2).

We can select both formulas and double-click the little box in the lower-right to drag the formulas all the way to the bottom. (See Figure 3.3)

Let's assume that the base on this example is 18, which is the same as the height (see Figure 3.4). If the Base cell’s data changes, the formula’s cell will recalculate the area of the rectangle because it is referencing the Base cell.

Area of a Square

Another way to calculate the area of a shape is if it is a square. In this case, it’s 18 by 18 so the base and height are the same (see Figure 3.5). We can do that by just taking either the base or the height and squaring it.

1. Select an empty cell and type an equals symbol ( = ) to start the formula.
2. Select the reference cell for the Base data.
3. Type a caret symbol ( ^ ) and 2 to square the base.
4. Press Enter to complete the cell’s formula (=B4^2).
   

   

Circular Referencing

Now let's talk about what a circular reference is by going through an example. If we want to calculate the height of the rectangle, and we know its area and base data, we could divide the area by the base. (See Figure 3.6)

However, when we press Enter, we get an error message: 

“There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells.” 

Whenever you see this warning or any type of warning in Excel where it doesn’t recognize your formula or see some kind of problem, you should never ignore it. (See Figure 3.7)

In this case, it has a circular reference. When we select OK, we will see a blue line showing us where the reference is circular. It is circular because our area data cell refers to the Base and Height cells, and our calculation for the Height cell refers to the result of the initial area formula, so it’s referencing itself. (See Figure 3.8)

This reasoning or logic that we have used is circular and Excel cannot compute it—no calculator could compute it. We will need to eliminate one of the formulas. We can only reference the area formula on the inserted base and height data, or the height based on the inserted area and base data. We cannot do both.

If you see circular referencing or an error message, you can navigate to the Formulas tab, select Error Checking, and choose Circular References to view cells with an error. (See Figure 3.9)

It may help solve the problem if you receive a workbook with a circular reference from someone else, or if you save your work and return to it later. Understanding how to resolve circular references is a key skill as you work and create formulas throughout a workbook that reference other cells.