Let’s take a look. Recall from an earlier lesson that copying and pasting the formula in cell D11 to other locations caused the cell references to change. To illustrate how absolute cell references work, let’s convert our formula to use absolute references and try again. To make a reference absolute, you need to add dollar signs to the address: one to lock the row, and one to lock the column. You can do this manually or using the keyboard shortcut F4. Let’s first convert the relative reference B9 to an absolute reference manually by adding a dollar sign in front of the B and in front of the 9. Now let’s copy and paste the formula to see how cell references are affected. As you can see, the address B9 is now fixed and has not been changed. The relative reference D6 has been changed. Let’s undo and convert the relative address D6 to an absolute address; this time with a keyboard shortcut. Just put the cursor in or next to the reference and press F4. You’ll see dollar signs added to both the column and to the row. Now both cell references are absolute and won’t change when we copy the formula. Let’s give it a try. Notice that we get the same result each time. This is because each copy of the formula is still referencing the exact same cells as the first copy. To summarize, relative references change when formulas are copied to new locations; absolute references don’t. In the next lesson, we’ll look at a situation where this behavior is useful.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
