Named ranges have a property named "scope". Usually, you do not need to adjust this property to use named ranges in Excel. On the other hand, it can become very useful if you have a number of sheets that are identical. The scope concept allows you to use the same name in different worksheets. In this guide, we're going to show you how to make Excel change scope of named range across the workbooks and worksheets, with examples.
Let's assume that you have workbook that stores multiple sheets containing data from different years, and you want to use the same formulas for all. What if the "Amount" cell is on B2 in one worksheet and on C25 in another? You do not need to update your formulas for each worksheet, and use the same name (of a named range) for different cells in every worksheet, if you use worksheet scopes.
When the scope is changed from workbook to a worksheet, the named range becomes an element of the worksheet. Other sheets do not recognize the worksheet-scoped named range. Thus, you can name different cells with the same name in every sheet.
Let's see how to make Excel change scope of named range to something different than "Workbook":
- Select the cell or range you want to name
- Click on Define Name in Formula tab of the toolbar
- Give it a name
- Change the Scope to a worksheet and save
In our example, we have 4 named ranges with 2 duplicate names. As you can see in the screenshot, each "Lookup_Value" named range refer to different cells.
This flexibility allows using the same formula in all sheets, even with named ranges referring to different locations.
Worksheet "Generation I":
Worksheet "Generation II":
A small tip
As we mentioned before in the article, you cannot directly use a worksheet-scoped named range from another worksheet. On the other hand, you can use them by placing the worksheet name before the name, just like a regular reference.
These article summarizes scope concept of named ranges in Excel. If you want to learn more about named ranges, please see our complete guide: Excel Named Ranges.
