Named Ranges in Excel enable you to give one, or a group of cells a name other than the default D3 for a single cell, or E3:F50 for a range of cells.

Let’s look at a simple example of how you might use a Named Range.  Say you had a range of cells containing sales figures, you could highlight the cells and name the range ‘Sales’.

If you then wanted to Sum the range you could simply enter a formula that read =SUM(Sales).  Not only is this easier than entering =SUM( and then highlighting the range and hitting enter, but it’s also more intuitive if you ever have to revisit the spreadsheet weeks or months later.

If you’ve ever endured the frustration of trying to decipher a spreadsheet someone else has created you will love how easy Named Ranges make reading and interpreting formulas.  For example, rather than say =SUM(D5:D600), which requires you to go hunting around to find out what D5:D600 represents, imagine how much quicker and easier it is to read =SUM(Sales).  You instantly know what that formula is doing.

Ok, that was a really simple example.  I’ll list some uses for Named Ranges later in this tutorial and cover the rules, and some advanced features, but first let’s take a look at how to name a range in Excel.

How to Name a Range in Excel

Naming a Range in Excel is actually quite straight forward.

1)      Highlight the range you want to name

2)      Type a name in the Name Box and hit Enter.

You’ll find the Name Box just to the left of the formula bar.  I’ve entered the name ‘NameBox’ in the graphic below, but yours will most likely display the active cell reference.

Excel Name Box for quickly Naming Ranges

Now you’re ready to use your Named Range in your formulas.

Named Range Example

In the image below I have set up a Named Range called ‘Viewers’ which encompasses cells C2:C30.  In cell E2 I’ve started to type a formula =SUM(vi, you can see below the syntax Excel is displaying a list of Named Ranges that match the first two letters of my name (there’s only one in this case).  To accept the first name in the list I can hit the TAB key and Excel will enter the remainder of the name for me, and I can simply hit ENTER to finish the formula.

It’s often the case that you will use the same ranges in multiple formulas, so you can see that a little work setting up some Named Ranges can save you time in the long run when creating formulas.

Excel Named Range Example

Once you’ve set up some Named Ranges you can click on the drop down arrow on the Name Box to see a list of Names.  If you click on a Name Excel will take you to the sheet the range is on and highlight the cells for you.

Named Range Rules:

1)      You can set a Named Range to have the scope of a single Worksheet or the whole Workbook.  The whole Workbook is the default, so if you enter a Named Range directly into the Name Box, as explained above, it will have a scope of the whole Workbook.

This means:

a. If you set the Named Range to have a scope of the whole Workbook you can reference it on any sheet in the Workbook, and you can only use the name once.

b. Or if you set it to have a scope of the Worksheet, you can only reference it on the Worksheet you specify when setting up the Name Range.

One advantage of this is you can have a range with the same Name on each Worksheet.  This would be useful if you had a sheet for each region of your business with quarterly figures on each sheet.  You could then have a Named Range for Qtr1, Qtr2 etc on every sheet.  This would make creating formulas very quick.

To enter a Named Range with the scope of the Worksheet only you need to insert it using the Define Name button on the Formulas tab of the ribbon. When you click on the Define Name button the New Name dialog box will open.

Excel Name Range New Name box
Excel Name Range New Name Dialog Box

Enter your range name in the Name: field, then click on the drop down list on in the Scope: field and select the Worksheet you want the name available in.

What does Scope mean and how will it affect my Named Ranges? Let’s say on Sheet 1 you select a range A1:A10 and give it a name ‘Jobs’, and you then select Sheet 2 as the Scope.  This means when you’re in Sheet 1 you will NOT see the Named Range ‘Jobs’ in the Name Box or be able to use it in any formulas.

But when you’re in Sheet 2 you will be able to pick it from the Name Box list and use it in formulas.  For example, on Sheet 2 you could enter a formula =SUM(Jobs) and it would actually sum the range A1:A10 on Sheet 1.

2)      Named Ranges cannot have any spaces in the name, so for names containing multiple words use UpperCase letters to distinguish the separate words, or use an underscore e.g. Name_Box.  Of course you don’t have to format your names in this way, but I recommend you do as it makes them easier to read.

3)      Named Ranges are not case sensitive. When you’re entering a formula using a Name, say ‘Sales’, you can enter it as =SUM(sales) and Excel will correct your entry to match your Name e.g. =SUM(Sales) when you hit enter.

4)      Named Ranges must be 253 characters or less in length.  Excel will let you enter a name that is 255 characters long, but it won’t let you choose it from the Name Box.

5)      Named Ranges cannot be single letters ‘C’ or ‘R’.  You can however use other single letters, although this is not recommended because using single letters defeats one of the main purposes of making formulas easy to follow.

6)      Once you delete a Named Range in the Name Manager you cannot undo the action using CTRL+Z or the Undo key and any formulas using the name will return a #NAME? error.

7)      If you delete the cells containing the Named Range any formulas referencing the Name will return a #REF! error, however the Named Range will remain in the Name Manager, where you can Edit it and correct the range of cells.

Managing Your Named Ranges

There’ll come a time when you want to edit or delete a Named Range.  To do this access the Name Manager on the Formulas tab of the ribbon. The Name Manager Dialog box will open.

Excel Name Range Name Manager
Excel Name Manager Dialog Box

From here you can Edit and Delete your Named Ranges, or even create new ones.  Remember, once you delete a name you cannot undo that action.

Named Range Advanced Features:

1.       You can set non-contiguous Named Ranges by holding down the CTRL key while you highlight the cells you want to name.

2.       Set Dynamic Named Ranges that update automatically as your data grows using the OFFSET function. That’s a lesson for another day.

3.       Shortcut to create named ranges from a table of data.  If you have your data laid out in columns or rows with headers Excel can interpret this and set up Named Ranges for you automatically.  Simply highlight all of your data and press CRTL+SHIFT+F3 and the following dialog box will open.

Shortcut to Create Excel Named Ranges

Excel will ask you if you want the names created from values in the Top Row, Left Column, Bottom Row or Right Column.  Simply tick your choice(s) and click ok.  Bob’s your Uncle, you now have Named Ranges automatically set up using the names of your Column or Row headers.

Different uses for Named Ranges in Excel:

1)      Formula References; Simplify the creation and retrospective interpretation of formulas by using Named Ranges in your formulas

2)      Multiple Print Areas on one worksheet; Selecting non-contiguous print areas is quick and easy using Named Ranges.  Note: Excel 2007 onwards allows you to set multiple print areas on the Page Layout Tab of the Ribbon under Print Area.

3)      Reduce worksheet clutter with Named Constants. Sounds complicated but it’s not.  A Named Constant is just a fancy name given to values you might use repeatedly in your formulas.  For example, let’s say you’re preparing a budget for the coming year and you want to increase this year’s figures by 5%, you could give the value ’5%’ a name like ‘Markup’ and then you could:

a. Write your formulas like this =C1*Markup.  This is more intuitive when you come to review the spreadsheet later, or for anyone else reviewing it.

b. Update the value globally by editing it once in the Name Manager.  This will then automatically update the formulas.

c. Reduce clutter on your spreadsheet.  Often a helper cell is set up with this key figure in to achieve these benefits, but if you’ve got many of these key figures your spreadsheets can become crowded.

To set up a Named Constant

1)   Click on the Define Name button on the Formulas Tab

2)  In the New Name dialog box enter the name of your value, in our example it’s ‘Markup’

3) Select your scope

4) In the Refers to: field enter your value, in our case =1.05 for a Markup of 5%.

Excel set up Named Constant

To update the value simply edit the Named Constant in the Name Manager

4)      Navigate quickly to a section of your workbook/worksheet

5)      Automate Named Ranges; by inserting a Table (Insert tab, Table) Excel automatically gives the table a name like Table1.  Go to the Name Manager to change the default name to something more meaningful. Note: you can’t make a reference to a cell in a table absolute, so there is a time and a place for Tables.  See my shortcut to create named ranges if the limitation of Tables are causing you hassles.

Don’t forget you can get more free Microsoft Office online training and video tutorials from My Online Training Hub. Just type your name and email address in the form below and click the ‘Get Free Access’ button for instant access to our free online training.

Post a comment and let us know how you use Named Ranges in Excel?

Share the knowledge with your friends and colleagues on Twitter, and Facebook etc. using the shortcuts below.

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 62 comments… read them below or add one }

JERALD July 10, 2014 at 7:08 pm

Dear madam,

am unable to make sum(north). could pl help me below details:

MONTH NORTH SOUTH EAST WEST
JAN 100 110 0
FEB 200 11
MAR 100 20
APR 400 88
MAY 400 10
JUN 700 200 110
JUL 800 10 50
AUG 900
SEP 30 400
OCT 1050
NOV 300
DEC 220

Reply

Mynda Treacy July 10, 2014 at 7:51 pm

Hi Jerald,

Did you name the range that the north data is in? You need to do this first.

Without seeing your file I cannot tell what might be the problem otherwise. You can send us your file via our help desk and we’ll take a look.

Mynda

Reply

Cam March 22, 2014 at 7:06 am

Mynda, first many thanks for all this great stuff on Excel. I started many moons ago on Lotus 1-2-3. In 123, I could create range name labels, which was useful…. perhaps you’ve already explained it, but can you do this is Excel. That is, if I have a column of Divisions, with the leaders in the column to the right, can I create range names of the leaders of the Divisions.?

Not sure if this will make sense to you…. Thanks for any help you can be.

Cam

Reply

Mynda Treacy March 22, 2014 at 7:55 am

Hi Cam,

You can create named ranges quickly from a range of cells. So, if you have divisions in column A and leader names in column B you could highlight the two ranges together then on the Formulas tab of the ribbon in the Defined Names section select ‘Create from Selection’.

This will open the dialog box and you need to check the ‘Left column’ box, then press OK.

You’ll now have named ranges for each division in column A that maps to the leader in column B

I hope that’s what you’re after. If not you could send me an example via the help desk.

Cheers,

Mynda.

Reply

Sarath Chandra November 8, 2013 at 9:53 am

Hi, First of all I appreciate that you encourage/advocate sharing knowledge for free (not many in this world do). Your tips are well explained and easy to follow. May the God bless you!

Reply

Mynda Treacy November 8, 2013 at 3:02 pm

Thank you, Sarath :-)

Reply

Valon February 5, 2014 at 8:37 am

Hi Mynda,
Can you help me please , to find a solution for COUNTIFS formula, but with three ore more criteria in same cell: eg
=COUNTIFS(A1:A10,B1:B10,”Jack”,C1:C10,”apple”,D1:D10,”January”,E1:E10,”day&night”),
Mynda i have problem, to count as number, e1:e10 because the excel doesnt know, day&night. I wanna count when I choose day, to be counted, or when i choose night to be counted. ? Thnks a lot

Reply

Catalin Bombea February 5, 2014 at 2:37 pm

Hi Valon,
Your formula starts with a problem: =COUNTIFS(A1:A10,B1:B10… There is no criteria for the first range.
The syntax of this formula is: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…), up to 127 criterias.
For the other problem, as you can add up to 127 criterias, you can use 2 criterias instead of day&night in a single criteria, like E1:E10,”day”,E1:E10,”night”
Your formula should look like:
=COUNTIFS(A1:A10,”which is criteria 1?”,B1:B10,”Jack”,C1:C10,”apple”,D1:D10,”January”,E1:E10,”day”,E1:E10,”night”)
Catalin

Reply

Valon February 6, 2014 at 6:31 am

Catalin,
Thanks a lot, and I am very appreciate to you.

You helped me a lot,

Reply

Catalin Bombea February 6, 2014 at 3:58 pm

You’re wellcome :)

mano October 7, 2013 at 3:27 pm

hi mynda
your Absolute! thanks so match

Reply

Mynda Treacy October 7, 2013 at 10:26 pm

:) You’re welcome, Mano.

Reply

Nisha Chauhan May 8, 2013 at 3:14 am

hello mam can u pls explain me how to use name manager with the basic i m article in chartered accountant office i want to use it in my office.

Reply

Mynda Treacy May 8, 2013 at 10:09 am

Hi Nisha,

This is all I’ve got on the Name Manager in our free training. If you’d like to sign up for our Premium training it is covered in more depth.

Kind regards,

Mynda.

Reply

Mr. Biker March 31, 2013 at 7:03 pm

It was suggested elsewhere that I use a range in a formula. I never did it before. I used Mr. Google and found this explanation. WOW! Talk about easy and clear.
I’ve been using MS help inside Excel and it’s close to worthless.
This blog is so easy, logical and complete (as I was reading, questions popped into my head and you answered them about 2 seconds later) I’m going to incorporate ranges into my spreadsheets from now on.
I’ve bookmarked your site and cannot thank you enough!

Reply

Carlo Estopia April 1, 2013 at 10:41 pm

Mr. Biker,

You’re welcome,
On behalf of Mynda.

Cheers,

CarloE

Reply

matt March 27, 2013 at 3:20 am

Is there a way to use the name manager to populate a radio box or some sort of list box in VBA?

So I have X defined ranges in my worksheet. I run a macro that pops up a box that allows me to select the range I want to use and then closes the box.

Reply

Carlo Estopia March 27, 2013 at 4:22 pm

Hi Matt,

As much as we would like to help you,
VBA is outside the scope we are supporting right now.

The good news is that we are currently developing VBA
programs for Office Apps.

Cheers.

Carlo

Reply

Madhura February 11, 2013 at 3:01 pm

Hi,

I would like to know how to rename a named range or extend the range by few more cells when I add new values to the range
Eg; Say I have a list of courses
Course1
Course2
Course3
I would have saved these values as a named range- Courses. How do I add more values to this list and still be able to add it under the same named range?

Please help!

Reply

Carlo Estopia February 11, 2013 at 5:58 pm

Hi Madhura,

Judging from the way you presented your problem, I bet you are already familiar
with named ranges.

I know you know this already, Go to Formulas ribbon, Click Name Manager, Click New.
Name this as ‘Mylist’
Now here’s the new part, in the refers to box, paste this formula:

 =Sheet1!$B$2:OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B$2:$B$1000),,1) 

Now add a Data Validatation. In the source box, type:

 =Mylist 

Assumptions:
Sheet1 is the name of your sheet. and your list is in B2:B1000.
So start with your three items and try to add some.

The rest of the story is here:

Everything you need to know is all in this link: DYNAMIC NAMED RANGES

Cheers.

CarloE

Reply

Madhura February 11, 2013 at 6:14 pm

That works awesome!
Thanks CarloE

Cheers!

Reply

Carlo Estopia February 11, 2013 at 6:35 pm

Hi Madhura,

You’re much welcome. It’s Mynda’s blog post and I just learned it from her.
Thank Mynda!

Cheers.

CarloE

Reply

Tim January 17, 2013 at 3:59 am

I would appreciate any help you can provide. I’m using a SUMPRODUCT formula which is summing multiple values that are present within a Named Range. This works great until the Named Range values are not contiguous at which point I get the #VALUE! error. I’m not sure why this is happening. The formula is the same. Should the fact that the Named Range is non-contiguous matter? Thanks.

Reply

Carlo Estopia January 22, 2013 at 10:10 pm

Hi Tim,

I would appreciate it more if you can send your file through Help Desk with some sample data and your formula you’re trying to work out.

Anyway…

I just want to clarify if what you mean is that the Named Range is composed of non-contiguous ranges or if you mean that the Named Range is contiguous but the values in it are not contiguous.

Anyway, if you mean the former, I would say Excel really does return an error for that (I would suggest for a workaround if you can send me your file). If you mean the latter, As long as the Named Ranges referenced are of the same dimension I don’t think there will be an error.

Sincerely,

CarloE

Reply

Nathan November 28, 2012 at 2:44 am

Thank you for the excellent tutorial.
Nathan

Reply

Mynda Treacy November 28, 2012 at 8:21 am

Cheers, Nathan :)

Reply

Gans92 December 17, 2012 at 4:26 pm

Hi Mynda,

I have an excel sheet which I am trying to load on a sharepoint page using excel web parts.

My excel sheet has an option button, and also certain fields that are coloured conditionally. I try to create a named range so that I could load the sheet on sharepoint. But I find that the option button and other controls are not included in the named range. Is there any way in which I can ensure that my excel controls are included in the excel named ranges? Also any idea why the colours in the coloured fields are not appearing?

Reply

Mynda Treacy December 18, 2012 at 8:19 am

Hi Gans92,

I’m sorry, I’m not familiar with Sharepoint and it’s limitations.

Kind regards,

Mynda.

Reply

Mohini October 11, 2012 at 10:48 pm

Dear Sir,
The column is extremely helpful. ITs helped me to understand the Excel with named range which I am using in SpreadsheerGear. IF you can help me to apply the alternate rowstyle to the name range would really very helpful.
Thanks in advance!
Mohini

Reply

Mynda Treacy October 11, 2012 at 11:01 pm

Hi Mohini,

I’m glad we could help :)

I’m sorry, I don’t know what you mean by ‘apply the alternate row style to the name range’.

Kind regards,

Mynda.

Reply

Mohini October 12, 2012 at 2:55 pm

Hello Mynda,
Thank you so much for the quick reply.
Here is my problem : Actually I am using Speadsheet gear for Exporting the reports. In this I am preparing the Names (range of the cells). And these names I am referring with their names in the code to populate the data. I am able to populate the data in the names as well as able to apply the formats. But I need to apply the alternate rowstyles for data rows where alternate rows will be colored in gray. I can do this in excel to the table or set of the cells by using conditional formatting but I need to apply the same to the name which I am creating in the excel template. Please help!
Your help will be really appreciated!!
Thanks in Advance !
Mohini

Reply

Mynda Treacy October 12, 2012 at 4:31 pm

Hi Mohini,

I don’t think you can…every time you enter the named range in the ‘Applies to’ field Excel converts that range to cell references again.

There is some discussion about it on a Microsoft forum here. There is also a VBA alternate solution.

I’m sorry I can’t be of more help.

Kind regards,

Mynda.

Reply

Ovi October 11, 2012 at 6:45 am

Hello,
About name range, can you help me with this:
I defined a name range (Products) with 2 columns and 40 rows (=Sheet2!$B$2:$C$41). Cells are all strings. I use this definition as a source for a combobox. My problem is that in combobox I see only first column of defined name range (column B from 2 to 41). It is possible to see all range in combobox selection ?
Thanks

Reply

Mynda Treacy October 11, 2012 at 7:54 pm

Hi Ovi,

I don’t think so! I suggest you create a helper column (let’s say column D) that joins columns B & C together and refer your combo box to the concatenated values in column D.

Kind regards,

Mynda.

Reply

Ovi October 12, 2012 at 2:36 am

Hi Mynda,
Thanks for your quick answer. That’s was my thinking too. But, my first effort was to use sheets as they are… Almost because of end users, you now… I think that I can use a formula for the new column, something about D=B+C, so when B or C changes, also D changes. Any other suggestions ?
All the best,
Ovi

Reply

Mynda Treacy October 12, 2012 at 10:44 am

Hi Ovi,

Yes you can concatenate the text in columns B & C like this:

=B1&C1

For more on using the ampersand symbol to concatenate.

Kind regards,

Mynda.
P.S. Oh, yes…I know what you mean about end users ;)

Reply

eric shiver October 2, 2012 at 8:22 am

How can I print individual rows 1 row at a time, 1 row per printed page
without having to call out each row to print one at a time manually. I do this function every date up to 50 rows of data and it is time consuming. I would like to just state a range and walk away while it is being printed.
HELP!

Reply

Mynda Treacy October 2, 2012 at 6:23 pm

Hi Eric,

I’d set my margins so small that only one row fits on each page. I hope that helps.

Kind regards,

Mynda.

Reply

Adam January 8, 2013 at 7:17 am

Hi Eric / Mynda
I’ve had this issue before, the easiest way I have found is to setup page breaks, for the number of rows you want printed, then save the sheet after use, save as page breaks 5 rows, or something like that, the next time you need this, special paste the data (as values), then excel will remember your setting
Hope it help

Hi Mynda
Excellent site, thank you for you guides, very helpful indeed

Reply

Mynda Treacy January 8, 2013 at 8:59 am

Cheers, Adam. Thanks for sharing your idea.

Mynda.

Reply

T. Sathish Kumar September 28, 2012 at 3:40 pm

Hi, I tried many things by seeing your website i’m working in a school we use to maintain a data base of the student in excel sheet, it consist of assessment marks of all subjects and converting to grade. when i’m converting it to grade i found i cannot able to convert absentees which have mentioned using “AB” this has to written in the formula. I convert marks in to grade using this formula =IF(E8>=45,”P”,”F”) in the same formula i have to include “AB” if they are absent,.

FOR YOUR CLEAR UNDERSTANDING: I have to check whether they are present or absent, if they are present i have to check whether they are above 45 if satisfies then it has to convert it into “P” else “F”.
Please help me in this so that you

Reply

Philip Treacy September 29, 2012 at 2:31 pm

Hi,

You can use a nested IF statement for this.

For example, where A1 contains whether they’re present or absent, and B1 contains their marks:

=IF(A1=”Present”,IF(B1<45,”F”,”P”))

Kind Regards,

Phil.

Reply

karen September 27, 2012 at 8:06 pm

thumbs up!!! that’s exactly what I need, very detailed and straightfoward!Really appreciate!

Reply

Mynda Treacy September 28, 2012 at 7:52 am

You’re welcome, Karen :)

Reply

Olivia August 21, 2012 at 3:42 am

I am trying to improve my Microsoft proficiency and this tutorials are very helpful. Thanks so much for making them available. I will pass them on.

Reply

Bilal April 19, 2012 at 9:26 pm

really good to learn

Reply

Kurian March 25, 2012 at 5:43 pm

I have two sheets (namely Site and Sumary)
Site
Name SITE Salary budgeted month 30-04-12 31-05-12 30-06-12
A ABC 1000 30-06-2012 1000 1000 1000
B BCA 1100 31-05-2012 1100 1100
C ABC 1200 30-04-2012 1200
D ABC 1300 30-06-2012 1300 1300 1300

In summary sheet, i would like count the number of persons by month with by Site.

I tried countif funtion but doesnot give result. which function i have to use here.
Kinldy help me

Reply

Mynda Treacy April 25, 2012 at 8:39 pm

Hi Kurian,

I apologise for the late response to your question. We were abroad at the time and unfortunately your question was missed.

I presume you want to count the number of persons based on the data in the ‘budgeted month’ column. If so you could use a formula like this:

=COUNTIFS($B$2:$B$5,”ABC”,$D$2:$D$5,E$1)

Where column B contained ‘Site’ and column D contained ‘budgeted month’ and cell E1 contained ’30/04/2012′. You could then copy this across to columns F and G.

I hope that helps. If not please let me know.

Kind regards,

Mynda.

Reply

Kurian March 25, 2012 at 5:32 pm

I would like to learn more in Countif function (in two sheets)

Best Regards
Kurian

Reply

Dave March 22, 2012 at 8:14 pm

First – Great article

I would like to add the following notes:

Long range names can also cause issues as a lot of people don’t know that you can re-size the name box (in Excel 2007 & 2010) so you can read them.

In earlier versions of Excel (which many companies still use) it was not possible to manually resize this box and you needed to use a VBA procedure to auto resize it – which also may not be practical given many companies security restrictions on running macros.

I would also suggest that constants which are often one or more letters can be identified using a descriptor such as const_ for constant, or calc_ for a calculation etc. which allows users to still use terms they are familiar without having to invent new names for something.

Thanks.

Reply

Mynda Treacy March 24, 2012 at 6:31 am

Thanks, Dave. Great tips. I didn’t know you could re-size the name box either!

Kind regards,

Mynda.

Reply

Thomas February 9, 2012 at 5:40 am

I created an Excel chart using macros written in Excel 2003. A function of this macro is the create Chart01, copy it to Chart02 then update the data point references in Chart02. However, when the code initializes the copy of Chart01 I receive several “Name Conflicts” messages. I quickly click through the “Use in destination sheet” messages, and everything turns out okay. My question is: What is the cause of this, and how do I correct it? I’ve been using this code for several years, and never had a problem in 2003. HELP!

Reply

Mynda Treacy February 10, 2012 at 9:56 am

Hi Thomas,

Excel 2007/2010 doesn’t like the word ‘chart’ to be used in a named range used with charts. You will need to rename your named ranges that containt the word ‘chart’.

Go to the Formulas tab > Name Manager and edit them.

Also because Excel 2007/10 has so many more rows and columns you need to be careful not to abbreviate your named range so that it is the same as a cell reference. e.g. CH1

I hope that helps.

Kind regards,

Mynda.

Reply

Jeff January 20, 2012 at 12:39 am

Is there any benefit (or hinderance) to having a global named range that is listed in the Name Manager multiple times, but with different scopes?

In other words, I have a Data tab as my data source. I then have a Tab1 that references the DataSet range on the Data tab. When I copy Tab1 and make a Tab2 (Tab3, Tab4, etc.), each time I create the new tab it creates a new DataSet range in the Name Manager, but with a different scope. So I end up with something like this:

DataSet Refers to: Data Scope=Workbook
DataSet Refers to: Data Scope=Tab1
DataSet Refers to: Data Scope=Tab2
DataSet Refers to: Data Scope=Tab3
etc.

Does that effect speed/performance at all? Should I delete all the individual names and just have one that says Scope=Workbook? Or does having each one make calculation faster? Or doesn’t it matter either way

Thank you

Reply

Mynda Treacy January 20, 2012 at 11:16 am

Hi Jeff,

Great question.

Having multiple named ranges that are the same but with different scopes doesn’t cause performance issues. The named ranges on their own don’t do any calculations (and therefore don’t require any memory) it’s only when you use them in formulas, and even then it depends on what formulas you use them in plus the size of the data range as to whether they may cause performance issues. So having multiple named ranges the same as opposed to different names makes no difference to Excel’s performance.

Just to be clear on how the named ranges work: Say the named range ‘DataSet’ on Tab1 refers to the cells on Tab1 (say A1:A10), and it has a scope of ‘Workbook’, this means you can use the named range in a formula anywhere in the workbook. (It doesn’t refer to the same cell range on all sheets in the workbook.)

And, the named range ‘DataSet’ on Tab2 refers to the cells on Tab2 (say A1:A10), but you can only use that named range in a formula on Tab2 because the scope for that named range is ‘Tab2′.

You cannot have a named range that refers to cell ranges on multiple tabs/sheets.

So, what may be an issue is the confusion caused from having multiple named ranges with the same name. You would be better off having a separate named range for each tab, say Tab1_DataSet, Tab2_DataSet etc. and each having a scope of the workbook.

Another reason for having different named ranges is if you ever wanted to do a summary page that summed the named ranges for each tab you would need to have a unique named range for each tab with the scope ‘workbook’ for each. e.g. =SUM(Tab1_DataSet,Tab2_DataSet,Tab3_DataSet,Tab4_DataSet).

I hope that makes sense.

Kind regards,

Mynda.

Reply

Peter November 16, 2011 at 11:04 pm

I found this very interesting. I am a trainer helping teachers understanding Excel and its components.
Is there something for Excel 2010 – Pivot Tables?

Reply

Mynda Treacy November 17, 2011 at 10:12 pm

Hi Peter,

Thanks for your feedback.

PivotTables in Excel 2007 and 2010 are almost identical. 2010 offers some additional functionality like slicers and larger data set capabilities, but the way you create a PivotTable and set options etc. is unchanged from 2007.

You can read my PivotTable tutorial here.

Kind regards,

Mynda.

Reply

Ranju July 14, 2011 at 7:25 pm

Hi Teacher,
I want to use IF, COUNTIF,COUNTIFS,SUMIF,SUMIFS functions in a single cell. Please guide me with some examples.

Reply

Mynda July 14, 2011 at 8:06 pm

Hi Ranju,

Why would you want to do that!? Sure I can come up with a scenario but it may not apply to your needs. What would help me serve you better is if you were to give me an example of what you’re trying to achieve and I can offer you a solution, that may or may not need to use all those functions in a single cell.

I look forward to hearing back.

Kind regards,

Mynda.

Reply

Palliana February 22, 2011 at 3:13 am

HI, I am learning all about Name Manager and your blog was very insightful. I have a question which I hope you might be able to help me. I have a massive list of names that are assigned to a specific category…Doe, John = Sunshine, Doe, Mary = Sunshine for example. This works great when I add them in Name manager and test it in my formula. My question is when it is not Doe, John or Doe, Mary I want to label this differently = Rain for example. So, without having to type all the names which are thousands is there a way I can describe that in one cellin Name Manager and it understands if not doe, john or doe, mary then = rain. Help!

Reply

Mynda February 23, 2011 at 9:37 am

@Palliana

Thank you for your question. I’m having a little difficulty understanding exactly what you mean but I’ll have a guess.

I think you’re saying you’ve got a list of names e.g. Doe, Mary and Doe, John that belong to a named range called Sunshine and other names that belong to a different named range called Rain.

But not all of the names are in Excel, and therefore you’d need to type them in so you can tell Excel what named range they are in….but you want a quicker option.

If this is the case it may be possible to use a formula (like an IF statement) to perform whatever calculation you want without the need for a named range in the first instance, but without knowing what the end result is I can’t advise you on what this formula should be.

However, if you want/need to use named ranges unfortunately you will have to import, or type the names into Excel and give the categories a named range. There’s no ‘formula’ type of logic in the Name Manager that will allow you to tell it that all ‘these’ names are in the Sunshine category and any ‘others’ are in the Rain category.

If your names (Doe, John etc) are in another system are you able to import them into Excel?

I hope this helps. Please let me know if I’ve misunderstood your question.

Reply

Pat Decasas January 2, 2011 at 7:01 am

I know this is really boring and you are skipping to the next comment, but I just wanted to throw you a big thanks – you cleared up some things for me!

Reply

Previous post:

Next post: