Cell Referencing

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**)

Download this workbook for the chapter.

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.

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

- Select an empty cell and type an equals symbol (
**=**) to start the formula. - Select the reference cell for the
**Base**data. - Type an asterisk symbol (
*****) for multiplication. - Select the reference cell for the
**Height**data. - Type a forward slash (
**/**) for division, and type**2**to divide the height by half. - 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.

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.

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

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.

This content is provided to you freely by EdTech Books.

Access it online or download it at https://edtechbooks.org/bus_115_business_app/cell_referencing.