Excel Tip: A short introduction to named ranges

Named ranges can generate intense debate among Excel experts as everyone has their own views about how often they should be used - from those who argue that you should never directly address a cell in a formula (and should always give the cell (or range of cells) a range name, to those who only use them if they have to.

I would put myself somewhere in between the two extremes. There is certainly no denying that they can be a useful tool.

I don't expect to resolve this debate here - I merely hope to introduce you to named ranges and how to use them.

Put simply, named ranges allow you to name a cell (or range of cells) so that this name can be used in a formula instead of the actual cell reference(s).

This provides the following advantages:

• It can be easier to see what's going on in a formula if clear names are used;
• If a range needs to be extended or moved, the range can be edited once, rather than in every formula that refers to the range;
• Whereas inserting rows and columns, etc. will update both formulae referring directly to the moved cells and ranges, direct references from macros will not be updated. If the macro refers to a range name then this will not be a problem - as the range will have updated;
• Dynamic ranges - the definition of a range does not have to be a fixed reference and can be a formula. It can therefore be dependent on other variables in the spreadsheet .

There are two main ways to define a range.

The simplest way (not possible for dynamic ranges), is to select the range with the mouse and enter the chosen name in the Name Box at the top left of the screen, just below the ribbon. This will give the selected cells that range name, and you can re-select those cells at any time by selecting the name from the dropdown in the Name Box.

Note that a Range Name cannot include certain characters (most notably, spaces), but you will be told if you have tried to allocate an invalid name.

The second way is to use Define Name on the Formulas Ribbon. In the dialog box that you see when you click Define Name, you can enter your chosen range name in the box at the top, and the selected range in the box at the bottom, which will default to the cell(s) you had selected before clicking Define Name. You will not normally need to use the other two fields in the dialog box, but if you wish you can choose to confine the scope of the range to one particular worksheet, rather than the whole workbook and/or add some comments.

Note that a Range Name cannot include certain characters (most notably, spaces), but you will be told if you have tried to allocate an invalid name.

The big advantage of the second method is that you can enter a formula in the "Refers to" box, rather than just a direct reference. This can give you a dynamic range, that changes with the contents of the spreadsheet. You may, for example, use functions such as OFFSET or INDEX to determine the dynamic range.

Finally, you can use the Name Manager (next to Define Name on the Formulas Ribbon) to edit existing ranges all in one place.

To use a range name in a formula just enter the name. So let's say we have named the range A1:A4 as sales. If we wished to total these cells we could type either:

=SUM(A1:A4)

or

=SUM(sales)

And say we entered the VAT Rate in cell B1 and named it vatrate. To return the gross sales including VAT, instead of typing:

=SUM(A1:A4)*(1+B1)

we could type:

=SUM(sales)*(1+vatrate)

but now we have all of the advantages mentioned above!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".