What is a mixed reference in Excel? Definition and when to use one

They exist for a job neither of the other reference types can do: one formula, typed once in the corner of a rectangle, dragged in two directions, and correct in every cell it lands in.

One coordinate pinned, one free

Exceljet's definition is the whole idea in a line — a mixed reference is "a reference where part of the reference is absolute and part is relative", with $A1 locking the column, A$1 locking the row, and $A$1:A2 locking only the first cell of a range. Microsoft's framing is procedural: a dollar sign before either coordinate "fixes either the column or the row (for example, $B4 or C$4)".

The trick is to read the dollar sign as the word always, attached to the coordinate that follows it rather than to the cell:

• $A2 — always column A, whatever row this copy lands in.
• A$2 — always row 2, whatever column this copy lands in.

That is the entire decision. A dollar sign is applied not to a reference but to one of its two coordinates, and the coordinate left bare goes on behaving exactly like a relative reference.

A two-way grid, worked

Put unit prices down column A from A2, and quantity tiers along row 1 from B1. Every cell needs price × tier, and every cell a different pair — so the price reference must travel down but never sideways, and the tier reference sideways but never down. Type =$A2*B$1 once, in B2, and fill it across and down:

Read down a column: the $A never moves and the row number tracks the fill. Read across a row: the $1 never moves and the column letter tracks the fill. Ablebits describes the two halves in exactly those terms — you "lock the column coordinate because the formula should always multiply the original numbers in column A", while "the row coordinate is relative since it needs to change for other rows." The payoff is that "all the calculations are performed with a single formula, which changes properly for each row and column where it is copied."

The same shape carries any two-input grid: a margin grid with costs down column A and prices along row 1 is =(B$1-$A2)/B$1 in B2 and nothing else, and a sensitivity table or a shipping matrix of weight against zone is the same corner formula again. Neither alternative works. Written with absolute references throughout, =$A$2*$B$1, the rectangle fills with one number repeated; written with nothing locked, =A2*B1, the top-left cell is right and the rest walk diagonally away from the data they were meant to read.

Choosing which half to lock

Ask what must not follow the copy. If the reference has to keep coming from one column, the dollar goes on the letter; if from one row, on the number. In a grid formula the two references end up mirror images — one $A2, one B$1 — so if both carry their dollar in the same position, one of them is wrong.

Locking the wrong half is the characteristic mixed-reference bug, and it rarely produces an error message. It produces a plausible grid of wrong numbers:

GoSkills, whose guide defines the form as one where "part of the reference is fixed, either the row or the column, and the other part is relative", adds advice worth the ten seconds it costs: "before assigning the dollar sign to a column or row, it's recommended to review the calculations and logic on hand to avoid errors." The cheapest check is the opposite corner — fill the rectangle, then read the bottom-right cell and confirm it names the two inputs you expect.

Typing the dollar signs by hand is not the intended workflow. The two mixed forms sit in the middle of the F4 cycle: put the cursor inside a reference and press F4 repeatedly to walk through the absolute and mixed variants and back round to relative. Google lists the Sheets equivalent as "Absolute/relative references (when entering a formula): F4", or Fn + F4 on a Mac, and the syntax is identical in both applications.

One use lives outside formulas entirely: a conditional-formatting rule that colours a whole row pins the column and lets the row float, so every cell is judged by the same test.

How Formula Foundry handles this

Formula Foundry does not fill a grid for you. There is no drag handle in the sidebar and no bulk write — the add-on composes one formula, inserts it into the selected cell, which for this pattern is the cell that matters, and leaves the copying to the spreadsheet. What it improves is the moment before that. =$A2*B$1 and =A$2*B$1 are one character apart and look identical at a glance in the formula bar; in the editor they do not, because the formula opens on multiple lines, indented along its nesting, with functions, ranges, constants and named references each in their own colour.

For a grid formula somebody else built, the AI assistant explains it step by step in plain English, and the visual builder renders the call tree as a form in which every argument sits in its own named field. What none of that does is check your logic. Formula Foundry has no calculation engine for your workbook: it parses, formats, explains and translates, never evaluating the sheet, so it cannot tell you the dollar is on the wrong coordinate — only the numbers in the rectangle can. It also does nothing for conditional formatting, whose rules live in the spreadsheet's own dialog.

Two things reduce how often you write the corner formula from scratch. The snippet library saves a formula by name and inserts it in one click, and on team plans the saved set is shared, so the version everyone starts from is the one that was checked. Global @@variables handle the constants that would otherwise be typed into the formula, leaving the dollar signs to do only the job they are good at. The features overview covers the editor, the builder, the snippet library and the variables panel.

FAQ