Excel Unplugged

Using Named Ranges in Excel


There will be three different views of named ranges this post. First all different techniques of creating named ranges. After that a few techniques of using named ranges will be given and finally a few magical tips and tricks on operating with those names. Even if you think you know all about named ranges in Excel, read the third part. You will not regret it!

Creating Named ranges

There are basically three ways of creating named ranges in Excel. The simplest way is to select the range of cells you wish to name (can also be a single cell) and then write the desired name in the Name Box.

Named Ranges in Excel

Note the underscore in the name! A name of the range must not contain any spaces!

The second way is a bit more complicated but here Excel does the hard lifting so you don’t have to. The desired result is for each row to be named by the month name in the left most column and for each column to be named by the weekday name in the top row. You can do this by selecting the data and then FORMULAS/Create From Selection

Named Ranges in Excel

Now Excel wants to know where the desired names are and in this case it’s Top row and Left Column.

After pressing OK, you get twenty new names. Note the spaces! Even though Excel is automatically assigning the names, it still stands that no spaces are allowed in the names so Excel replaces any spaces by underscores.

Named Ranges in Excel

The third way of defining names is to do it manually either through FORMULAS/Name Manager/New

Named Ranges in Excel

or by FORMULAS/Define Name. Either way you get the following dialog box.

Named Ranges in Excel

Now here, you can create dynamic named ranges (more about this here) that you can use in charts or as sources for data validation dropdown list or by simply using it to SUM up values dynamically.

Using named ranges

First thing that named ranges give you is a sort of a dynamic table of contents in your workbook. You can use the dropdown list of names in the Name Box to navigate your workbook.

Another great use of named ranges is to use them in your formulas. This makes your formulas easier to read and it also removes a need for using absolute cell references since a named range remains the same even after you copy your formula.

Whereas you can’t use tables in Excel for defining a data validation dropdown source like Table1[Column1], you can create a manual named range that points to Column1 of Table1 and you can then use it as a dropdown source.

Named ranges also allow you to use parts of your Excel workbook in SharePoint.

But it gets even better than this

Tips and Tricks

First trick is a little excel command that does magic when you define names to ranges that you are already using in formulas but in a simple Excel A1 syntax. Now you want to replace those by your newly defined names. The command you want to use is FORMULAS/Define Name/Apply Names…

Named Ranges in Excel

and instantly you get your names in your formulas.

Next up is a brilliance of using XLM names. This are old eXceL Macros. They can only be used in named ranges but they give you some new abilities in Excel. Three great uses of this are




Here is an example of using FILES(). First you define a custom name Files as =FILES()

Named Ranges in Excel

And now you can use this in your INDEX formulas and by using the ROW() function getting the first, second and so on file in a same folder.

Named Ranges in Excel

Here is the same way of getting all the named ranges from a workbook into your sheet. Pure brilliance! Keep in mind, this are macros so you will not be able to use them in macro free excel files.

Third trick is a type of formula you have probably never written. And that is a formula of using SPACE as an operator. I will be using the second sample from defining names section above.

Named Ranges in Excel

Now every row is named by the month name and every column by the day name above. Now we can do magic. We write

=April Friday

And we get

Named Ranges in Excel

Pure brilliance!

And here’s a free one for the road. If you set your zoom level below 40 so let say to 39, Excel starts showing you the names of your named ranges 🙂

Named Ranges in Excel

For some of this tricks, a big thanks goes out to Bob Umlas. This is his great book where you can find many more just like these

Comments 4

  1. MF says:

    Nice tips. I know about =FILES but not =NAMES and =LINKS. Nice to learn something new.

    One more step to make the =FILES even better is to specify the path.

    For example, in A1 we input

    C:\Your Path\*

    Then when we define the name, we use =FILES($A$1) instead of =FILES()


  2. Thanks for your comment MF. I to think that if you learn something new, than you’ve won the day. And thanks for the addition on the =FILES() function.

  3. Ivan M. says:

    Thank you for these good tips related to Names in Excel

    I would add two more tips:

    1) Using Names for storing of any permanent constants (number, text, etc.)
    We can manually create new name in Name Manager (e.g. ‘Name’ -> “PI_number” and ‘Refers To’ -> “=3.14159”). Then we can use the name in formulas without actual reference to a sheet’s range of cells

    2) Using Names for storing of permanent arrays
    We can input to a sheet range the array we need. The steps are following:
    – we input country names (e.g. Germany, United Kingdom, Ukraine, and Switzerland) to the range (“A1:A4”)
    – then we input country codes to the Range (“B1:B4”) accordingly (e.g. DE, GB, UA, CH)
    – after that we select any free cell and refer to the whole range (e.g. “=A1:B4”) in formula bar
    – we press ENTER, get the error #VALUE, then we click on the formula bar again and press F9
    – formula bar returns the full array (e.g. {“Germany”,”DE”;”United Kingdom”,”GB”;”Ukraine”,”UA”;”Switzerland”,”CH”})
    – we copy it, go to Name manager and manually create new Name, referring to the just-copied array (e.g. ‘Name’ -> “Country_code” and ‘Refers To’ -> “={“Germany”,”DE”;”United Kingdom”,”GB”;”Ukraine”,”UA”;”Switzerland”,”CH”}”)
    – after that we can go back to worksheets, delete everything we worked there with and start using the array in VLOOKUP

    1. Great tips Ivan… I particularly like the second one 🙂 Thanks for sharing.

Leave a Reply

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

%d bloggers like this: