What Is Mixed Referencing In Excel

5 min read

Mixed referencing in Excel is a cell-reference technique that locks either the column or the row of a cell address while leaving the other part free to change when a formula is copied. It sits between relative referencing, where both the column and row change, and absolute referencing, where both stay fixed. Practically speaking, the dollar sign tells Excel which part of the address should not move. Practically speaking, a mixed reference looks like $A1 or A$1. This small detail becomes powerful when you build formulas that need to stretch across rows and columns without breaking.

Quick note before moving on Most people skip this — try not to..

What Is Mixed Referencing in Excel?

In Excel, every cell reference can be relative, absolute, or mixed. Plus, a relative reference such as A1 changes freely when you copy a formula to another cell. An absolute reference such as $A$1 stays fixed no matter where you copy it. A mixed reference combines the two behaviors: one part of the address is locked with a dollar sign, and the other part remains relative.

For example:

  • $A1 locks the column to A, but the row number can change.
  • A$1 locks the row to 1, but the column letter can change.

This means a mixed reference is not fully fixed and not fully flexible. Think about it: it is selectively fixed. That selective behavior is exactly what makes it useful in formulas that need to reference a fixed row heading, a fixed column heading, a tax rate, a commission rate, or a conversion factor while still adapting to different rows or columns.

Why Mixed References Matter

Many Excel users learn relative and absolute references first. Relative references are the default, and absolute references are common when locking a single value such as a tax rate. Mixed references are less familiar, but they solve a specific problem: you need part of a cell address to stay anchored while the rest moves Simple, but easy to overlook..

Imagine you are building a multiplication table. The numbers 1 through 10 appear across the top row and down the first column. If you write a formula using only relative references, copying it across and down will cause both the row and column references to shift. The formula will quickly point to the wrong cells. If you use only absolute references, the formula will keep pointing to the same two cells and will not adapt at all. A mixed reference lets you lock the row heading and the column heading separately so the formula works correctly across the entire table.

That is the core value of mixed referencing in Excel: controlled movement.

Relative, Absolute, and Mixed References Compared

Understanding mixed references is easier when you compare all three reference types side by side.

Reference Type Example Behavior When Copied Best Used For
Relative A1 Both column and row change Formulas that move with each row or column
Absolute $A$1 Column and row stay fixed Locking a single constant value
Mixed $A1 or A$1 One part stays fixed, the other changes Locking a row heading or column heading

A relative reference is like saying, “Use the cell that is in the same position relative to this formula.Which means ” An absolute reference is like saying, “Always use this exact cell. ” A mixed reference is like saying, “Always use this column, but let the row move,” or “Always use this row, but let the column move.

How to Create a Mixed Reference

You can create a mixed reference manually by typing a dollar sign before either the column letter or the row number.

  • To lock the column: type $A1.
  • To lock the row: type A$1.

You can also use a keyboard shortcut. After typing a cell reference in a formula, press F4. Excel cycles through the reference types:

  1. A1 — relative
  2. $A$1 — absolute
  3. A$1 — mixed with the row locked
  4. $A1 — mixed with the column locked
  5. Back to A1 — relative

On some laptops, you may need to press Fn + F4 because the function keys are shared with other controls. On Mac versions of Excel, the shortcut may also require the Fn key depending on your keyboard settings.

The F4 shortcut is faster than typing dollar signs manually, especially when you are editing a long formula. Place the cursor next to the cell reference, press F4 until you see the reference type you need, and continue building the formula.

How Excel Adjusts Mixed References When Copied

The behavior of a mixed reference becomes clear when you copy a formula.

Suppose cell B2 contains this formula:

=$A2*B$1

Now copy the formula from B2 to C3.

  • $A2 locks column A. When copied one column to the right, the column stays A. When copied one row down, the row changes from 2 to 3. The reference becomes $A3.
  • B$1 locks row 1. When copied one column to the right, the column changes from B to C. When copied one row down, the row stays 1. The reference becomes C$1.

So the copied formula in C3 becomes:

=$A3*C$1

This is exactly what you want in a grid-style calculation. The formula always pulls the row label from column A and the column label from row 1, while the calculation moves through the grid And it works..

Practical Examples of Mixed Referencing in Excel

Example 1: Multiplication Table

Create a multiplication table with numbers 1 through 10 in A2:A11 and B1:K1. In cell B2, enter:

=$A2*B$1

Copy the formula across row 2 and down to row 11. The mixed references keep the row labels in column A and the column labels in row 1 anchored while the formula fills the entire table.

Example 2: Commission Rates

Suppose column A contains sales amounts, and row 1 contains commission rates for different sales tiers. You want to calculate commission for each salesperson and each rate. A formula like:

=$A2*B$1

lets you copy the formula across columns and down rows. The sales amount stays anchored to column A, and the commission rate stays

Don't Stop

What's Just Gone Live

Handpicked

You Might Find These Interesting

Thank you for reading about What Is Mixed Referencing In Excel. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home