How to Change the Scope of a Named Range in Excel – 3 Methods

This is the sample dataset.

excel change scope of named range

Method 1. Using the Define Name Feature to Change the Scope of a Named Range

Steps:

  • Go to Formulas >> Define Name.
  • A dialog box will open. Enter AmmunitionStore(here) in Name.
  • Click the upward arrow as shown below.

  • Select B5:B13 in gunName (the name of the sheet, here).
  • Click the downward arrow as shown below.

excel change scope of named range

  • Select the sheet name (gunName) in Scope.
  • Click OK.

  • Enter the following formula in any cell. Here, F7.
=AmmunitionStore

excel change scope of named range

  • Press ENTER button and you will see the range in B5:B13.

Note: You cannot use this range in another worksheet in your Excel file.

Read More: How to Paste Range Names in Excel


Method 2 – Changing the Scope of a Named Range in the workbook

Steps: 

  • Go to Formulas >> Define Name.
  • In the dialog box, enter guntype (here) in Name.
  • Select Workbook in Scope.
  • Click the upward arrow as shown below.

excel change scope of named range

  • Select B5:B13.
  • Click the downward arrow as shown below.

  • Click OK.

excel change scope of named range

  • Go to Formulas >> Name Manager.
  • Select guntype in Name
  • Enter the following formula in Refers to:.
=FILTER(guns!$B$5:$B$13,guns!$D$5:$D$13="Auto")
  • Click Close.

excel change scope of named range

The FILTER function searches for the automatic types in D5:D13, compares and filters the types with the guns in B5:B13.

  • Enter the following formula in E5.
=guntype

  • Press ENTER and you the output in column E.

excel change scope of named range

You can use this formula to copy the automatic guns to other sheets in your file.

  • Open a new sheet and enter the formula in any cell. Here,B5.

‘auto guns’ is the new sheet.

  • Press ENTER button and you will see the output in column B.

excel change scope of named range

Read More: How to Name a Column in Excel


Method 3 – Applying a VBA to Change the Scope of a Named Range in Excel

Steps:

  • Select B5:D13 and name it Guns_Class.

  • Open Visual Basic in the Developer Tab.

excel change scope of named range

  • In Insert, click Module.

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

The code deleted the Name range Guns_Class and replaced it with a new range Guns_Name using the Name method.

  • In Macros, click Run.

  • Enter the following formula in any cell.
=Guns_Name
excel change scope of named range

  • Press ENTER and you will see the selected range in F5:G13.

Read More: How to Delete All Named Ranges in Excel


Practice Section

Practice here.

excel change scope of named range


Download Practice Workbook


 

Related Articles


<< Go Back to Named Range | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo