• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Named Ranges Explained

You are here: Home / Excel / Excel Named Ranges Explained
excel named ranges explained
October 27, 2010 by Mynda Treacy

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.

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.

Examples of Excel Named Ranges

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.

New Name box
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.

Name Manager
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.

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 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%.

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.

excel named ranges explained

More Named Ranges Posts

Relative Named Ranges

Relative Named Ranges in Excel allow you to reuse named ranges with different results dependent on the cell you use them in.
Excel Named Ranges

Excel Named Range Shortcut

This Excel Named Range shortcut using the exclamation mark will enable you to create a global name with local scope.
Writing Excel Formulas Efficiently

Writing Excel Formulas Efficiently

Formulas should and can be quick to write, interpret and update. That's why writing Excel formulas efficiently should be one of the first skills you master.
Excel Dynamic Named Ranges

Excel Dynamic Named Ranges

Excel Dynamic Named Ranges update automatically to include new data in the ranges referenced in your formulas, PivotTables and other Excel tools.

More Excel Posts

tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.


Category: ExcelTag: named ranges
Previous Post:Get Your Own Interactive Google Map FREE
Next Post:Excel Pivot Tables Explained

Reader Interactions

Comments

  1. Sheila

    November 30, 2022 at 2:08 pm

    I am working on impact ratings High, Medium, Low, Nil. How do I average the occurences of this in one column. I assigned values 4-0 to each one but not sure it is correct, although I got numerical values. Could you please help? Thank you.

    Reply
    • Mynda Treacy

      November 30, 2022 at 2:31 pm

      Hi Sheila, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  2. Chris Enity

    June 29, 2021 at 5:24 am

    I have an excel platform that is used for computing students result, it has a precessing side and an output side. I don’t want the both sides to show at the same time, I want to create an interface such that when you click on Processor, only the processing side of excel sheet will show and when you click on output, only the output will show. How can I achieve this?

    Reply
    • Mynda Treacy

      June 29, 2021 at 1:02 pm

      Maybe something like hyperlink buttons to navigate to the different parts of your workbook.

      Reply
  3. Manuel Goudie

    December 23, 2020 at 8:05 pm

    Is there any way to change the scope of a named range once created? Let´s say I just created a named range through the name box. By default, the scope is the “Workbook”. If I decide afterwards that I want it to be “worksheet” scoped, how can I modify it?
    Thanks
    Merry Xmas!

    Reply
    • Mynda Treacy

      December 24, 2020 at 7:17 am

      Hi Manuel, no, the only way to change the scope is to delete the name and recreate it.

      Reply
  4. Nicole Wahlgren

    February 7, 2020 at 12:34 am

    I am trying to color band a certain area of my worksheet which I’ve defined as a named range. I also have a command button that copies and adds the row before it. Is there anyway to define function that adds color banding as I add rows? I am assuming the VBA code is copying both the formatting and the formulas from the above row. Is there anyway to only have it copy the formulas and the borders without the fill?

    Reply
    • Mynda Treacy

      February 7, 2020 at 10:16 am

      Hi Nicole,

      You might like to change the code to ‘paste formulas’ which is available in the Paste Special dialog box.

      Mynda

      Reply
  5. DG

    June 28, 2019 at 12:36 am

    Hi,
    I’m unable to use the markup function it keeps giving me an error of #NAME?

    Reply
    • Catalin Bombea

      June 28, 2019 at 2:40 pm

      Hi DG,
      Please upload a sample file on our forum so we can see what’s wrong.
      Cheers,
      Catalin

      Reply
  6. Ted

    April 7, 2018 at 2:00 am

    without a workbook to download, I got lost pretty quickly

    Reply
    • Mynda Treacy

      April 7, 2018 at 7:38 am

      Hi Ted,

      The workbook download link is towards the top of the post. It’s under the second heading.

      Mynda

      Reply
  7. Lisa

    February 24, 2017 at 4:04 am

    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!

    Reply
    • Mynda Treacy

      February 24, 2017 at 10:38 am

      Wonderful! Glad it will help 🙂

      Mynda

      Reply
  8. Siôn

    February 23, 2017 at 6:06 am

    Thanks for the explanations and tips on uses!

    Reply
  9. Rakan

    October 16, 2016 at 4:01 pm

    You are the best

    Thank you

    Reply
  10. Lynne

    May 2, 2016 at 10:37 am

    Excellent! I’ve used Excel for years and never even realized there was a Formulas tab. Wow,can’t wait to subscribe:-)

    Reply
    • Mynda Treacy

      May 2, 2016 at 10:45 am

      Glad we could help, Lynne 🙂

      Reply
  11. walter

    January 28, 2016 at 3:08 am

    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

    Reply
    • Catalin Bombea

      January 28, 2016 at 4:05 pm

      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

      Reply
  12. Syed Najmul Hassan

    September 8, 2015 at 12:12 pm

    VERY HELPFUL INDEED

    Reply
    • Mynda Treacy

      September 8, 2015 at 12:23 pm

      Thanks, Syed. Glad we could help 🙂

      Reply
  13. Thomas

    August 26, 2015 at 3:52 am

    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

    Reply
    • Mynda Treacy

      August 26, 2015 at 9:45 am

      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

      Reply
  14. Chris

    August 4, 2015 at 6:55 am

    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?

    Reply
    • Mynda Treacy

      August 4, 2015 at 10:28 am

      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

      Reply
      • Chris Jordan

        August 4, 2015 at 10:38 am

        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

        Reply
        • Mynda Treacy

          August 4, 2015 at 12:15 pm

          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

          Reply
          • Chris Jordan

            August 6, 2015 at 2:40 am

            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

            August 6, 2015 at 9:56 am

            Hi Chris,

            Glad it’s working now, albeit with a bit more effort required.

            Kind regards,

            Mynda

  15. R. Charan Pagan

    June 25, 2015 at 4:33 am

    There are some excellent tips in here! Thanks.

    Reply
    • Mynda Treacy

      June 25, 2015 at 10:15 am

      Thank you!

      Reply
  16. Jonathan Mills

    March 3, 2015 at 8:52 am

    Great summary book….every one who uses Excel should have this.

    Reply
    • Mynda Treacy

      March 3, 2015 at 9:42 am

      Cheers, Jonathan 🙂

      Reply
  17. Denis

    December 4, 2014 at 2:21 pm

    YOU ARE DEFINITELY GREAT!

    by an Italian in China

    Reply
    • Mynda Treacy

      December 5, 2014 at 2:52 am

      Thanks, Denis 🙂

      Reply
  18. 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
  19. 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
  20. 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 🙂

        • Carlos Mario

          June 25, 2015 at 12:52 am

          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

          Reply
          • Catalin Bombea

            June 25, 2015 at 6:25 pm

            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

  21. 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
  22. 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
  23. 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
  24. 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
  25. 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
  26. 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
  27. 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
  28. 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
  29. 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
  30. 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
  31. 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
  32. 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
  33. 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
  34. Bilal

    April 19, 2012 at 9:26 pm

    really good to learn

    Reply
  35. 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
  36. Kurian

    March 25, 2012 at 5:32 pm

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

    Best Regards
    Kurian

    Reply
  37. 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
  38. 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
  39. 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
  40. 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
  41. 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
  42. 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
  43. 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

Trackbacks

  1. Excel VLOOKUP Multiple Values says:
    January 4, 2012 at 11:41 pm

    […] table_array : Table1[[Name]:[Pay Rise 2006]] – Our table or data range is an Excel Table hence the data range has the name ‘Table1’. You could easily replace this reference with a regular data range e.g. $A$8:$F$34 or a named range. […]

    Reply
  2. Excel INDEX and MATCH Functions says:
    February 6, 2011 at 8:38 pm

    […] Use named ranges instead of $C$33:$C$40 etc. to make formulas more intuitive and quicker to […]

    Reply
  3. Save Time With Excel 2007 Tables says:
    December 10, 2010 at 2:09 pm

    […] Instead it uses what Excel calls ‘Structured Referencing’ which is very similar to Named Ranges.  Read Magical Feature 4 below for more on […]

    Reply
  4. Excel HLOOKUP Formulas Explained says:
    November 26, 2010 at 1:38 pm

    […] make it even easier by giving the Commission Rates table a named range.  Let’s call it ‘ComRates’.  We could then more easily build the HLOOKUP formula, and also […]

    Reply
  5. Excel Drop Down Lists says:
    November 22, 2010 at 2:08 pm

    […] Now give the table a Named Range.  I’ll call mine […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.