Inevitably, you will often reference other cells in your calculations. There are two different types of cell references in Excel: relative and absolute. This is an important concept to understand for entering formulas in Excel.

Relative cell references will change when a formula is copied to another cell. Absolute cell references will remain constant when a formula is copied to other cells.

As an example, see the simple table below. There are a series of values in column A. In cell B1, there’s a formula with a **relative** reference to cell A1. If you **fill **that formula down (using the **fill handle **from Chapter 1, Section 7), the other cells in column B will refer to the cell directly to their left, **not** cell A1:

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

An **absolute** reference would maintain the reference to cell A1 in all of the cells:

For a table such as this, an absolute reference might not make sense, but there are many situations where it’s necessary.

In Excel, absolute references are denoted using a **dollar sign** before the row and column designators. A quick way to turn a *relative* reference into one that is *absolute* is to use the **F4 **key to toggle. The first time you type **F4**, the reference will become a fixed cell absolute reference, i.e. $A$1. This reference will always stay locked onto A1.

However, pressing **F4** a second time will allow you to fix the row reference while allowing the column to change (i.e. A$1). If you copy this reference, the column letter will be able to change (to A, B, C, etc.) like a relative reference, but it will always reference the first cell in the column.

Typing **F4 **a third time will fix the column reference but allow the row to change (i.e. $A1).

The fourth time **F4 **is pressed, it changes back to a relative reference.

Worksheet 03b contains an example that requires different types of references to complete the table. This is a fan airflow spreadsheet that uses fan affinity laws to calculate airflow as speed and fan diameter change based on a measured airflow with a known fan diameter and speed. The fan affinity laws state that the new airflow is proportional to the ratio of fan speeds (*n*) and the ratio of fan diameters (*d*) cubed:

This formula can be used to calculate the airflow from a reference fan speed, fan diameter and airflow rate as the speed and fan diameter change. In Worksheet 03b, the reference fan speed, fan diameter and airflow are in a smaller table above.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

Enter the formula into the first cell in the large table: **=C7*(B12/C5)*(C11/C6)^3** (see next page). Excel uses relative cell references by default, so use those for now.

This will calculate the expected airflow if a similar fan of smaller diameter was run at a lower speed. However, if you use the **fill handle** to fill this formula into the rest of the table, there will be errors in all the other cells:

This is because the references have moved. Absolute references are required here.

The three references to the original fan (in the small table) should stay fixed on a single cell. To do this, **select **the cell with the formula, **click **within the reference and type **F4** once. Do this for the references to cells C5, C6, and C7. This will add dollar signs before both the column letter and the row number, indicating that this reference will be constant.

The *n _{2}* variable is the speed of the new fan. This should be allowed to vary, because we have a number of speeds in the column on the left side of the table. For this reference, it will be necessary to fix the

*column*, but allow the

*row*to vary.

**Click**within the reference to cell B12 and type

**F4**three times to obtain $B12.

The *d _{2}* variable (C11) is the diameter of the new fan, located in the row along the top of the table. To lock onto this row and allow the column to vary, type

**F4**twice to obtain C$11.

**Fill **this formula into the rest of the table by **dragging the fill handle **first to the right, then down.

To verify that the cell references are correct, **click **within any cell then within the formula bar. The reference cells will be highlighted with a colored box. Each cell should highlight the corresponding fan diameter at the top of the table and the corresponding speed on the left side.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]