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

All comments about this page:

Comment by: Jan Karel Pieterse (5/1/2007 11:12:37 AM)

Hi Armadno,

Thanks!

If you want to ask questions, check out the newsgroup links on my <a target="_blank" href="http://www.jkp-ads.com/links.htm">links page</a>

Comment by: Danny (11/8/2007 4:49:30 AM)

Thank you for your very impressive introduction to Excel defined names. There were several pieces of information that were quite new to me and very powerful: for instance that you can define names local to the sheet *and* the names are copied appropriately when you copy the sheet.
One thing I've wondered about that you didn't address: exactly how does Excel force a column to produce a single value when needed? I have created user-defined functions that expect a single value and work fine on named columns, but produce a DEF? error when I try to run "Evaluate formula".

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"

Range Names In Excel

Conclusion

Name Manager

Links

Comments

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