Let’s take a look. So, we’ve looked at both relative and absolute references, and also at a situation where we needed to use both at the same time. These are sometimes called “mixed references.” A good example of a problem that requires a mixed reference is a multiplication table. If we enter a formula to multiply the numbers in column B by row 4, and use only relative references, we end up with huge numbers. This is because the relative references change to be relative to each formula. The numbers get huge because the formula is multiplying values in the cells directly above and to the left. On the other hand, if we make the references absolute and then copy the formula across the table, we end up with the same value in each cell. This is because the fully absolute references don’t change no matter where the formula is copied. What we need is something in between. For the first reference, which refers to the numbers in column B, we need to lock the column. We can do this by adding a dollar sign before the “B”. For the second reference, which refers to the numbers in row 4, we need to lock the row. We can do this by adding a dollar sign before the “4”. Now, when we copy the formulas across the table, we get the correct values. If we check a few of the copied formulas, you can see that they refer to the right cells. When you’re working with mixed references, remember that you can use keyboard shortcuts to “rotate” through the options instead of adding dollar signs manually. Let me clear the table and demonstrate. First, I’ll add the normal references. Then, I can place the cursor in the first reference and use F4 on Windows, or Command T on the Mac, to toggle through options until only the column is fixed. Then I’ll do the same thing on the second reference until only the row is fixed. Now I can copy the formula across the table, and the mixed references generate the correct formulas.
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.
