Mixed References

Mixed References
Last Updated on 03/10/2023
Reading time: 3 minutes

A mixed reference is a cell's reference that blocks the reference of a Column or a Row but not both. This is very useful to build complex workbooks.

  1. Block the reference of the column

    Like $A1

  2. Block the reference of the row

    For instance A$1

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 at the position of the $

  • If the $ is on the left of the letter it means you locked 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

Tips: Press the key F4 multiple times to change the position of the $ when you build your formula.

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 from 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 👍😍😎

Multiplication table
Multiplication table

Tips to know where to insert the $?

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

  1. First, don't think at all to the dollar. Write your formula without dollar
  2. Create 3 or 4 formula in your document
The formulas has been created for 4 others cells
  1. Now, display the formulas of your document by activating the menu Formulas>Show Formulas
Menu to show the formulas

Your spreadsheet now looks like this

Display of the formulas in the cells
  1. Now look carefully at the formulas. 🧐Each formula points to
    • The column B, so we put a $ before the B
    • The row 3, so we put a $ before the 3
Demo of multiplication table with Excel

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

1 Comment

  1. Azhar hanif
    23/09/2021 @ 09:12

    can we say that when rows and columns are different with respect to multiplication and
    for divisions then we used mix reference

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Mixed References

Reading time: 3 minutes
Last Updated on 03/10/2023

A mixed reference is a cell's reference that blocks the reference of a Column or a Row but not both. This is very useful to build complex workbooks.

  1. Block the reference of the column

    Like $A1

  2. Block the reference of the row

    For instance A$1

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 at the position of the $

  • If the $ is on the left of the letter it means you locked 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

Tips: Press the key F4 multiple times to change the position of the $ when you build your formula.

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 from 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 👍😍😎

Multiplication table
Multiplication table

Tips to know where to insert the $?

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

  1. First, don't think at all to the dollar. Write your formula without dollar
  2. Create 3 or 4 formula in your document
The formulas has been created for 4 others cells
  1. Now, display the formulas of your document by activating the menu Formulas>Show Formulas
Menu to show the formulas

Your spreadsheet now looks like this

Display of the formulas in the cells
  1. Now look carefully at the formulas. 🧐Each formula points to
    • The column B, so we put a $ before the B
    • The row 3, so we put a $ before the 3
Demo of multiplication table with Excel

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

1 Comment

  1. Azhar hanif
    23/09/2021 @ 09:12

    can we say that when rows and columns are different with respect to multiplication and
    for divisions then we used mix reference

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *