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 one 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
Press the key stroke F4 multiple times to change the position of the $.
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 😉😉
We want to stay always on the headers of our table so we will write the formula as follows
- Start by copying cell C4 (Ctrl + C)
- Then select all other cells
- 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 👍😍😎
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
- Now, display the formulas of your document by activating the menu Formulas>Show Formulas
- Your spreadsheet now looks like this
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 as these are the elements that never change.
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