Mixed References in Excel: $A1 vs A$1 Explained

A mixed reference in Excel locks only half of a cell address: either the column or the row. $A1 keeps the column fixed, and A$1 keeps the row fixed. Mixed references let one formula fill a whole grid, which is why they turn up in job tests and real reports. This guide explains how each type behaves when you copy it, with a worked example you can type in.

What is a mixed reference?#

A cell reference like A1 has two parts: the column letter and the row number. A $ sign in front of either part locks it, so it doesn't change when you copy the formula. There are four combinations:

The rule to remember: the $ locks whatever comes right after it. In $A1, the $ is before A, so the column is locked. In A$1, it is before 1, so the row is locked.

If relative and absolute references are new to you, read relative vs absolute references first. Mixed references are the third option after those two.

How $A1 and A$1 behave when you copy#

Here is what happens to each mixed reference when a formula moves one cell.

$A1 (column locked):

• Copied down one row, it becomes $A2. The row follows.
• Copied right one column, it stays $A1. The column is locked.

A$1 (row locked):

• Copied down one row, it stays A$1. The row is locked.
• Copied right one column, it becomes B$1. The column follows.

So $A1 is for inputs listed down the left side in column A. A$1 is for inputs listed across the top in row 1.

Worked example: a discount grid with one formula#

Say you want sale prices for three products at three discount rates. Prices go down column A, and rates go across row 1:

In B2, type:

=$A2*(1-B$1)

The result is 95 (100 × 0.95). Now fill the formula across to D2 and down to row 4, or copy B2 and paste it into B2:D4. Every cell picks the right price and the right rate:

Look at C3: its formula is =$A3*(1-C$1). The price still comes from column A, and the rate still comes from row 1. That is the whole idea.

What goes wrong without mixed references#

Try the same grid with the wrong references and you can see why mixed ones matter:

• All relative, =A2*(1-B1): copied to C2, it becomes =B2*(1-C1). It now multiplies a discounted price instead of the original, so the numbers are wrong.
• All absolute, =$A$2*(1-$B$1): every cell returns 95, because every copy points to the same two cells.
• Swapped, =A$2*(1-$B1): the price is stuck on row 2 and the rate on column B, so most copies pick the wrong inputs. Some even point at other results in the grid, and the numbers become nonsense.

When a filled grid looks strange, click a cell in the middle and read its formula. If the inputs are not the ones you expect, a $ is in the wrong place.

How to type mixed references with F4#

You don't need to type the $ signs. While you edit a formula, put the cursor in a reference and press F4. Each press cycles to the next form:

A1 → $A$1 → A$1 → $A1 → A1

So for A$1 press F4 twice, and for $A1 press it three times. On a Mac, press fn+F4 or ⌘T. The guide to what F4 does in Excel covers the other job F4 does when you are not editing.

How to check which references you need#

Before you type the first formula of a grid, ask two questions about each input:

1. When I copy across, should this input move to the next column? If not, lock the column with $ before the letter.
2. When I copy down, should this input move to the next row? If not, lock the row with $ before the number.

For the price in column A, the answers are "no" across and "yes" down, so it becomes $A2. For the rate in row 1, they are "yes" across and "no" down, so it becomes B$1. Two quick questions, and the grid works the first time.

Other places mixed references help#

Grids are the classic case, but mixed references appear in other common tasks:

• Running totals: =SUM($B$2:B2) keeps the start of the range fixed while the end moves down. Filled down, it adds one more row each time.
• Conditional formatting rules: a rule like =$D2="Overdue" colours the whole row, because the column is locked to D while the row changes.
• Lookup tables across and down: a two-way table of rates often needs $A2 for the row label and B$1 for the column header.

If a formula has to stay exactly the same wherever you copy it, mixed references aren't the answer. See how to copy a formula without changing its references for that. For one fixed cell, such as a single tax rate, a full absolute reference is simpler; the guide to the dollar sign in Excel explains it.

Practise it#

Mixed references click once you have filled a grid yourself and watched every cell come out right. Try the discount-grid exercise on this page, then open the relative vs absolute references lesson to fill formulas down, see them break, and fix them with $ and F4 in a live spreadsheet. Each step is checked instantly.