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: 67

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

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

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

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

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

    Paul

  6. 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).

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

  8. 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).

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



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)