## 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

FastExcel
The best tool to optimise your Excel model!

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
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.