Names and Name Manager

Data OrganizationModelling & Forecasting7 min readMicrosoft 365 for Windows

The problem

Read this formula and say what it calculates:

=Inputs!$B$3*(1-Inputs!$B$4)*(1-Inputs!$B$5)*(1-Inputs!$B$6)

Now read this one:

=ListPrice*(1-DistFee)*(1-MandDisc)*(1-RebateRate)

They give the same result, a net price of 922.37 per pack. The second one tells you what it does, and anyone who knows pricing can check it without opening the sheet that holds the inputs. That is what a name does: it attaches a meaning to a cell, a range, a constant or a formula, and the meaning travels with every formula that uses it.

The gain is double. Readability is the visible one. Maintenance is the other, because the definition of a name sits in one place, and when it changes every formula that uses it follows. The examples use the companion file W03_Named_Ranges.xlsx. The Inputs sheet holds six named inputs, Calc uses them in three ways, Scenario B shows what happens with a name of limited scope and Names lists the names of the workbook.

Creating a name

There are four ways. The quickest is the Name Box, to the left of the formula bar: select the cell or range, type the name in the box and press Enter. The result is a name that applies to the whole workbook. The second is Formulas > Define Name, which also lets you choose the scope and add a comment explaining what the name is. The third, Formulas > Create from Selection, names several cells at once from the labels next to them, from the top row, left column, bottom row or right column. The fourth is Name Manager, described below.

To use a name, type it in a formula, where Excel completes it after the first letters, or choose it from Formulas > Use in Formula. The same menu has Paste Names and Paste List, which writes a table of all the names of the workbook and what they refer to. The list of names also serves for navigation. Open the Name Box drop-down, or press F5 for the Go To dialog, and a click on a name takes you to its cells, however far away they are.

Select B3 and the Name Box shows ListPrice instead of the address: the cell now has a meaning.

What a name can refer to

A name usually points at a cell or a range. In the file, ListPrice refers to Inputs!$B$3, DistFee to $B$4 and so on. But a name can also hold a constant with no cell behind it. Two are defined in the file: Months is 12 and Thousand is 1000, set in the Refers to box as a plain number. The formula =NetPriceUnitsMonthMonths gives an annual net sales of US$53,128,729, and dividing by Thousand after converting at the rate in FX gives 48,878.4 in thousands of euros. A constant that appears in many formulas is better as a name than as a number typed everywhere.

A name can hold a formula. NetPrice is defined as =ListPrice(1-DistFee)(1-MandDisc)*(1-RebateRate), and the cell Calc!D3 contains only =NetPrice. The name acts as a small function with no arguments. Gross-to-net, the share of the list price that the company keeps, is NetPrice/ListPrice, 71.8%. Table names, described in the article on structured tables, also appear among the names of the workbook.

Whichever form you choose, give the name a comment. The comment shows in Name Manager, and it is the only documentation a constant or a formula name will ever have.

The net price calculated with cell references, with names and with the named formula: all three return 922.37.

Rules for names

Microsoft's page gives the syntax:

The first character must be a letter, an underscore or a backslash. The remaining characters can be letters, numbers, periods or underscores.
No spaces. Use an underscore or a period instead.
A name cannot look like a cell reference, such as Z$100 or R1C1. For the same reason Q1, FY26 and TAX2026 are not available.
The letters C, c, R and r alone are not allowed, because they are shorthand for selecting a row or a column.
A name can have up to 255 characters and is not case-sensitive: Sales and SALES are the same name.

One caution on the sources. Some descriptions of the rules give a slash as a valid first character and a limit of 256 characters. Microsoft's current page says backslash and 255.

A good name is short, speaks the language of the business and follows one convention across the workbook. ListPrice and MandDisc are better than P and Disc2. A prefix for the type helps: tbl for tables, a verb or a noun for the rest. And no name should be reused with a different meaning.

Scope

Every name has a scope. A workbook-level name is recognized on all the sheets of the file. A worksheet-level name is recognized only on the sheet where it is defined, and from other sheets it needs the sheet name in front: Sheet1!Budget. The same name can exist at two scopes, and in that case the sheet-level name wins on its own sheet.

The Scenario B sheet shows the effect. It defines its own RebateRate, 20% in B2. The formula in B4 is identical to the one on Calc, but it returns 838.52 instead of 922.37, a difference of -83.85, because on this sheet RebateRate means something else. That can be useful on purpose. In a model it is more often a trap, since no one reading =RebateRate can see which one is meant. A practical rule is to use workbook-level names only and to avoid worksheet-level ones unless a template must repeat the same structure on many sheets.

Name Manager

Formulas > Name Manager, or Ctrl+F3, lists every name and table name, with the current value, what it refers to, the scope and the comment. From there you can add, edit and delete names. Editing the Refers to box changes the definition everywhere at once. You can sort by clicking a column heading and delete several names together.

The filter is the part most users never find. It narrows the list to worksheet or workbook names, to names with errors or without, and to defined names or table names. The names with errors are those whose reference has gone, for example because the sheet or the range was deleted: they show #REF! and any formula that uses them fails. In a workbook with more than 10 or 15 names, the filter is the fastest way to clean up.

Every name in one list, with its value, scope and comment; the Filter menu isolates the broken ones.

Two limits are worth knowing. You cannot open Name Manager while a cell is in edit mode. And it does not show names that were created in VBA or marked as hidden.

Names and the What-If tools

Names matter particularly with Scenario Manager. A scenario summary report shows the changing cells and the result cells by the name they have, if they have one, or by address if not. A summary of scenarios made of cell addresses such as $B$3:$B$6 is hard to read. A summary with ListPrice and RebateRate is a document.

Common mistakes

Choosing a name that looks like a cell reference or contains a space.
Defining the same name at workbook and sheet level, and then not knowing which one a formula reads.
Deleting the cells a name refers to and leaving #REF! in Name Manager. Check the filter for names with errors before sharing a file.
Defining a name while a cell is selected, using a relative reference. Such a name is relative to the active cell, and it moves when used elsewhere. Define names with absolute references unless the shifting is the point.
Typing a constant in a formula when a named constant would document it.
Leaving names behind from copied sheets, pointing at another workbook.
Giving no comment to a name that holds a formula.