The great thing about Excel is that you can write a formula in one cell that references the contents of another cell. For example, say that cell B2 (the cell located in column B, row 2) contains the text "Happy Birthday!" You could write a formula anywhere in your worksheet which references cell B2 to use the information contained in that cell. Say you wanted to know the length of the text in cell B2, so you use the LEN function: =LEN(B2) which would return 15.
This is great because you only need to write "Happy Birthday!" once in your worksheet, and you can reference it in other formulas as many times as you want without writing the text in several places. Instead of typing "Happy Birthday!" into every formula, you can just write B2.
In the example above, B2 is what we call a relative reference (as opposed to an absolute reference) because it doesn't have dollar signs ($). A relative reference will change when the formula is copied or dragged to a new cell. For example, say you have a table of city names, and you want the first three letters of each city name.
| A | B | |
|---|---|---|
| 1 | NAME | FIRST 3 LETTERS |
| 2 | Minneapolis | =LEFT(A2, 3) |
| 3 | Chicago | |
| 4 | Madison | |
| 5 | New York |
We can use the LEFT function to get the first three letters of each city name. We can write =LEFT(A2, 3) in cell B2. In this example, A2 is a relative reference, so Excel knows to reference the cell to the left of where the formula is written. This is handy because now we can copy this formula all the way down column B and get the correct answer for each city.
Just as cell B2 referenced the city in cell A2, when we copy the formula to cell B3 the formula will change to reference the city in cell A3, and so on. If we were to copy this formula to cell B100, the formula would change to reference cell A100. This is useful when we want to use the same type of formula on multiple items in a list.
An absolute reference, on the other hand, does not change when the formula is copied or dragged to a new location.
An absolute reference in Excel is a cell reference that has dollar signs ($) before the letter and/or the number in the cell reference. For example, rather than the relative reference A2, you might see the absolute reference $A$2, as well as $A2 or A$2.
The dollar sign in an absolute reference "locks" or "anchors" the piece of the reference (the row, column, or both) that it appears in front of.
The absolute reference $A2 will always reference column A, but the row will change depending on where the reference is copied.
The absolute reference A$2 will always reference row 2, but the column will update based on where the reference is copied.
The absolute reference $A$2 will always reference cell A2, no matter where in the worksheet the formula is copied.
Absolute references are used when the cell you are referencing should not update based on where the formula is written. For example, say we have a worksheet where one specific cell contains a rate or percentage we would like to use in our formula. In the table below, we show the total bill for several diners in a restaurant. To calculate the proper tip amount for each bill, we will multiply the bill amount by the tip percentage, located in cell E1. We keep the tip amount in its own cell rather than writing 0.18 in each formula so that if we decide to change the tip percentage we only need to update it in one place (cell E1) rather than updating every single formula in the list.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | BILL | TIP AMOUNT | Tip Percentage: | 0.18 | |
| 2 | 24.17 | ? | |||
| 3 | 37.98 | ||||
| 4 | 14.01 | ||||
| 5 | 88.25 |
Say we wrote a formula with all relative references. We could write =A2*E1 in cell B2. This would work just fine for the first bill, and we would multiply 24.17 by 0.18 to get the correct tip. But as we copy this formula down the list to cell B3, the formula would change to multiply cell A3 by E2, which, of course, is empty.
Why does Excel do this? Because we copy the formula one row downward, Excel updates every relative reference one row downward as well, which is correct in the case of A3, but not in the case of E2. We want to reference cell E1 for the tip percentage no matter where the formula is written.
So we would write =A2*$E$1. This way we can copy the formula down column B and it will always reference the correct bill (because it's a relative reference) and always reference the correct tip percentage (because it's an absolute reference), of which there is only one, located in cell E1.
We can write an absolute reference such as $A$1 which will always reference cell A1 no matter where the formula is located. We say that both the row and the column in this absolute reference are anchored or locked, because each has a dollar sign in front. We can also lock one piece at a time.
By writing $A1 we lock the column; the row will change depending on where the formula is copied to, but it will always reference column A.
We can also write A$1. This locks the row. This absolute reference will always reference row 1, but the column will update depending on where the formula is pasted.
For example, say we have a company where the salespeople earn a different commission rate each month. We want to multiply each salesperson's sales for a given month by the commission percentage for that month to find their commission amount for the month.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | MONTH | COMMISSION % | MIKE SALES | JULIA SALES | ALEX SALES | MIKE COMMISSION | JULIA COMMISSION | ALEX COMMISSION | |
| 2 | Jan | 0.22 | 1020 | 1850 | 1400 | ? | |||
| 3 | Feb | 0.25 | 1520 | 1440 | 1500 | ||||
| 4 | Mar | 0.26 | 1580 | 1630 | 1650 | ||||
| 5 | Apr | 0.28 | 1650 | 1000 | 1740 |
We want to write one formula to find Mike's January commission, and then copy that formula to find everyone's commission for all months with the same formula.
In cell G2 we could write =$B2*C2 to calculate Mike's commission. Notice that C2 is a relative reference, and $B2 is an absolute reference where only the column is locked - the row updates based on where the formula is written.
If we write =$B2*C2 in G2 we multiply Mike's Sales by the January commission rate to correctly calculate Mike's January commission.
We can copy the formula to cell H2 (one column to the right of G2) to calculate Julia's commission. The relative reference changes to D2 (one column to the right of C2) to reference Julia's January sales, but the $B2 continues to reference the January commission rate because the column is locked. This correctly calculates Julia's commission for January.
If we copy the original formula down from G2 to G3 (one row downward), we can calculate Mike's February commission. The C2 changes to C3 (one row downward) to reference Mike's February sales. The $B2 changes to $B3 to reference the February commission rate because the column is locked, but the row is not.
We can copy this formula to any cell from G2 to I5 (and beyond) and always calculate the salesperson's correct commission for the given month.
Excel Exercises has tons of information and practice exercises to help you learn and practice working with new concepts in Excel. Get some Excel practice now!.