Learn how to use a mixed reference in Excel to split the references into absolute and relative parts through examples.
This article is a part of our definitive guide on Excel Formulas.
How to use mixed reference
In Excel, all cell references are relative by default. Let us see what will happen if you want to use relative and absolute references in your expression:
- =$B1 lock the column B
- =B$1 lock the first row
- =$B$1 lock the B1 cell
Advantages of using mixed references in Excel Formulas
You can save time, make your spreadsheets and formulas easy to understand, and copy a formula across rows and columns without editing it.
Furthermore, in a spreadsheet audit, you can reduce the possible errors using carefully planned formulas and references.
Example
In the example, the formula in cell F4 uses mixed references. So, to copy the formula across the range F4:H6, you can do that without modifying the original formula.
=$D4 * (1-F$3)
The first part of the formula is $D4, which locks column D, and it enables you to copy the formula right until cell H4. In this case, the constant is the original price for Product A.
The formula uses mixed references and will calculate the discount price for Product A using the three variables:
- =F4 =$D4 * (1 – F$3)
- =G4 =$D4 * (1 – G$3)
- =H4 =$D4 * (1 – H$3)
Let us see the second part of the formula:
=$D4 * (1-F$3)
The 1-F$3 mixed reference locks the third rows that contain discounts. In this case, the constant 30%.
Using a mixed reference (F$3) enables you to copy the formula across the range F4:F6 without manually changing the formula in cell F4.
Result:
Tip: if you are working with formulas in Excel, the fastest way to toggle between absolute and relative references is by using a shortcut. We strongly recommend you use this way to speed up your work.
Additional resources and examples:
