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.
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).
- 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
- How to Use Dynamic Named Range in an Excel Chart
- How to Name a Column in Excel
- How to Paste Range Names in Excel
- How to Name a Group of Cells in Excel
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.
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.
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
- Excel VBA to Create Named Range from Selection
- How to Delete All Named Ranges in Excel
- How to Edit Named Range in Excel
- How to Find a Named range in Excel
- How to Navigate to a Named Range in Excel
- How to Display Named Range Contents in Excel
- How to Create Dynamic Named Range in Excel
- Create Dynamic Named Range with VBA in Excel
- How to Use Named Range in Excel VLOOKUP Function
- Excel INDIRECT Function with Named Range
- Excel Reference Named Range in Another Sheet
- How to Ignore Blank Cells in Named Range in Excel
- Excel VBA to Loop Through Named Range
- How to Check If Named Range Exists with Excel VBA
- Define Names with the Create from Selection Tool in Excel
- Excel VBA to Delete Named Range