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:

- =
**$B**1 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 and make your spreadsheets and formulas easy to understand and copy a formula across rows and columns without editing it on the fly.

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 reference 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 using a shortcut.** We strongly recommend you use this way to speed up your work.

**Additional resources and examples:**