How do you change the scope of a defined name in Excel?

Is it possible configure Excel (maybe a check box Option available), so that, when I create a named range using the "shortcut" in the formula bar, the scope is set by default to th

How do you change the scope of a defined name in Excel?

Is it possible configure Excel (maybe a check box Option available), so that, when I create a named range using the "shortcut" in the formula bar, the scope is set by default to the active worksheet, instead of to the whole workbook?

Unfortunately no, it's not possible to change that default in Excel.

However, Define Name exists as a command in its own right. This means that one thing you can do is to add a custom ribbon button to go directly to the Define Name dialog.

enter image description here

This saves navigating to the Name Manager and drilling down within that to get to this dialog.

enter image description here

Changing default setting of the SCOPE OF NAMED RANGE to specific worksheet is looking difficult.

A Name can be either global to a workbook or local to a worksheet. The standard method is create a Range name as a global names, but yes you can set the SCOPE OF NAMED RANGE to any specific worksheet.

Method 1:

  • While creating Named Range, you can pick a worksheet from the list, then Excel will restrict it's scope to the sheet only.
enter image description here

Method 2:

You may use this VBA code to change the SCOPE of the NAMED RANGE. Sub ChangeScope() Application.Calculation = xlManual Names("MyName").Delete Range("Sheet1!$A$1:$A$10").Name = "Sheet1!MyName" Application.Calculation = xlAutomatic End Sub

N.B.

1. This single line can do it also.Sheet1.Names.Add("Myname", Sheet1.Range("A1:A10"))

2. In this code you may adjust cell references and NAME of the Range as you need.

3. Code Change Scope can be used as Workbook Open event (calling Macro) also.

Video liên quan