Cara menggunakan change name scope excel

Named ranges have a property named "scope". Usually, you do not need to adjust this property to use named ranges in Excel. On the other hand, it can become very useful if you have a number of sheets that are identical. The scope concept allows you to use the same name in different worksheets. In this guide, we're going to show you how to make Excel change scope of named range across the workbooks and worksheets, with examples.

Download-Workbook

Let's assume that you have workbook that stores multiple sheets containing data from different years, and you want to use the same formulas for all. What if the "Amount" cell is on B2 in one worksheet and on C25 in another? You do not need to update your formulas for each worksheet, and use the same name (of a named range) for different cells in every worksheet, if you use worksheet scopes.

When the scope is changed from workbook to a worksheet, the named range becomes an element of the worksheet. Other sheets do not recognize the worksheet-scoped named range. Thus, you can name different cells with the same name in every sheet.

Let's see how to make Excel change scope of named range to something different than "Workbook":

Select the cell or range you want to name
Click on Define Name in Formula tab of the toolbar
Give it a name
Change the Scope to a worksheet and save

In our example, we have 4 named ranges with 2 duplicate names. As you can see in the screenshot, each "Lookup_Value" named range refer to different cells.

This flexibility allows using the same formula in all sheets, even with named ranges referring to different locations.

=VLOOKUP(Lookup_Value,PokemonTable,2,0)

Worksheet "Generation I":

Worksheet "Generation II":

A small tip

As we mentioned before in the article, you cannot directly use a worksheet-scoped named range from another worksheet. On the other hand, you can use them by placing the worksheet name before the name, just like a regular reference.

These article summarizes scope concept of named ranges in Excel. If you want to learn more about named ranges, please see our complete guide: Excel Named Ranges.

Tutorial » Excel 2013 » Formula dan fungsi » ...

Ketika Anda bekerja dengan data yang sangat besar, pasti Anda sering kesulitan mencari dimana letak data tertentu disimpan. Salah satu cara untuk memudahkan Anda ketika bekerja dengan data yang sangat besar adalah dengan menentukan Named Range untuk setiap kelompok cell yang menyimpan informasi tertentu. Setelah Anda menentukan Named Range, Anda bisa menampilkan kontennya, mengubah namanya atau menghapusnya.