Professional Workflow›Extra· 35 min read

Named Ranges & Mixed References

Give a range a friendly name like TaxRate so your formulas read like plain English — and master the mixed reference for copying across a grid.

What you will learn

Create and use a named range
Write a formula that reads in plain words
Use a mixed reference ($A1 or A$1) to copy across a table

From cryptic to clear

You already lock a cell with the dollar sign, like $E$1 for a tax rate. It works, but =B2*$E$1 does not say what it does. A named range lets you give a cell or range a friendly name — like TaxRate — and then use that name in formulas. The same formula becomes =B2*TaxRate, which anyone can read.

Creating a name

Naming a cell takes seconds. Here is the full process:

Click the cell you want to name — say E1, which holds your tax rate 0.1.
Click in the Name Box (the small box on the far left, just above column A, that normally shows the cell address).
Type the name TaxRate and press Enter. The cell is now named.
From now on, typing TaxRate in any formula means “the value in E1”.

Now compare the two ways of writing the same total-with-tax formula:

The same calculation: with a locked cell, then with a named range

=B2*$E$1
=B2*TaxRate

Note: Output: If B2 is 200 and the tax rate is 0.1, both show 20. The two formulas do exactly the same thing. But =B2*TaxRate explains itself, and a name is automatically absolute — it always points at E1 no matter where you copy it, so you never need dollar signs on it.

Names make formulas readable — =Price*Quantity beats =B2*C2.
Names are absolute by default, so copied formulas never break.
One named cell can drive a whole workbook: change TaxRate once and every formula updates.

Watch out: Names cannot contain spaces and cannot look like a cell address. Use TaxRate or Tax_Rate, never Tax Rate or A1. Stick to letters, numbers and underscores, and start with a letter.

Mixed references: lock one part, free the other

Earlier you met fully absolute $A$1 (both locked) and relative A1 (both free). The in-between case is the mixed reference, where you lock only the column ($A1) or only the row (A$1). This is the secret to filling a whole grid — like a times-table — from a single formula.

Picture a times table. The numbers 1–3 sit across row 1 (in B1, C1, D1) and down column A (in A2, A3, A4). Each inside cell should multiply its row header by its column header. The clever formula in B2 is:

Lock the column for the row-header (A) and the row for the column-header (1)

=$A2*B$1

Note: Output: In B2 (row header 1, column header 1) it shows 1. Now the magic: copy this ONE formula across and down the whole grid. $A2 keeps the column locked on A (so it always reads the row header) but lets the row move. B$1 keeps the row locked on 1 (so it always reads the column header) but lets the column move. In C3 the formula becomes =$A3*C$1 — exactly right — filling the entire table from a single cell.

Reference	What is locked	When you copy
`A1`	Nothing (relative)	Both column and row move
`$A$1`	Both column and row	Never moves
`$A1`	Column A only	Row changes, column stays A
`A$1`	Row 1 only	Column changes, row stays 1

Tip: Press the F4 key repeatedly while a reference is selected in a formula to cycle through the four forms: A1 → $A$1 → A$1 → $A1 → back to A1. It is far faster than typing dollar signs.

Q. You name cell E1 as Discount. What is the main advantage of writing =B2*Discount instead of =B2*$E$1?

Answer: A named range makes formulas readable and is absolute by default, so copies always point to E1 — no dollar signs needed and the formula reads like plain English.

✍️ Practice

Put a tax rate in E1, name it TaxRate, and write =B2*TaxRate for a column of prices.
Build a 3×3 times table using the single mixed-reference formula =$A2*B$1 copied across and down.

🏠 Homework

Create a small invoice where the VAT rate lives in one named cell (VATRate). Use the name in every line’s tax formula, then change the rate once and confirm every line updates.