Replace Range Names in Formulas: How To

Yes, you can replace range names with cell references in Excel formulas, and vice versa, using the Name Manager and the “Apply Names” feature. This article explains how to swap named ranges for their underlying cell references (like changing Sales to B2:B100) and how to reapply names to existing formulas, including the exact steps, common pitfalls, and what happens to your formulas when you do.

Why You Might Want to Replace Range Names

Named ranges make formulas readable (=SUM(Sales) is clearer than =SUM(B2:B100)), but there are times you need to see the raw references. When you share a workbook with someone who doesn’t understand your naming conventions, or when you’re auditing a complex model, seeing =B2*C2 instead of =Price*Qty can make the logic easier to trace. Also, if you’re copying formulas to another workbook that doesn’t have those names defined, the formulas will break unless you replace the names with direct references first. Conversely, you might want to replace references with names to make a formula more self-documenting for future users.

How Excel Stores Names and References

When you create a named range, Excel stores a name-to-reference mapping in the workbook’s Name Manager. The name points to a specific cell, range, formula, or constant. When you type that name into a formula, Excel substitutes the name with its defined value during calculation. The formula bar shows the name (e.g., =SUM(Sales)), but internally Excel tracks that Sales means Sheet1!$B$2:$B$100. Replacing a name with a reference means editing the formula to use the actual cell address instead of the name. Excel provides a built-in tool to do this automatically across a selection or the whole sheet.

How to Replace Names with Cell References

Excel has a dedicated feature for this, tucked away in the Name Manager. It’s not obvious, but it works reliably.

1. Select the range of cells containing formulas you want to change. If you want to replace names in the entire worksheet, select a single cell (like A1) and the process will apply to the whole sheet.
2. Go to the Formulas tab on the ribbon.
3. Click Define Name (in the Defined Names group) and choose Apply Names… from the dropdown.
4. In the dialog box, you’ll see a list of all defined names. Select the name(s) you want to replace with references. You can hold Ctrl to select multiple names.
5. Click OK. Excel will replace every occurrence of the selected name within the selected formulas with its absolute reference (e.g., =SUM(Sales) becomes =SUM(Sheet1!$B$2:$B$100)).

Note that this only works if the name is used as a reference in the formula, not if it’s used as a function name (like SUM) or a constant.

How to Replace References with Names

The reverse process—turning =SUM(B2:B100) into =SUM(Sales)—is also possible, but it’s less automated. You can do it manually by editing each formula, but for a large sheet, that’s tedious. A better approach is to use the Apply Names feature again, but this time you need to ensure the name is defined and then use a different method:

1. Define the named range (e.g., Sales for B2:B100).
2. Select the cells with formulas.
3. Go to Formulas > Define Name > Apply Names.
4. In the dialog, select the name you want to apply.
5. Check the box “Use row and column names” if you want Excel to try to use structured references, but for simple ranges, it’s usually better to uncheck it.
6. Click OK. Excel will replace any references that exactly match the named range with the name.

This method is finicky—it only replaces references that exactly match the range, including absolute/relative modifiers. For example, if your formula uses =B2:B100 (relative) and your name refers to $B$2:$B$100 (absolute), Excel may not replace it unless you adjust the reference style. A simpler, more reliable method is to use the Find and Replace feature (Ctrl+H) to replace the reference text with the name, but you must be careful to avoid partial matches (e.g., replacing B2 inside B20).

Common Problems and How to Avoid Them

Replacing names can cause unexpected issues if you’re not careful:

• Absolute vs. Relative References: The Apply Names feature always inserts absolute references (with $ signs). If your original formula used relative references (e.g., =A1*B1), replacing a name will change the behavior when you copy the formula to other cells. For example, if you have a named range Price for $B$1, and you use =A1*Price, applying the name will turn it into =A1*$B$1, which is fine. But if you had =A1*B1 and you apply a name for B1, it becomes =A1*$B$1, which will NOT change relative to the row when copied. This is often not what you want.
• Names on Other Sheets: When a name refers to a range on a different sheet, the applied reference will include the sheet name (e.g., Sheet2!$A$1). This is correct, but it can make formulas longer and harder to read.
• Partial Matches: The Apply Names feature can sometimes replace a name that is part of a larger reference. For example, if you have a name Tax for B2 and a formula =B2*C2, applying the name might turn it into =Tax*C2 (good), but if you have a name TaxRate for B3, it might replace TaxRate with B3 but leave Tax alone—or worse, replace the first three characters of TaxRate with B2? No, Excel is smarter than that; it only replaces whole names. However, if you have a name like B for a column, it could replace the column letter in a reference like B2? No, Excel only replaces when the name is a complete token (separated by operators, commas, parentheses, etc.).
• Names in Data Validation or Conditional Formatting: Replacing names in formulas does NOT affect names used in data validation rules or conditional formatting. You must edit those separately.

When to Avoid Replacing Names

Named ranges are a powerful feature for maintainability. Replacing them with hard-coded references makes your formulas harder to read and maintain. If you change the structure of your data (e.g., add rows), a named range automatically expands (if defined as a table or with a dynamic formula), but a direct reference like B2:B100 will not. So, before you replace names, consider whether you really need to. If you’re doing it for performance, note that the performance difference is negligible in modern Excel. If you’re doing it for sharing, consider whether you can just include the names in the shared workbook.

Alternatives to Replacing Names

If your goal is to understand what a name refers to, you can simply click on a cell with the name in a formula and press F2 (or click in the formula bar) to see the reference highlighted on the sheet. Or use the Name Manager (Formulas > Name Manager) to see all names and their references. If you want to convert formulas to use names for readability, you can use the Apply Names feature as described above, but it’s often easier to just type the name manually when you write new formulas. For auditing, you can use the Trace Precedents feature (Formulas > Trace Precedents) to visually see which cells a formula depends on, without changing the formula itself.

Frequently Asked Questions

Can I replace a name with a reference in a single formula without affecting others?

Yes, simply edit the formula directly. Click on the cell, go to the formula bar, and replace the name with the actual reference (e.g., change =SUM(Sales) to =SUM(Sheet1!$B$2:$B$100)). This only affects that one cell.

Why does the Apply Names feature not replace some names?

The Apply Names feature only replaces names that are used as direct references to cells or ranges. If a name is used in an array formula, or if it’s a constant (like =5), it may not be replaced. Also, if the name is used as a function (like SUM), it won’t be replaced. Additionally, if the name is not in the list you selected, it won’t be touched.

What happens to named ranges when I delete a sheet?

If a named range refers to a sheet that is deleted, the name becomes invalid (it shows #REF! in the Name Manager). Formulas that use that name will return #REF! errors. You must redefine the name or replace it with a valid reference.

Can I replace names in formulas on multiple sheets at once?

Yes, the Apply Names feature works on the selected cells. If you select cells on multiple sheets (by grouping sheets), it will apply to all of them. However, be careful: if the name refers to a sheet that is not active, the applied reference will include the sheet name, which may be undesirable.

Does replacing names affect the result of the formula?

No, if done correctly, the result should be identical. The only difference is the visual appearance of the formula. However, if the name was defined as a relative reference (e.g., B1 without $), replacing it with an absolute reference ($B$1) could change the result if the formula is copied to other cells, because the relative reference would have adjusted, but the absolute one won’t.

Is there a way to replace all names in a workbook at once?

There is no built-in one-click “replace all” for the entire workbook. You can select all cells in a sheet by clicking the triangle at the top-left corner, then use Apply Names, but you’d have to repeat for each sheet. Alternatively, you can use a VBA macro to loop through all sheets and replace names, but that requires coding knowledge.

Related

• How to Check Whether Your Mobile Network Is Affected By an Internet Shutdown
• Refresh Rate & Input Lag for a Second Esports Monitor
• Energy vs Power Signals: Definitions, Equations & Examples