How To Use Range Names
Using the table I showed you in fig. 3 (See section 1) I'll demonstrate some options with names. To calculate the total of Apples sold, one would normally use this formula: =SUM(B2:B4). After defining names you can simply type: =SUM(Apples). It is evident this is easier to understand.
Fig. 5, formula with applied name
Another interesting thing is that names are always arrays. If you select three cells below each other and type =Apples, followed by control-shift-enter:
Fig 5 a: names are arrays
you will get the values of the three cells Apples refers to.
But there is more. When you would like to know the apples sold in Europe (1000) you can type:
By separating the two names with a space (also called the intersect operator) you indicate you want to get just the intersection of these two named ranges. Since in this case this is a single cell, the formula returns a single number.
In fact names can be used in all areas of Excel that accept a range of cells as an argument.
One special example is Data Validation using the List option (See fig. 6). Without explaining this powerful Excel feature I show you that using a defined name enables you to use a list source that is not on the same worksheet as the one you want to set the Validation on. Normally, if you would try to enter a range of cells that is on another worksheet into the Source box, versions of Excel prior to Excel 2010 show an error message:
Fig. 6:Data, Validation, List option
In order to achieve this, name the range you want to use as the source (e.g. Apples) and use this formula in the Source box:
The Validation will now be accepted by all versions of Excel.