Mixed reference

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 one does what? 🤔🤨🙄

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

  • If the $ is on the left of the letter, this means you lock the column
  • If the $ is on the lest of the row number, you lock the row

 

Press the key stroke F4 many 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 entire document. This will save us to write the 99 other formulas 😉😉

 

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 the C4 cell (Ctrl + C)
  2. Then select all the other cells
  3. Finally, paste the formula (Ctrl + V)

The multiplication table is now correct for every single cells.

We have create only one formula and copy it for the 99 other cells. What a productivity 👍😍😎

How to know where insert the $?

If creating a formula with mixed references is difficult for you at the first glance, there is a 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.

Mixed references in a Table

Finally, if you want to do a mixed reference in a Table (Insertion>Table), THIS IS NOT POSSIBLE! ⛔⛔⛔

In a Table, you can only create absolute or relative references

Related posts


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


Leave a Reply

Your email address will not be published.