Microsoft Excel · Calculation tools, not function references · Beginner

Create and Maintain Named Ranges in Excel

Manage named cells and ranges in Excel with a complete practice example, expected results and recovery checks.

Official documentation reviewed; example logic independently checked; native Excel execution not performed · Editorial standards

Quick answer: Use Formulas → Define Name to give a fixed cell or range a readable workbook-level name, then check its address and scope in Name Manager. A name is a reference, not a copy of the data and not automatically a growing Excel table.

Version and platform: Microsoft 365 Excel for Windows desktop; Mac, Excel for the web and Excel 2013 interfaces not covered. Use a disposable workbook for the exercise.

Prepare a small, auditable example

In a blank workbook rename the worksheet Inputs. Enter Rate in A1 and 0.08 in B1. Enter Amount in A3 and 100, 200, 50 in A4:A6. Reserve C1 for a calculated result. The intended calculation is the sum of the three amounts multiplied by the rate: 350 × 0.08 = 28.

The deliberately fixed range makes the effect of adding another amount visible. Do not create an Excel table for this exercise; table expansion is a separate workflow.

Create names with explicit scope

Select Inputs!B1 and choose Formulas → Define Name.
Enter FeeRate as the name. Leave Scope at Workbook and verify Refers to is =Inputs!$B$1. Confirm with OK.
Select A4:A6 and define BaseAmounts, also with Workbook scope. Verify =Inputs!$A$4:$A$6 before confirming.
In C1 enter =SUM(BaseAmounts)*FeeRate. It should return 28.
Open Formulas → Name Manager. Inspect Name, Value, Refers To and Scope for both entries. A descriptive comment such as “Practice fee assumption” makes the purpose easier to recognize later.

The dollar signs make these addresses absolute. Microsoft warns that typing relative references into a name can produce a reference relative to the cell where the name is used. Selecting the intended range when defining it is safer for this fixed-input exercise. Avoid spaces in names; FeeRate and BaseAmounts are deliberately simple and do not resemble cell addresses.

Change the data, then change the range

Change B1 to 0.10. C1 should become 35 without editing the formula. Restore B1 to 0.08, then enter 150 in A7. C1 should still be 28, because BaseAmounts stops at A6. This is not a recalculation failure.

In Name Manager select BaseAmounts, choose Edit, and change Refers to to =Inputs!$A$4:$A$7. Confirm and inspect C1: 500 × 0.08 = 40. Save the workbook as XLSX, reopen it, and verify both named addresses and the result. The name has now changed meaning for every formula that uses it, not just C1.

Scope and recovery

Workbook scope makes a name available throughout the workbook; worksheet scope makes it local to a particular sheet. Inspect the Scope column before assuming two identically spelled names are interchangeable. For this exercise, create only the workbook-scoped names. If you chose the wrong scope, first identify dependent formulas before replacing the name; do not delete a name casually in a real model.

If C1 shows a name error, check spelling and whether the name exists. If it returns an unexpected but plausible number, inspect Refers To for an off-by-one range or a relative address. A missing new row usually means the defined range needs maintenance, not that SUM is broken. Name Manager cannot list every hidden or VBA-defined name, so its visible list is not a complete security audit.

For a genuinely growing dataset, see the existing PivotTable source and refresh guide; its Excel 2013 scope is separate from this modern name-management walkthrough.

Official references

Documentation snapshots retrieved 20 October 2026. These instructions and example checks are documentation-based; no native Excel run or application screenshot is claimed.