Examples How to use named range scope in Excel

Excel allows creating names with scope limited to a single sheet or the entire workbook. By default, a name is available throughout the entire workbook, which is usually convenient when one name's value needs to be used on various sheets. However, there are cases where you may need to restrict the name's scope to a specific sheet. Excel offers this possibility to create names that can only be accessed from one sheet.



Example of the Need to Restrict the Scope of a Named Range

The primary reason for limiting the scope of named ranges is encapsulation of formulas in Excel. For instance, when copying a sheet, formulas with names should not disrupt the logic of calculation algorithms in Excel tables. It's essential to avoid errors when copying formulas with names.

Let's consider an example of a report on income, expenses, and profit for each quarter of 2023. To calculate the total values, the formulas use references to named ranges in their arguments. The default scope for all named ranges is the entire "Workbook":

We copy a sheet for entering data for 2024 to avoid creating the entire report structure with all its formulas from scratch, we copy the sheet, rename it as "2024", and fill in the new data:

As a result, all total values remain unchanged and produce an incorrect final result.

We need to define names to make it possible to create a book structure that adheres to the following requirements:

Profit is the income minus costs. We generate reports annually, summing up the annual profit. To do this, we subtract expenses from income separately for each quarter and then sum the results.

As long as we are analyzing a sheet with data for a particular year, other years are not of interest to us. For example, the formula:

=SUM(Cost)

associates with the expenses of the current year on the current sheet.

The formula structure won't change for the next year. So why create them from scratch on a new sheet when you can copy the sheet with ready-made formulas?

If the action of the name "Cost" spread to all sheets, we couldn't use it on other sheets without risking errors. And if you create a new name for each new sheet, after copying the sheet, you would have to edit each formula to remove the old names and insert new ones into the arguments.

The best solution in this situation is to limit the scope of the name to a single sheet.

First, create an example report with all the formulas already prepared, even if the data is not yet filled in. This example will serve as a template. For the convenience of data entry, highlight the data entry area with a colored background. Follow these steps:

1. Select the range B2:B5 on the "Sample Report" sheet and choose the "Formulas" - "Define Name" tool. In the resulting "Define Name" dialog, create a name "Income" limited to the scope of a single sheet, as shown below:

2. Create two more names in a similar way: "Cost" for the range C2:C5 and "Profit" for D2:D5. These two names should also have the scope limited to the current sheet and have absolute references in the "Refers to" field.

3. Don't forget to fill the D2:D5 range with the formulas. Also, enter the formulas in cells B6, C6, and D6 to get the total values.

4. Now, copy the "Sample Report" sheet and name the copy "2023." You already know how to copy a sheet from the lesson: Creating, Copying, and Moving Sheets in Excel.

5. Fill the "2023" sheet with data and open the "Formulas" - "Name Manager" tool.

As you can see in the Excel Name Manager, Excel automatically created new names with a scope limited to a single sheet:

Download

Helpful Tip: In this workbook, it's a good idea to protect the "Sample Report" sheet's formulas from being changed. Leave only the input data range B2:C5 available for editing.

Attention! If you copy not the entire sheet but only its contents, new names will be created automatically. However, their scope may extend to the entire workbook. To avoid errors, it's better to copy the entire sheet rather than just its contents.