Using range names is an important skill if you are an intermediate or above user. They simplify formula creation and make your formula easier to understand and maintain. They also work well with macros.
There are a few rules you need to be aware of in creating range names:
- the name can’t start with a number (it can have numbers in it)
- names can’t be the same as a cell reference eg Q1 for quarter one doesn’t work. In Excel 2007 and later versions QTR1 won’t work either, as it is a cell reference in the expanded grid. You can use the underscore Character so that Q_1 and QTR_1 are both valid range names.
- names can’t contain symbols. The underscore character and the full stop are allowed e.g. Tax.Rate or Tax_Rate
- name length is limited to 255 characters (not really an issue)
Beware of too much abbreviation in range names. You need to make your names understandable. Your files may be used by other people, so using descriptive name is important.
I recommend (this is not a rule) that you capitalise at least one letter in your range names.
I also recommend capitalising the first letter of any words you use e.g. TaxRate and ProductCode.
Capitalising serves two purposes. The first is that if you type a range name in lowercase Excel will automatically capitalise it in the formula if it recognises it. If you misspell the name it will stay in lowercase and is then easier to find and correct.
The second reason is that I find TaxRate and ProductCode are easier and quicker to read than taxrate and productcode.