Lock a Named Range in an Excel Formula?

Usually, you do not need to add dollar signs to lock a named range in an Excel formula: a name such as TaxRate stays the same when you copy a formula. To make sure it really points to a fixed cell or range, check that its definition uses absolute references, such as =Sheet1!$B$1, rather than relative references.

What “locking” a named range means

In Excel, locking a reference means keeping it pointed at the same cell or range when a formula is copied to another location. With ordinary cell references, dollar signs do that job: $B$1 fixes both the column and row, while B$1 fixes only the row and $B1 fixes only the column.

A defined name is different. In a formula, you refer to the name itself, not to its address. For example, a formula can use TaxRate instead of $B$1. You do not write $TaxRate; dollar signs are not the way to anchor a name. Whether the name points to a fixed location depends on the reference stored in its definition.

Most names created by selecting a cell or range and defining a name refer to absolute addresses. That is why a formula using a named range commonly continues to refer to that range after you fill the formula down or copy it elsewhere. The important exception is a name defined with relative references.

How named ranges behave when you copy formulas

Suppose a worksheet has an input value in B1, and the name TaxRate refers to that cell. With the value in A2 and the rate in B1, enter this formula in C2:

With the rate in B1 set to 8% and the amount in A2 set to 200, enter this in C2. The result is 16.

=A2*TaxRate

When you copy the formula from C2 to C3, the ordinary reference A2 adjusts to A3, but the name TaxRate still refers to its defined target. This is a useful combination: the row-specific amount changes, while the shared rate remains fixed. The same principle applies to names referring to multi-cell ranges, such as a fixed list used in a sum or lookup.

For a range named Sales, with the name referring to B2:B20, use this formula to total the range. If those cells contain values that add up to 5,400, the formula returns 5,400 wherever you enter it.

=SUM(Sales)

Copying that formula to another cell does not turn the name into a different range when the name’s definition is absolute. The formula text remains simple, while the Name Manager stores the address behind the name.

Check the name’s definition in Name Manager

In Excel for Microsoft 365 on Windows, open Formulas > Defined Names > Name Manager. Select the name and inspect the Refers to box. A fixed reference to a cell or range includes dollar signs before the column letters and row numbers. For example, a fixed range on Sheet1 appears as =Sheet1!$B$2:$B$20.

To make a name fixed, select it in Name Manager, choose Edit, and correct the reference in Refers to. Keep the sheet name if the target is on a different worksheet. Choose OK, then close Name Manager. These are the main things to check:

Both coordinates are fixed: use $B$1 for one fixed cell or $B$2:$B$20 for a fixed range.
The correct worksheet is specified: check that the name points to the intended sheet, not a similarly named location elsewhere.
The name points to the intended cells: confirm the first and last cells in a multi-cell range.
The scope is appropriate: check whether the name applies throughout the workbook or only on a particular worksheet.

You can also select a cell that uses the name and look at the formula bar, but the formula bar shows the name, not necessarily its underlying address. Name Manager is the reliable place to inspect or edit that definition. The ribbon labels are similar in current Excel for Mac, though their placement can vary slightly. Excel for the web has more limited name-management options than the desktop app, so use desktop Excel if you need to inspect or edit a definition and cannot find the relevant controls online.

Set up a fixed named range

If you are creating a name rather than repairing an existing one, select the cell or range you want to name and choose Formulas > Defined Names > Define Name. Enter a clear name, select the desired scope, and review the Refers to field before confirming. For a fixed range, its address should include dollar signs.

For example, suppose a worksheet has product prices in B2:B20 and you want to name that list Prices. In Name Manager, the fixed definition should refer to =Sheet1!$B$2:$B$20. If the sheet name contains spaces, Excel uses apostrophes around it in the address. When you select a range before creating a name, Excel generally records the selected cells as absolute references; nevertheless, checking the definition prevents a mistaken relative reference from causing surprises later.

Names make formulas easier to read, but the name should describe what the cells represent. A name such as QuarterlyPrices is more informative than Range1. Avoid names that resemble cell addresses, and keep names consistent with the data they refer to. A meaningful name is especially helpful in a large workbook where the underlying cells may be on another sheet.

Absolute names and relative names

Excel allows a defined name to contain relative references. A relative reference has no dollar signs, so it can shift according to the position from which the name is used. This behavior can be intentional, but it can also make a name appear to change when you copy a formula.

For example, a name whose Refers to definition contains an unanchored address such as =Sheet1!A1 is not the same as one referring to =Sheet1!$A$1. The first definition can behave relatively; the second identifies a fixed cell. If a name is supposed to represent one shared input, use absolute references. If it is designed to refer to a location relative to each formula, retain relative references only when that behavior is deliberate and documented.

A useful way to diagnose an unexpected shift is to check the name definition before changing the formula. Adding dollar signs to unrelated references in the formula will not fix a name that is defined incorrectly. Conversely, if the name is absolute, adding a dollar sign to its use in the formula is unnecessary.

Workbook scope, worksheet scope, and tables

When a name is created, its scope determines where Excel recognizes it. A workbook-scoped name can be used across worksheets. A worksheet-scoped name is available on its own sheet and may coexist with a workbook-scoped name of the same spelling. If both exist, the local worksheet name can take precedence on that sheet. This can make two formulas that look identical refer to different cells.

If a name seems to resolve to the wrong range, inspect its scope in Name Manager and look for duplicate names. You can rename a worksheet-scoped name or use a workbook-scoped name deliberately, but do not assume the name is unique just because its spelling appears only once in a formula. Scope is separate from whether the address is absolute: a name can be workbook-scoped and still contain relative references, or worksheet-scoped and contain absolute references.

Excel tables offer another option for formulas based on structured data. A structured reference such as a table column name adjusts as table rows are added, which is often desirable for expanding lists. That is different from locking a fixed cell range: a table reference is designed to follow the table’s data. Choose a named range for a clearly defined reusable address, or a table reference when the data is a growing, organized list.

Common problems and how to fix them

If a formula changes unexpectedly after copying, first distinguish between the formula’s ordinary cell references and the name’s definition. A relative reference like A2 is expected to shift when copied. The name should remain pointed at its intended target if it is defined with absolute references. Check both parts rather than assuming the name is responsible for every change.

The named range moves: open Name Manager and add dollar signs to the reference in Refers to if it is meant to be fixed.
The formula returns an unexpected value: verify the name’s scope, worksheet, and range boundaries. A duplicate local name may override a workbook-level name.
The formula shows an error: confirm that the name still exists and that its reference points to valid cells. Renaming or deleting a sheet can affect references.
The name appears to be unavailable: check for spelling differences and scope. A worksheet-scoped name may not be available from a formula on another sheet.
A list grows over time: consider using an Excel table if the formula should include new rows automatically rather than remain restricted to a fixed address.

Names are not a substitute for checking formula results. After editing a definition, verify a formula that uses it, then copy the formula to another relevant row or sheet and confirm that both the intended shifting references and the fixed named range behave correctly.

Frequently Asked Questions

Do I put dollar signs around a named range?
No. Use the name as written, such as =SUM(Sales). To keep its target fixed, make sure the name’s definition uses absolute cell references in Name Manager.

Why does my named range change when I copy a formula?
The name may be defined with relative references, or a same-named worksheet-level definition may be taking precedence. Check Refers to and the scope in Name Manager.

Can I lock only the row or only the column in a named range?
Yes. The name’s definition can use a mixed reference, such as =$B1 or =B$1. Those definitions intentionally allow one part of the reference to adjust. Use a fully absolute reference such as =$B$1 when both row and column must stay fixed.

Will a named range expand when I add new data?
Not necessarily. A name referring to a fixed address such as B2:B20 remains limited to that address. If new rows should be included automatically, an Excel table and its structured references may be a better fit.

Can I use the same name on different worksheets?
Excel permits worksheet-scoped names, including names that share a spelling with a workbook-scoped name. Because scope affects which definition a formula uses, inspect Name Manager if a formula seems to resolve to an unexpected range.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.