Excel Named Ranges 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.
Inserting Excel Named Ranges
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.
 |
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.
 |
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.
 |
 |
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.
 |
 |
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.
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.
 |
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%.
 |
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.
Don’t forget you can get more free Microsoft Office online training and video tutorials from My Online Training Hub.
Post a comment and let us know how you use Named Ranges in Excel?
Please share the knowledge with your friends and colleagues on Twitter, and Facebook etc.
Ted says
without a workbook to download, I got lost pretty quickly
Mynda Treacy says
Hi Ted,
The workbook download link is towards the top of the post. It’s under the second heading.
Mynda
Lisa says
Thank you! This is exactly what I was needing for my Data Validation lists, as I was out of character space for the items, and we share the workbook, so can not use tables. This worked great, now will not have to update 12 sheets for just adding one new type. Just update one list on a separate worksheet and it is done!
Mynda Treacy says
Wonderful! Glad it will help 🙂
Mynda
Siôn says
Thanks for the explanations and tips on uses!
Rakan says
You are the best
Thank you
Lynne says
Excellent! I’ve used Excel for years and never even realized there was a Formulas tab. Wow,can’t wait to subscribe:-)
Mynda Treacy says
Glad we could help, Lynne 🙂
walter says
Thank you very clear just i have a problem.
I have a formula like sum(first) ,name first = a2:h2
when i copy to another cell down i have the same result because the sum retain the name and don’t change the rows(to 3)
I have no $ at my name and now i have at row 2 sum(first) and at row 3 sum(first) ?
thank you
Catalin Bombea says
Hi Walter,
A Defined Name is not aware of the locations where this name is used.
You have to write the formula in a different manner, like:
=SUM(OFFSET(first,0,0)) (or =SUM(OFFSET(first,ROW(A1)-1,0)) for a dynamic formula)
To sum the next row below first, change the offset row to 1:
=SUM(OFFSET(first,1,0)) (the dynamic formula based on ROW function will do that without manual adjustments)
Catalin
Syed Najmul Hassan says
VERY HELPFUL INDEED
Mynda Treacy says
Thanks, Syed. Glad we could help 🙂
Thomas says
Hi Mynda,
Great site as others have noted.
I`ve used dynamic ranges in the past using the offset functionality, it`s bulky cumbersome and can`t be used in a dropdown list IIRC.. but this new table feature that I`ve just found out about thanks to you does pretty neat stuff indeed…
So the problem is that I want to ignore the header for my data validation dropdown list… is that possible
Thanks,
Thomas
Mynda Treacy says
Hi Thomas,
You can use a dynamic range using OFFSET for your data validation source, assuming that’s what you mean by ‘dropdown list’, as opposed to a Combo Box form control.
Another way to build a dynamic named range is using INDEX, as described here.
However, the easiest way is to use an Excel Table as the source for your data validatopm list. There are 3 approaches to this as explained here, and they all ignore the header.
I hope that helps.
Mynda
Chris says
In older versions of Excel (circa 2003), a named range could be saved in a user’s Personal.xls workbook, which referred to a range in another workbook (i.e., a range not actually in Personal.xls). Once in place, this named range could be referenced by formulas in any other workbook, and as long as the user was working on the same PC/network account with the Personal.xls open/running in the background, the formulas would work. I am now using Excel 2010 and I cannot seem to achieve this same functionality. Personal.xlsb will allow me to save the named range, but my other workbooks cannot “see” or use the name unless I redefine the range, which defeats the point of defining the range in the first place. Any tips?
Mynda Treacy says
Hi Chris,
I haven’t tried it but are you prefixing the name with the workbook name e.g.:
=personal.xlsb!your_named_range
Mynda
Chris Jordan says
Yes, the range is defined in my Personal.xlsb workbook with the full network path, file name, worksheet name, and cell range. Here is the actual ‘Refers to:’ string copied straight from Excel:
=’G:\PLANNING\XFILES\PLANNING\ZIP_Codes\[SD_County_zip_code_summary.xlsx]Master_Zip_Listing’!$C$3:$AT$193
Mynda Treacy says
Ok, and let’s say the name for this reference string in Personal.xlsb is ‘Master’?
Are you referencing it with:
=Personal.xlsb!Master
And is your Personal.xlsb workbook open?
Mynda
Chris Jordan says
Referencing it as you suggested (Personal.xlsb!Master) works! Thank you so much! However, I am disappointed that I now need to reference it in this fashion – in the older versions of Excel (like 2003), it was not necessary to include the “Personal.xlsb!” portion – simply including the name (Master) in the formula produced the desired result. Thanks again for your help.
Mynda Treacy says
Hi Chris,
Glad it’s working now, albeit with a bit more effort required.
Kind regards,
Mynda
R. Charan Pagan says
There are some excellent tips in here! Thanks.
Mynda Treacy says
Thank you!
Jonathan Mills says
Great summary book….every one who uses Excel should have this.
Mynda Treacy says
Cheers, Jonathan 🙂
Denis says
YOU ARE DEFINITELY GREAT!
by an Italian in China
Mynda Treacy says
Thanks, Denis 🙂
JERALD says
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
Mynda Treacy says
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
Cam says
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
Mynda Treacy says
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.
Sarath Chandra says
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!
Mynda Treacy says
Thank you, Sarath 🙂
Valon says
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
Catalin Bombea says
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
Valon says
Catalin,
Thanks a lot, and I am very appreciate to you.
You helped me a lot,
Catalin Bombea says
You’re wellcome 🙂
Carlos Mario says
AFAIK
you can use 2 criterias instead of day&night in a single criteria, like E1:E10,”day”,E1:E10,”night”
doesn’t work in COUNTIFS because it considers the two criteria in an AND relation.
I think you must try with SUMPRODUCT , something like
SUMPRODUCT((A1:A10=”which is criteria 1?”)*(B1:B10=”Jack”)*(C1:C10=”apple”)*(D1:D10=”January”)*(E1:E10=”day”))
+
SUMPRODUCT((A1:A10=,”which is criteria 1?”)*(B1:B10=”Jack”)*(C1:C10=”apple”)*(D1:D10=”January”)*(E1:E10=”night”))
HTH
Catalin Bombea says
You’re right Carlos,
thanks for spotting that error.
Your sumproduct version will work fine, but the simplest approach is to write all variables in a single formula, instead of writing again the entire formula for the second variable, like:
=SUM(COUNTIFS(A1:A10,”John”,B1:B10,”Jack”,C1:C10,”apple”,D1:D10,”January”,E1:E10,{“day”,”night”}))
Sumproduct will accept too the list of constants:
=SUMPRODUCT((A1:A10=”John”)*(B1:B10=”Jack”)*(C1:C10=”apple”)*(D1:D10=”January”)*(E1:E10={“day”,”night”}))
Cheers,
Catalin
mano says
hi mynda
your Absolute! thanks so match
Mynda Treacy says
🙂 You’re welcome, Mano.
Nisha Chauhan says
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.
Mynda Treacy says
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.
Mr. Biker says
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!
Carlo Estopia says
Mr. Biker,
You’re welcome,
On behalf of Mynda.
Cheers,
CarloE
matt says
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.
Carlo Estopia says
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
Madhura says
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!
Carlo Estopia says
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:
Now add a Data Validatation. In the source box, type:
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
Madhura says
That works awesome!
Thanks CarloE
Cheers!
Carlo Estopia says
Hi Madhura,
You’re much welcome. It’s Mynda’s blog post and I just learned it from her.
Thank Mynda!
Cheers.
CarloE
Tim says
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.
Carlo Estopia says
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
Nathan says
Thank you for the excellent tutorial.
Nathan
Mynda Treacy says
Cheers, Nathan 🙂
Gans92 says
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?
Mynda Treacy says
Hi Gans92,
I’m sorry, I’m not familiar with Sharepoint and it’s limitations.
Kind regards,
Mynda.
Mohini says
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
Mynda Treacy says
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.
Mohini says
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
Mynda Treacy says
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.
Ovi says
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
Mynda Treacy says
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.
Ovi says
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
Mynda Treacy says
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 😉
eric shiver says
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!
Mynda Treacy says
Hi Eric,
I’d set my margins so small that only one row fits on each page. I hope that helps.
Kind regards,
Mynda.
Adam says
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
Mynda Treacy says
Cheers, Adam. Thanks for sharing your idea.
Mynda.
T. Sathish Kumar says
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
Philip Treacy says
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.
karen says
thumbs up!!! that’s exactly what I need, very detailed and straightfoward!Really appreciate!
Mynda Treacy says
You’re welcome, Karen 🙂
Olivia says
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.
Bilal says
really good to learn
Kurian says
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
Mynda Treacy says
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.
Kurian says
I would like to learn more in Countif function (in two sheets)
Best Regards
Kurian
Dave says
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.
Mynda Treacy says
Thanks, Dave. Great tips. I didn’t know you could re-size the name box either!
Kind regards,
Mynda.
Thomas says
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!
Mynda Treacy says
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.
Jeff says
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
Mynda Treacy says
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.
Peter says
I found this very interesting. I am a trainer helping teachers understanding Excel and its components.
Is there something for Excel 2010 – Pivot Tables?
Mynda Treacy says
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.
Ranju says
Hi Teacher,
I want to use IF, COUNTIF,COUNTIFS,SUMIF,SUMIFS functions in a single cell. Please guide me with some examples.
Mynda says
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.
Palliana says
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!
Mynda says
@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.
Pat Decasas says
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!