Comments on: Excel named range - how to define and use names in Excel

Names in Excel are a paradoxical thing: being one of the most useful features, they are often considered pointless or nerdy. This tutorial will teach you different ways to create a named range in Excel to make your formulas much easier to write, read, and re-use. Continue reading →

Comments page 2. Total comments: 107

  1. Hello

    Your Website (and Blog) is Absolutely Incredible!!!
    You always don't skimp on Elaborating Every Topic, with Abundance of Explanatory Illustrative Pictures, going-by and showing step-by-step, how to Get Things DONE!
    giving Plenty of Examples and Interesting (and also, sometimes, Bizarre) Use Cases!

    It is Nearly IMPOSSIBLE to find a Subject or a Term you hadn't Dug-Up Inside-Out .
    Most of the time, BETTER THAN THE OFFICIAL Support and Documentation Website (or Webpages) of Microsoft themselves!!!

    THANK YOU!!!

    HOWEVER, I have Found such a Topic, where you are Lagging Far Behind one of the other "Excel-Pro-For-Beginners-and-Everyone"'s sites:

    The topic is: The Copious Usage and Capabilities of THE NAME BOX!

    I would be VERY HAPPY, if you had Compiled such a Guide of you own at Your Website.

    All the Best,
    and Keep On the Good Work!

  2. When you copy a sheet with a Name to a new file, the reference of the Name remains pointing to the source file; it's possible to change this reference with formulas or commands to avoid doing this manually?

  3. Thanks for this post. It was exactly what I was looking for.
    Had to sort who worked on which jobs and calculate their hours per job and get a total of hours. This helped refresh my memory as I have not worked with this in a long time.

  4. Is there a possible way for excel to state exactly the named cell from another worksheet inside that workbook in the formula to reference a sheet name! Named range?
    For example: =SUMIF(Toronto!costanalysis.

    I'm not getting this I'm getting =SUMIF(Toronto!A139

    Your feedback would be great! I already checked to Make sure defined name was set to Workbook.

  5. Hi, I am creating a report for review purposes and I need a specific cell to always display a text even after I add rows/columns. For example, I need Cell F2 to always show Text: "Day 1" even if I insert rows before column F. This shall be a daily revolving report.

    Please advise.
    Thanks

    1. I meant: Even if I insert COLUMNS before column F

  6. Sir,
    I am a beginner. I created a a NAMW say Test1 of Cell Range J30 to J50.

    Now I want to PASTE this J30 to J50 at may places. I Paste Name Test1 but it shows >>> =Test1
    and does not copy data from J30 to J50 at Desired place on Work Sheet say.. I wish to copy
    J30 tp J50 full content of Test1 at X30 down and AZ60 down or for that matter any where.

    While COPY a Defined NAME I do not get it
    Thanks

  7. Hi, I am trying to create links to an external workbook by using excel to:

    1. create the specific file path based on the current worksheet name
    2. creating a defined name using evaluate function to convert the text formula to a linked formula

    This process converts the text formula to an actual formula and obtains information from another workbook without opening the linked workbook. However after closing and reopening the file, the defined name link does not work and displays #REF. Does anyone know what's the issue there and if there's any other solutions to the issue i am having.

    Thank you

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? Please specify what you were trying to find, what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you.

  8. Hey. If a column has #name error, how can you solve the issue by assigning the same defined name to another column.
    Lets say, K6 has a defined name, and the K column has #name errors. How can I assign the define name to another range in order to resolve the errors in the K column.
    Thank you

  9. Hi

    can someone tell me if you can "call" a named cell? for exmple If I have a cell named "Apple" which has a value of 10, how can I set a cell to be ="A"&"pple" and expect that to return 10?

    Thanks

    1. Hi Ferdi,

      To "call" a named cell, you simply type its name after the equality sign, e.g.:
      =Apple

      Also, you can use a cell name for an argument of any function. For example, if you have a cell named "Apple" and another cell named "Orange", you can sum their values with this formula:

      =SUM(Apple, Orange)

      1. Thanks Svetlana. Maybe was my mistake and I wasn't totally clear. Say that you name "Apple" a cell which contains the value 10. Then in cell A1 you have the letter "A" in cell A2 you have "pple". Is it possible in A3 to type =A1&A2 and expect this to return 10?:) Maybe not but I thought it was worth asking

        1. Hi Ferdi,

          Thank you for the clarification! No, it does not work that way. The result of concatenation is always a text string ("Apple" in your case), even if it is the same as the cell name.

  10. Could someone help in shortening this formula: =IFERROR(IF([@Sbjs]>7,IF(AND(AN11>59.99,H11>34.9,L11>34.9,AO11>5,AO11<25,AJ1139.99,H11>34.9,L11>34.9,AO11>18,AO11<43,AJ1139.9,H11>10,L11<36,AO1129.9,H11>24.9,L11>24.9,AO11>36,AO11<55,AJ1124.99,H11>5,AO11<54,AJ1148,"U","X")))))),IF([@Sbjs]>5,IF(AND(AN11>59.99,H11>34.9,L11>34.9,AO11>5,AO11<19,AJ1139.99,H11>34.9,L11>34.9,AO11>18,AO11<37,AJ1139.9,H11>10,L11<36,AJ1129.9,H11>24.9,L11>24.9,AO11>36,AO11<45,AJ1124.99,AO11>5,AO11<51,AJ1148,"U","X")))))),IF([@Sbjs]>3,IF(AND(AN11>59.99,H11>29.9,I11>29.9,AO11>3,AO11<13,AJ1139.99,H11>29.9,I11>29.9,AO11>12,AO11<25,AJ1139.9,AO11>10,AO11<30,AJ1129.9,H11>24.9,I11>24.9,AO11>24,AO11<30,AJ1124.99,AO11>3,AO11<34,AJ1133,"U","X"))))))))),"0")

    1. Hello!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find and what problem or error occurred. Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.

      1. I am creating grades of three categories with 4 subjects each, basing on their average score, the performance of two essential subjects i.e. either Eng and Mtc for category 1, or End and Num for Category 2 and 3. In addition to the weighing scale of 1-9 in every subject. For one to be in grade 1 or A, He must be having an average of 59.9 and above, then Eng and Maths or Num each having 34.9, aggregate total score between 3 and 13 for category 1, between 5 and 19 for category 2 and between 7 and 25 for category 3. I am sorry it may be quite confusing but that's the tusk I have. The file I am working on can be found on this link:

  11. Hello !
    I am a food supplier in a small town and I have about 200 addresses a day so far everything is written by hand but it is difficult for me already. I want to make it easier to work with an Excel spreadsheet. Automatic ordering of customers depending on their location. For example, I put John who is number 31, and before him is Anna with number 50 and I want John to automatically move over Anna. I searched on Google and I think I should have something like a database with all my clients and their numbers (John 31 .. Anna 50) and the main table to have this property to sort them automatically depending on the date database. I will be grateful if you help me.

    1. Hello Antonio!
      I suggest using a pivot table for your data. Set automatic sorting by the field that you need. Read more about pivot tables on our blog here.

  12. Hello,
    Hope you are doing fine.
    I have got to group different columns in excel worksheet, the columns are not in a range, I mean I want to group column A and F, B and G and so on; the error I face says I cannot do so to columns of different ranges.
    Is there any way I can group like that without moving the column data to the same range?
    Thanks in advance

  13. Hi, I wish to be able to transfer data on a row from one spreadsheet to another workbook based on a statement in the 1st being True / False (yes / no).
    A user decides to select "yes" in one particular field. The data on this row then meets that particular test. I then wish this row data to be transferred to another workbook
    have thought of "if" but cannot then work out the argument to get the data on the row to transfer

    1. Hello Paul!
      You should have two identical tables on different sheets. In cell B1 on Sheet1 you can write the formula = IF (Sheet2! A12 = "Yes", Sheet1! B12, ""). After that you can copy this formula right along the row and down along the column. If A1 is “Yes”, then line 2 in Sheet1 will be empty.
      In order for the lines to be copied without empty lines, you need to use the VBA.

  14. Hi, thanks for the article.

    I was wondering if there is a way to insert multiple named ranges (non-contiguous) into another sheet? I know to insert a single named range you select the right number of cells and use something like: =named_range and click Ctrl-Shift-Enter. But I can't work out how to insert multiple named ranges at once.

    Any help would be appreciated.

    Ged

    1. Hello Ged!
      I'm sorry, but Excel only allows you to copy and paste one object at a time: cell, range, chart, etc. At the same time, two ranges or formulas cannot be inserted using standard program features.

      1. Hi Alexander,
        Thanks for the response. That's a shame, I thought it should have been possible to do this with a union or something like that. None of this worked so I thought there might have been a less known syntax for getting this to work. Thanks for your help.

  15. Have defined a named item as the date range for a chart(s). When going into the chart "select Data", i was expecting to be able to use the named function as the date range. But I get and invalid error. I have tried using "=" with the name, but that is also invalid. The named function is =RATES!$A$37:$A$55, which is the range in the chart.Since there are 6-10 charts that have to be updated daily, the idea is to make one change to the named function i.e change the ending range, which would than apply to all the charts. (FYI the charts are on a "charts" sheet.)
    Your help is appreciated.
    Paul Woodward

    1. Hello Paul!
      If I got your problem right, it is all about applying a named range when creating a chart. Besides, you want your graph to change when the named range changes, for example, when new rows are added to it.
      For this purpose, it is necessary to create a named range, as described above, first of all. Let us suppose we name your range =RATES!$A$37:$A$55 "Rates001". Then you should create a chart and indicate =RATES!$A$37:$A$55 in the Chart Data Range field.
      After creating a chart, please click Select Data Source in the menu again, select the data range (Series1) in the Legend Entries window and click Edit. Type =Rates!Rates001 in the Series Values field. Please do not forget to click OK. Now your named range "Rates001" will be a source of data for the graph. I hope I have answered your question. I also recommend reading article on our blog about charts in Excel.

  16. I highlighted three cells (a1,b1,c1) containing numeric data and which the format
    was numeric and created a name range for these 3 cells by typing in a name in the
    name box. When I now reference the range by just typing in the name range in a
    cell I get a #VALUE error. I have researched & researched to find out why but
    can't find an answer. When I go into the name manager & look at the value for this
    named range all the values in the array are enclosed in double quotes, which I think
    means that Excel is interpreting these values as text and perhaps that is why I am
    getting the error. I have tried to change the format by the means you mentioned
    in this article, but none of them work. Any suggestions would be appreciated.

  17. Good morning,
    I have a table of some 100 columns wide, all of which are attributes for individuals named in column A and for some 400 down. I use formulas in each column to subtotal/total plus compile some statistics. Instead of naming a range for each column I want to set a constant that would represent the last row and use this constant in my per column calcs. Can't figure out how to do this in excel or VBA. In essence instead of having a name for say "A1:A400", set "1" as constant "top" and "400" as constant "bottom". Then reuse the constants in all formulas in all columns. In that manner if new rows are added or I desire to expand the table range I only have to change the "bottom" constant and all calcs change. Makes sense??

  18. There is no 'Name Manager' on my ribbon that I can find.

  19. Hi,
    applying names in existing formulas seem to work only if the named range or cell and the cell where the formula is are both in the same sheet (tab). What if they are located in different tabs? Which is most usual really!
    Thank you

    1. Hi Tas,

      When defining a name in Excel, the name scope is set to Workbook by default. That means the name is recognized anywhere within a given workbook, no matter on which tab the formula is located. To view the level of existing names, press Ctrl+F3 to open the Name Manager and check the Scope column.

      1. Hi Svetlana, I have the same issue as Tas. I have already got the scope set to Workbook however so do you know why I would still be having the problem and what a solution would be? Thanks

  20. This is an exceptionally clear and comprehensive explanation. Thank you.

  21. Is there a way I could use named ranges in a cell comment?

  22. Hi, I am using a Named Range on a sheet. I have cell range unlocked and rest of sheet locked It runs fine with sheet uprotected, however if I protect the sheet the Named Ranges fails to work.
    Can you assist please?

  23. Hi, i need to know how we can get "true" ot "false" result using if function taking base from "named range".
    Eg- i have named range of colour as "color_list" having 5 color as red white, black, orange and green.
    Now i need need to get true or false result using if funtion taking information from "color_list".
    =if (and (A2="red",A3=colour_list)),"true","false"
    If cell A2 have text "red" and cell A3 have any color list from the named range "color_list" , then results "true" else " false".
    But i didnt get result using above formula.
    How can we get it.
    Please guide.

  24. Hi,

    I want to know can we save the background color for the cell in the name manager too rather than the constant value?

  25. Hi, how can i create dependent validation list if i have multiple table to select

  26. Hi,
    I want to start by thanking you for your articles. They are great!
    I am copying some worksheets from one workbook to another one as I need them as templates. There are about 300 names and after copying the worksheet, they show the source. Is it possible to edit them all at once, so that part can be replaced with a blank?
    Thank you!

  27. Hello,
    I would need to create a name [easy], calling another excel file [with multiple tab]. The address of the called/linked excel file, may change in location.
    as exemple, I'm looking for this; =namefilelinked+tab number+row number
    the second part of formula, '+tab number+row number' never changed. It is fixed.
    Is that possible and how?
    Thank you

  28. Hello,
    Is there anyone can help me with my query, I have around 100 sheets in my workbook; there are number of ranges from each workbook has a defined name.
    I am looking for a way to allow user to Pick any of the defined name ranges from a picklist and then on clicking can navigate to that specific workbook (range).

    Thanks,
    Ankit

  29. I am trying to make a packing list in Excel. The items to pack are boxes labeled things like Microphones, Grip, Clamps, DAW, Drapes, and Green Room. Each of those boxes contain a list of items to be packed. what I would like to do is make a master sheet that has every box and ever boxes' contents. then when we pack for a project, I could generate a customized list from the master list by simply inserting on a new sheet something like "=Microphone_Box", and getting the range names Microphone_Box" populated on my customized list.
    Am I making any sense and can it be done in Excel?

  30. Hi, Is there a way to make a cell refer to a named range? For example, the Covar function requires two arrays. Instead of typing in both named ranges, I would like to make the Covar function reference two cells that reference the named ranges. To take the covariance of ~500 stock's price data, I would have to manually enter in the named ranges for 250,000 cells. Is here an easier way to accomplish this?

  31. I have a workbook with twelve sheets, one for each month. I do several calculations(formulas), but the subsequent sheets only use the values from sheet 1. I've tried using "Jan:Dec!$d$4:$d$6" to include all of the sheets, but I get a #value! error indicating wrong data type.

  32. i have a problem in create a defined name using "Formulas>Create from selection>Top Row".If i try to create a defined names for more than 20 columns using Top rows,It created defined name for some columns and some columns were missing.How to resolve this issue

  33. I named a cell but made a spelling mistake. How can I correct the spelling in the name?
    Thanks,

    Paul

  34. Where can i find these named ranges when in Word to insert into a mail merge?

  35. Hello,
    would it be possible to include in a formula a text from a cell that refers to a given "name" that identify a column of values? I have the closing prices of a stock in column E that I named "Close", I had the cell N1 with the text "close", I would like to reference to cell N1 in the formula contained in the cell O8 (=min(Close)) as cell, so I would use, in O8 =min(N1). Changing the text in N1 would report the value calculated for the same function applied to different set of values (columns).
    Thanks for the support you can give me.

    1. Was there ever any answer to this? I’d love to know! I’m using a series of List Validations to develop a Budget vs Actual comparison tool. I’ve named ranges for Budget and Actual over similar periods and would like the names range referenced to change depending on the period and budget vs actual selection. This would be perfect!

      1. Just tried an update to my formulas using the =indirect() formula. Now I m able to change my vlookup to whatever table array the logic in my target cell returns. So instead of hardcoding the names range for the vlookup - I’ve pointed it to a cell and that cell results in the names range I’m looking for (=vlookup(a14,indirect(L17),b5,false) where a14 = row ref I’m searching for and l17 refers to the bud or actual PLs, b5 is column ref).

        Good luck!

        1. Dan - Thank you, thank you, thank you!!! You had the answer I have been searching for!!! I have been successfully able to use the INDIRECT formula within my VLOOKUP formula to be able to summarise my results by quarter (after first defining names for the data for each quarter).

  36. Can you use named ranges with the '=IF()" function? I have two columns of data and want to use a formula like this: =IF($A1=ListB, $A1, "No Poster") in column C where ListB is the named range B1:B100. Unfortunately the formula only works if data in A"X" and B"X" are the same, if the same data that is in A1 is in B3 for example I get the "No Poster" message in C1.

  37. Hi:
    Is it possible to have a named range available to any worksheet? What I mean is I want a named range available in named manager every time I open a new or an existing worksheet.

    Thanks you very much

    Mike.

    1. Hi Mike.

      Unfortunately, it is not possible to create a named range available in all the worksheets using the standard Excel features. However, it is possible to do using a special macro. I am really sorry, but we can’t help you with this. You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry for not being able to help you more.

  38. Hi there,

    I would like to define a Name that references the name's calories. However, when I list the name without an =, (so that it just says, Whole_Wheat_Bread) I would like to reference its value in another column titled Calories.

    i.e.

    Column A:
    Row 1: Food
    Row 2: Whole_Wheat_Bread
    Column B:
    Row 1: Calories
    Row 2: =A2

    [Assigned Value for Whole_Wheat_Bread is 78]

    However, when I sype =A2 it enters "Whole_Wheat_Bread" in the cell;
    I would like it to enter the Value associated with Whole_Wheat_Bread (which is 78)

    This is so when I enter food items for a daily diet, I can just enter the titled food item and let the caloric section autofill with the selected values so I do not have to type ='adjusted new food' in the calories column.

    Thank you!

    1. I believe it is the same answer as under post 11 (INDIRECT-formula).

  39. If I define with a name a range of cells and later insert a column or row through that defined block, does that new column or row become part of the defined range, or does the definition cease to apply, or will it split the defined thing into two parts, or just exclude the new column or row from the range and just run either side of it?

  40. Is it possible to use Concatenate or the ampersand to develop a known value name for use in a formula?
    I have several list selections in a spreadsheet, that when merged (concatenated) will give me one of several named values. I would like to avoid having a nasty IF statement just to accommodate the all the possible combinations.

  41. Hello

    I am looking to use VLOOKUP to return certain column data out of an array.
    Outside of a table this is what I have been using:
    =VLOOKUP($B2,Sheet2!($A$8:$H$48,Column(B8),FALSE) and then dragged the formula across and down the page. This is fine, as the lookup locks to B.

    However, when in a table with named ranges, I cannot drag the formula across as the initial reference keep changing as I drag across.
    =VLOOKUP([@sampleid],[Book1]Sheet1!$A$8:$H$48,COLUMN(B8),FALSE)

    The initial [@sampleid] needs to be locked, I assumed with $ but nowhere i put it allows the formula to function...

  42. I have a named range. Is it possible to reference, via a formula, individual cells in that range? My range is 3 columns by 1 row. I would like to be able to access each cell individually utilizing the range name.

  43. Need more clarity on.

    Apply names

    Ignore Relative/Absolute

    Use row and column names

    Regards
    Lalit.M

  44. I set a field with formula to link with the month of the date and set a list of name by "month" with formula. Is it possible to use the formula result to call the "names" result?
    ie 01 Jan 2018
    field A =left(A1,4,3),result=Jan
    Names set : Jan = B3
    End up, field A = B3
    I tried, but failed.
    Thanks

  45. Is there a way to create a name without the sheet reference? I want to have a name that references Sheet1!$S:$S if I use it in Sheet1 and Sheet2!$S:$S if I use it in Sheet2 but I can't figure out how to do that without using multiple names and I would need too many names for that.

    1. Hello, Garrett,

      Sure there is! You just need to name the range you want to use and then use this new name in references instead.
      Please have a closer look at this point of the article above for more details.

      If you mean that you don't want to use the "Sheet" word when naming your range, I'm afraid it's impossible to omit that part since Excel should understand what cells you refer to exactly.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)