Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Defined Names > Use
Deze pagina in het Nederlands

Range Names In Excel

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, 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:

=Apples

The Validation will now be accepted by all versions of Excel.