Named ranges in Excel

A reference like $F$1 is correct but mute. Nothing about it tells a reader that F1 holds the commission rate. Named ranges fix that. A name is a human-readable alias for a cell or range that you can use in any formula on the workbook. Give F1 the name CommRate and the payout formula becomes =B2*CommRate. That's clearer to read, and it's also automatically absolute. A defined name always points at the same place, so it behaves like $F$1 without a dollar sign in sight, and it fills down perfectly.

How do you create a named range in Excel?

There are three ways to create a name, in rising order of ceremony:

1. The Name Box is the fastest. Select the cell or range and click into the Name Box, the little box to the left of the formula bar that normally shows the active cell's address. Type CommRate and press Enter. Done.
2. Create from Selection is best for tables of labels. Select a block where one row or column holds the labels and the neighbours hold the values. Then choose Formulas ▸ Defined Names ▸ Create from Selection (keyboard Ctrl+Shift+F3), tick "Top row" or "Left column," and Excel names each value range after its adjacent label in one move.
3. Define Name gives the most control. Formulas ▸ Defined Names ▸ Define Name (keyboard Alt, M, M, D) opens a dialog where you set the name, the scope, and an optional comment. The scope is either workbook-wide or restricted to one sheet.

How do you manage names, and what are the rules?

Manage them all in the Name Manager (Ctrl+F3). There you can rename, redefine and delete names, and see every name and what it points at, which matters most when you audit someone else's file. A few naming rules to remember. Names take no spaces, so use Comm_Rate or CommRate. A name can't look like a cell address, so Q1 is illegal as a name, since it is a cell. And scope matters, because a sheet-scoped name is invisible from other sheets unless you qualify it. Then there is the standing hazard. If you name F1 as CommRate and later delete column F, the name survives but now points at #REF!, and every formula using it breaks. You find and repair that in the Name Manager.

What is LET, and when should you use it?

LET is the natural next step. It solves a different readability problem, the inside of a single long formula. LET lets you declare names that exist only within one formula, assign each a value, and then write a final calculation that uses them. The syntax is a sequence of name/value pairs followed by one final calculation:

=LET(name1, value1, name2, value2, …, final_calculation)

Take an invoice line: quantity in A2, unit price in B2, an 8% volume discount, then 18% GST on the discounted amount. Written the naive way you would repeat the sub-total expression, and any reader has to decode A2*B2 three times. Written with LET:

=LET(gross, A2*B2, afterDisc, gross*(1-0.08), afterDisc*(1+0.18))

Read it top to bottom. gross is quantity times price, afterDisc is gross less the 8% discount, and the formula returns afterDisc grossed up by 18% GST. With A2 = 320 units and B2 = ₹1,250, gross = ₹400,000, afterDisc = ₹368,000, and the result is ₹434,240. You can trust that number because you can read the steps.

LET brings two gifts. The first is legibility, because the calculation reads like a short paragraph with each step named. The second is efficiency. Excel computes each named value once and reuses it, where a repeated sub-expression would be evaluated several times. That matters when the repeated piece is an expensive lookup far more than when it's a cheap multiply. Here's a rule of thumb. The moment you find yourself typing the same sub-expression twice inside one formula, or nesting so deep you lose the thread, reach for LET. Named ranges name things across the workbook, and LET names things inside one formula. You'll use both constantly, and in the LAMBDA section you'll see that LET plus LAMBDA lets you name an entire reusable function.