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.

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

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.

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

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

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…

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

=LINKS()

=FILES()

=NAMES()

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

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.

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.

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

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 🙂

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