How to Change Scope of Named Range in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

This article will show you some easy process of how to change scope of named range in Excel. If you want to use a range of data in your excel sheet or workbook, you need to change the scope of that range. We are going to use the following dataset where we have different kind of guns, their class and their type.

Table of Contents hide

Change Scope of Named Range in Excel: 3 Ways

1. Using Define Name Feature to Change Scope of Named Range for Worksheet

2. Changing Scope of Named Range for the Whole Excel File

3. Applying VBA to Change Scope of Named Range in Excel

Practice Section

Conclusion

1. Using Define Name Feature to Change Scope of Named Range for Worksheet

Say you want to use the column where we store the names of the guns in any place of your Excel sheet. You may want to copy the column and paste it anywhere in your excel sheet. But if you want to use this column multiple times then it would be tiresome to use the copy and paste command over and over again. So we are going to use the Define Name command from the Formulas Tab. Let’s make the discussion below.

Steps:

First, go to Formulas >> Define Name.
A dialog box will show up. Type AmmunitionStore in the Name You can give a different name if you want.
Then click on the marked icon in the dialog box (Shown in the figure).

Select the range B5:B13. Here gunName is the name of the sheet.
Click on the marked icon (pointed by Click Here text box).

Now, choose the sheet name (gunName) from the Scope section as you want to use this range of names only in this worksheet.
Click OK.

You are all set now. Type the following formula anywhere in the sheet. In this case I’m putting it in cell F7.

=AmmunitionStore

Hit the ENTER button and you will see the range of the names in B5:B13.

Thus you can change the scope of the named range. You can use this range anywhere in the gunName sheet.

Note: You cannot use this range in another worksheet in your Excel file. It’s applicable only for the sheet you are working on.

Similar Readings

2. Changing Scope of Named Range for the Whole Excel File

Suppose you want to store the names of the guns which are Automatic Rifles and use them in any worksheet of your whole Excel file. Let’s go to the discussion quickly.

Steps:

Firstly, go to Formulas >> Define Name. A dialog box will appear. Type guntype in the Name You can give any name if you want.
Select Workbook from Scope
Click on the marked icon (pointed by Click Here text box).

Now select the range B5:B13.
Click on the marked icon (directed by Click Here text box). Here guns refers to the sheet

Now click OK.

After that, go to Formulas >> Name Manager.
Select guntype from the Name
Type the following formula in Refers to: section

=FILTER(guns!$B$5:$B$13,guns!$D$5:$D$13="Auto")

Click Close.

Here the FILTER function searches for the automatic types in D5:D13, then compares the types with the guns in cells B5 to B13. After that it filters out the auto rifles which refer to the guns sheet.

Type the following formula in cell E5.

=guntype

Hit the ENTER key and you will see all the automatic type guns in column E.

You can use this formula to copy the automatic guns in any sheet of your file.

Let’s open a new sheet and type the formula in any cell.

Here, ‘auto guns’ is the new sheet. We type the formula in cell B5.

Hit the ENTER button and you will see all the automated weapons in column B.

Thus you can change the scope of a specific named range for any Excel sheet of your file.

3. Applying VBA to Change Scope of Named Range in Excel

We can also use Visual Basic Application (VBA) to change the scope of the named range. Suppose you want to use both Guns and Class columns in a sheet. Let’s see how we can do this using VBA.

Steps:

Select the range B5:D13 and give this range a name Guns_Class.

Open Visual Basic from the Developer Tab.

Open a Module from Insert Tab in the Microsoft Visual Basic for Applications

I used the following code in the Module.

Sub Change_Scope_Name()
Application.Calculation = xlManual
Names("Guns_Class").Delete
Range("vba!$B$5:$C$13").Name = "vba!Guns_Name"
Application.Calculation = xlAutomatic
End Sub

Here, we deleted the Name range Guns_Class and replaced it with a new range Guns_Name using the Name method. If you refer to the Guns_Name range in the vba sheet, you will see the desired range.

Run Macros in the sheet you are working on.

After that, type the following formula in any cell of that sheet.

=Guns_Name

Hit ENTER and you will see the desired range throughout F5:G13.

By following this approach, you can easily change the scope of a named range for an Excel sheet.

Practice Section

Here I am giving you the dataset of this article so that you can practice these problems on your own.

Download Practice Workbook

Change Scope of Name Range.xlsm

Conclusion

The article is dedicated to explaining some tactics to change the scope of a named range in Microsoft Excel. I hope you benefit from my article. If you have any questions or ideas or feedback regarding this problem, please feel free to leave them in the comment box. This will help me enrich my upcoming article.

How to Change Scope of Named Range in Excel (3 Methods)

Change Scope of Named Range in Excel: 3 Ways

1. Using Define Name Feature to Change Scope of Named Range for Worksheet

2. Changing Scope of Named Range for the Whole Excel File

3. Applying VBA to Change Scope of Named Range in Excel

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply