Illegal Range Names
Named Ranges in Excel can contain letters, numbers, and some special characters. Ozgrid has a good list of guidelines for named ranges.
- The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.
- Names cannot be the same as a cell reference, such as Z$100 or R1C1.
- Spaces are not allowed. Underscore characters and periods may be used as word separators ¾ for example, First. Quarter or Sales_Tax.
- A name can contain up to 255 characters.
- Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.
It seems to be the second one that gets people a lot. If you try to create a range name like A1, you get a “That name is not valid” error.
Periods are OK, though.
When I tried to name a range that would contain the first standard of the 50th copper sample, I got an error. The name I wanted was cu50.s1.
Excel treats a period like a colon when you’re referencing ranges. If you type =SUM(b1.b12), it’s the same as =SUM(B1:B12).
When I tried to enter cu50.s1, Excel evaluated that to S1:CU50, a valid range reference and therefore an invalid range name. Excel 2007 has even more invalid range names because it has so many more columns than previous versions. I settled on _cu50.s1
“=SUM(b1.b12)”
That’s awesome!
As for named ranges, I wish more people I work with knew about them.
Does anybody know why the period-as-colon is in there? I thought it was a Lotus 1-2-3 holdover, but I’m not sure.
I have this theory:
If your name resembles a range reference, and contains a period AND a “c” or “f”, it’ll raise an invalid name error.
For example: c1.c2, f1.f2, c2.c1, f50.a1, s1.c50 are invalid names, but:
d1.d2, j50.a1, aa1.w50 are VALID names.
Excel seems to have something with the c’s and f’s in this issue. Somebody knows why?
Well, there goes my theory. f1.f2 works as does f50.a1, for me. It’s just Cs and Rs that don’t work - columns and rows in R1C1 notation. So I can’t use R1C1 or R1.C1, but I can use A1.A1. Odd.
I am surprised that they use the “period” as a delimiter in the Sum function, it is so inconsistent. Try “=Sum(1.1)” it doesn’t produce “=Sum(1:1)”.
Slightly off topic, but I get had a very annoying problem with delimiters in the dynamic range names on non US/UK format regional settings. This is because of the two types of List Separator “;” vs “,”.
For example I often use a range name with a dynamic “Refers to” as it allows me to refer to all the cells below a header row. “=OFFSET(Sheet1!$B$1,1,0,MAX(COUNTA(Sheet1!$B:$B)-1,1),1)”
This can be very a useful way to reduce the amount of VBA in a project by typing something like
set rng = ThisWorkbook.Names(”DynJan”).RefersToRange
The problem occurs when I switch my machine to Dutch or French regional settings etc. If I use a dynamic range name in Excel they operate fine, but if I use them in a VBA program on a European setup, they cause an error, I thought originally this was only a 2003 bug but it seems that it has not been fixed in 2007.
I found an unsatisfactory workaround to this. If I make a simple range name (ie one without any commas) that refers to my dynamic range name, then I can refer to the simple range name in vba and get the dynamic range name. You can test this yourself by playing with the Regional Settings in Control panel.
European delimiters are such a pain, but probably not as bad as having to work on a French Keyboard !
I see: Columns and Rows are Columnas y Filas in Spanish, version I´m using. In English don´t work the r’s. In Spanish, the f’s.
Then the new theory is: a name can not have a period and resemble a range reference in R1C1 notation. But in A1 notation, it´s ok the period.
It works for you?
Nit-picker remark: rule #2, “Names cannot be the same as a cell reference, such as Z$100 or R1C1″, should be restated as “cannot be the same as a range reference”. “A:A”, for instance, is not accepted as a valid name for a range…
Dick - yes I think it was a Lotus 123 compatability thing too. In 123 ranges displayed as A1..A10 by default (I just checked, they still do), but you could (can) specify a range from the keyboard with a single “.”. Using ranges in a function (like @SUM) you can still specify the range with a single period, but you can’t use a period as a list separator, because they would be confused with decimal points.
i recently found you can use greek letters for names too, eg α,π (alpha,pi) - this can be useful for working with math models and reducing formula length. It seems they can also be defined using other unicode character sets eg accented characters, hebrew and chinese!
Names can also begin with a backslash, a hangover from Lotus macros.
\ is valid, \_ is not, \__ is.
I think named ranges are over rated a bit, but I like the idea of calling a range “A1″, that’s genius, even better if it refers to cell B2!!!!
Does anyone use a good naming convention for range names, I generally define names in VBA following gidelines in Prof Excel Development but the take on names there is either not good or I don’t understand it (eg what’s a ptr, what’s a rgn?).
Also isn’t the name manager in 2007 so much better, I now flip flop between that and Decision Models one. Best new feature by far is ability to simply localise names to Worksheet. Real shame this is not the default action!
For named ranges that I intend to use in code, I prefix with ‘rng’. If I’m using them just in worksheet functions, I don’t prefix them at all. For named constants, I prefix with ‘con’ regardless of where I intend to use them.
So, to answer your question, No, I don’t use a good naming convention. But it works for me.
Neal: I don’t like the excel 2007 built-in name manager, because it takes more clicks/keys to edit an existing or define a new name from there. The add name button of Charles and my Name Manager does allow adding localised names btw.
Dick,
When I read the title of this post I thought it would be about range names that exist, but are illegal. In other words: corrupt range names. Like:
PRO
Names like these cannot be removed.
Anyone knows where one could find a list of Reserved Names
Eg
Criteria
Print_Area
Extract
R
C
_FilterDatabase
“*_FilterDatabase”
“*Print_Area”
“*Print_Titles”
“*.wvu.*”
“*wrn.*”
“*!Criteria”
Note that these are the english names, not necessarily the ones shown in the UI (NameLocal property).
Hi Jan,
What does those Last 3 mean and when they are used ? I haven’t seen anything like that.
Regards
Kanwaljit
Speaking of reserved names, I think the inability to name a worksheet “history” is pretty funny. A little googling and it appears that this is why:
http://www.pcreview.co.uk/forums/thread-2592835.php
http://spreadsheetpage.com/index.php/oddity/a_sheet_named_history/
Like JW, I never use Track Changes so I never would have known this.
Jan, The names that are corrupt can sometimes be removed by setting the cell refs to R1C1 and back again. this can force corrupt names to be made legal just long enough to remove them.
As Jan suggested localised range names have been around for a long time and many name managers allow you to control them quite easily. Even in the native Excel you use Ctrl+F3 to set a range. If you do it again it becomes a local range name to the sheet that you are on.
I also follow Dick’s style of naming and it seems to work for me.
Jan:
1. I know that way they can be removed, my Name Manager uses that trick.
2. I have never seen a local range name being created by defining the same name twice, the second time simply overwrites the first definition. Copying a sheet effectively creates local range names for all global ones pointing to the original sheet. This is one of the big problems in files with copied sheets, they gather lots of local range names and hence cause the file to produce odd results because people do not expect those.
JKP - “I don’t like the excel 2007 built-in name manager”
Me neither. It’s awkward and less efficient for simple to intermediate use than the old interface. And for advanced use, nothing beats the Name Manager.
[...] Daily dose of Excel name ranges - Good article on how to name ranges well [link] [TB] [...]
Kanwaljit:
The wvu names are inserted when you create custom views
I forgot what the wrn’s are for
The Filter name is inserted when you use advanced filter.
Hi Jan,
I wish if something like the “Comments” portion of Name Manager in Excel 2007 can be added to NM.
Regards
Kanwaljit
Dick:
It’s interesting you chose to use ozgrid as your source for guidelines for naming cells. Following your link leads to:
* The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.
* Names cannot be the same as a cell reference, such as Z$100 or R1C1.
* Spaces are not allowed. Underscore characters and periods may be used as word separators � for example, First. Quarter or Sales_Tax.
* A name can contain up to 255 characters.
* Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.
Now, check Excel 2003 help for ‘About labels and names in formulas’ particularly the section titled ‘Guidelines’:
What characters are allowed? The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.
Can names be cell references? Names cannot be the same as a cell reference, such as Z$100 or R1C1.
Can more than one word be used? Yes, but spaces are not allowed. Underscore characters and periods may be used as word separators — for example, Sales_Tax or First.Quarter.
How many characters can be used? A name can contain up to 255 characters.
Note If a name defined for a range contains more than 253 characters, you cannot select it from the Name box.
Are names case sensitive? Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.
Finally, check Excel 2007 help for ‘Learn about syntax rules for names’
* Valid characters The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.
Note You cannot use the uppercase and lowercase characters “C”, “c”, “R”, or “r” as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.
* Cell references disallowed Names cannot be the same as a cell reference, such as Z$100 or R1C1.
* Spaces are not valid Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.
* Name length A name can contain up to 255 characters.
* Case sensitivity Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then create another name called SALES in the same workbook, Excel prompts you to choose a unique name.
Going to the source (i.e., the Excel help files) leads to additional information about starting characters as well as other subtleties. Additionally, there is a change in behavior in how Excel treats duplicate names (see the last rule in both Excel 2003 and Excel 2007)
Tushar, Dick has done what a lot of us, myself included, tend to do: Assume there will be nothing helpful in the built-in help. It`s amazing how often this is true but Excel (Including VBA) tends to buck the trend.
The worst of it is when you forget to check your own notes on a subject, spend an hour working it all out again, then go to write it down and… find all your notes from the previous time.