JKP Application Development Services.

                    Microsoft Office Application Development

Range Names In Excel

Introduction 1 2 3 4 5 6 7 8 9 10 Conclusion

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:

=Apples Europe

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, Excel shows you 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:

=Apples

The Validation will now be accepted by Excel.

<< Previous    Next >>

    Subscribe in a reader

Use the contact page to issue questions or comments about this website.
Copyright © 2003-2008 JKP Application Development Services