Mixed References

Mixed reference

A mixed reference is a reference that is fixed only on part of the reference:

  • either the row
  • or the column

Before showing you an example of a calculation using mixed references, we will detail the use of the $ symbol in a reference.

An absolute reference has two $. There is one for the rows and one for the columns.

But which dollar does what? 🤔🤨🙄

In fact it's very simple, just look the position of the $

  • If the $ is on the left of the letter it means you lock the column
  • If the $ is on the left of the row number it means you lock the row
A mixed reference is a reference locked only for the column or the row reference lock
Explanation of the dollar in a reference

Press the key stroke F4 multiple times to change the position of the $.

Multiplication table

To illustrate the use of a mixed reference, we will construct a multiplication table.

The idea here is to create a single formula and copy it for the rest of the document. This will save us writing the 99 other formulas 😉😉

Formula with mixed reference
Formula with mixed reference

We want to stay always on the headers of our table so we will write the formula as follows

=$B4*C$3

  1. Start by copying cell C4 (Ctrl + C)
  2. Then select all other cells
  3. Finally, paste the formula (Ctrl + V)

The multiplication table is now correct for every single cell.

We have created only one formula and copied it for the 99 other cells. How productive 👍😍😎

Multiplication table
Multiplication table

Tips to know where to insert the $?

If creating a formula with mixed references is difficult for you at the first glance, use this trick to know where to put the $. 💡

Repeat the previous year and this time write 3 or 4 formulas without any $.

Your document looks like this

The formulas has been created for 4 others cells
The formulas has been created for 4 others cells

2. Now, display the formulas of your document by activating the menu Formulas>Show Formulas

Menu to show the formulas
Menu to show the formulas

Your spreadsheet now looks like this

Display of the formulas in the cells
Display of the formulas in the cells

3. Now look carefully at the formulas. 🧐🧐🧐 You notice that for all formulas you have:

  • always the column B
  • and also always the row 3

So it is easy to know where to put the $.

  • It's before the column B
  • And before the row 3

And then, you copy-paste this formula to the rest of your document. With only 1 formula, you are able to fill the 100 cells 😉

Formula with mixed reference
Formula with mixed reference

Mixed references in a Table

Finally, if you want to create a mixed reference in a Table (Insertion>Table), YOU CAN'T! ⛔⛔⛔

In a Table you can only create absolute or relative references

Permanent link to this article: https://www.excel-exercise.com/mixed-reference/

Leave a Reply

Your email address will not be published.