Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to create a mixed reference

Tags 
Summary 
A mixed reference is part relative, and part absolute. Mixed references are useful when you want only part of a cell reference to change when you copy a formula.
Video Transcript 

A mixed reference is a reference that's part relative, and part absolute.

Let's take a look.

So, we've looked at both relative and absolute references, and also at a situation where we need 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 copied formulas, you can see that they are referring to the right cells.

When you're working with mixed references, remember that you can use keyboard shortcuts to "rotate" through options instead of adding the 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 with 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.

Author 
Dave Bruns