Range Names In Excel

Conclusion

This article is focused on giving insight in the power of range names in Excel. Starting off with simply naming a range of cells, continued with the powerful option to put a formula in a name and topped off with showing how to put XLM macro functions to use in defined names, enabling you to do things you can normally only achieve using VBA.

By making proper use of defined names, you can build powerful and easy to maintain Excel solutions.

Name Manager

Creating and editing Range Names in Excel is much easier if you start using the Name Manager!

Links

There are various sites that have extensive information about Excel's capability to define a name that refers to a range of cells or a formula. An instructive one is:

Chip Pearson (English)

Comments

Showing last 8 comments of 17 in total (Show All Comments):

Comment by: Jan Karel Pieterse (11/10/2007 8:12:06 AM)

Anytime you enter a formula that should return an array in a single cell it will either produce #Value! or just return the first value of the array.
A range name always returns an array if the formula in there is an array, you don't have to "control+shift+enter" like with a cell.

Comment by: Phil (4/4/2008 5:59:26 AM)

Thanks so much for this wonderfull explanation. Sharing my files with my colleagues was a kind a nightmare when trying to explain formulas and links. It is quite a lot easier now!

Kind Regards

Phil

Comment by: Pavan (7/31/2008 10:51:02 PM)

hi,
Great job. You had Explained in the most simple manner about define names. I never
found myself reading and understanding english this much simple. I thank god for
getting this link. Frustated to find about the define names even in their(Office)
default help file.

Thank you,
Pavan :)

Comment by: Alex (8/16/2008 9:28:20 AM)

Your blog is interesting!

Keep up the good work!

Comment by: Rai (9/3/2008 2:05:10 PM)

Hi great link with awesome information.

I have a question:
I have several sheets in my file and there are few values propogated in number of
sheets. For this I have defined them as 'NAMES' using the Insert-Names-define
command. Now I can simply used these anywhere in the sheets. I have also written a
macro for a chart title which contains this variable but I am not sure how to print
them out. can some one help. the VB script looks like:

Code:
Selection.Characters.Text = "Effects of " + compound + "on the human body"
With Selection.Characters(Start:=1, Length:=69).Fontcompound has been
previously defined as : compound = AGR

I want the macro to print AGR wherever I have compound.

Thanks
Rai

Comment by: Jan Karel Pieterse (9/4/2008 2:04:42 AM)

Hi Rai,

If "compound" is a range name:
Selection.Characters.Text = "Effects of " + Range("compound").Value + "on the human
body"

Comment by: John (7/6/2010 1:50:45 AM)

I wish to use the contents of a cell (say I type in July) to use a named range in a Vlookup formula.

I have a sheet for each month of the year and have named ranges for each month July, August, etc and now wish to allow a user to select a month (from a drop dowm menu)and have the formula return result.

I have tried Cell("contents",cell) but the Vlookup command rejects it.

Comment by: Jan Karel Pieterse (7/6/2010 4:55:54 AM)

Hi John,

Have a look at the INDIRECT worksheet function.

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.