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:
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.
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 n2 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 d2 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.
