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

Last updated: September 21, 2023
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.

excel change scope of named range


Change Scope of Named Range in Excel: 3 Ways

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).

excel change scope of named range

  • 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

excel change scope of named range

  • 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).

excel change scope of named range

  • 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.

excel change scope of named range

  • 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.

excel change scope of named range

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.

excel change scope of named range

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.

excel change scope of named range

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.

excel change scope of named range

  • 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

excel change scope of named range

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
excel change scope of named range
  • 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.

Read More: How to Set Value to a Named Range with Excel VBA


Practice Section

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

excel change scope of named range


Download Practice Workbook


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.


Related Articles

Meraz Al Nahian
Meraz Al Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo