WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 525 reviews for our classroom and online training
How to refer to range names with worksheet and workbook scope
Range names can have either worksheet or workbook scope - this blog shows how to make sure that you're referring to the right range names!

Posted by Andy Brown on 01 November 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Referring to range names with different scope

As so often, this blog was prompted by a question from someone on a recent course.  To understand the question, it will help to step back a bit and explain what the problem is!

Range names can have worksheet or workbook scope

Suppose that following an expensive and wide-ranging Wise Owl survey you create the following spreadsheet, showing young people's pet hates:

Copying worksheet

Someone is now copying this sheet, by holding down the CTRL key while clicking and dragging on the worksheet tab.


The formula in the selected cell is =AVERAGE(Rating), where the range name Rating has been given to cells C5:C7.  You now copy this worksheet, by clicking and dragging on the tab with the CTRL key held down, and make changes to get:

Old people's pet hates

The formula is still the same, but it's now referring to the figures on the current worksheet.


What's happened is that you have two copies of the range name called Rating:

Number Scope Notes
1 Workbook The original range, on the Young sheet.
2 Worksheet The range on the copied Oldies sheet.

You can see this much more clearly in Name Manager:

Name Manager icon

Click on this icon in the Formula tab of the Excel ribbon to see your range names.


You can now see exactly which blocks of cells each range name refers to:

The two range names created

The range names have different scope.


Changing the default range name used

This brings me back to my original question: how to change the default range name used? 

You can refer to a locally scoped range name on a different sheet by prefixing the range name with the sheet name and an exclamation mark:

Referring to a local range name

Here the formula used is =AVERAGE(Oldies!Rating), to make sure that we pick up on the version of Rating on the Oldies sheet, and not the default one with workbook scope.

Conversely, if you want to use a range name with workbook scope, you must prefix it with the name of the workbook:

Using workbook scoped range name

Here the formula used is =AVERAGE('Generation gap.xlsx'!Rating), because we want to refer to the range name originally created, rather than the local instance created for the Oldies worksheet.

Perhaps the easiest way to avoid this problem happening is to avoid creating duplicate range names in the first place!

This blog has 1 thread Add post
16 Jun 18 at 18:53


Thought you might be interested to hear about the following named range bug in Excel!

We know each worksheet name has to be unique. Any attempt to name a worksheet that already has that name wil be met with an error prompt.  The same applies to named ranges - sort of!

Named ranges have two scopes, worksheet and workbook.

Suppose I created a named range, say myname, at the Sheet1 level, referring to cell A1.  Obviously if I try to create another named range called myname also at the Sheet1 level, the error prompt appears.

However, if you hide all named ranges in VBA, like this:

Dim nm As Name
For Each nm In Thisworkbook.Names
    nm.Visible = False
Next nm

Then if you attempt to create a named range called myname (and make it point to cell A2 of Sheet1) it will work! In doing so, it removes the original myname!

18 Jun 18 at 09:28

I never knew that!  Thanks.