More uses for Excel range names

Posted by Simon Hurst PM | on Thu, 14/01/2010 - 14:49 5993

Just how important are range names?
In early January a momentous event occurred on AccountingWEB.co.uk. The current holder of the 'most reads for a single article' crown was deposed. For several years, the Tax links page had led the way, but around lunchtime on 4 January 2010 an IT article overtook it with more 213,000 reads. That article covered New Range Name features in Excel 2007.

The range name article did have the considerable advantage of making it to the top of the Excel 2007 online help for 'range names' - so if you have Excel 2007, try typing 'range names' into the help search box.

Given the popluarity of the subject matter, ExcelZone asked if I could provide a follow-up article illustrating a few more examples of how range names can make life easier when using Excel. Here are those ideas.

Implicit intersections
You don’t necessarily need to use range names to work with implicit intersections, but they certainly make it easier and make the resulting formulae more understandable. In this example we have a range of Sales values and a single VATrate figure:

Excel range name - Sales example

Data validation lists
For an implicit intersection a range name is useful but not essential. When working with Data Validation, in some versions of Excel the only way to refer to a list held on a different sheet is to use a range name. If we had a list in a sheet called ‘Regions’ and wanted to use that list as the basis for Data Validation in another sheet, prior to Excel 2007 we wouldn’t be able to enter =regions!A2:A4. Excel would display a message saying that you can’t use references to other worksheets or workbooks as a data validation criteria.

Use a range name in a chart series
One of the comments to the original Excel 2007 range names article asked how to use names for chart series. Having created a chart, you can change the series cell references to names by selecting the series on the face of the chart and then replacing the cell reference in the formula bar with the corresponding range name. Note that you need to leave the Sheet! reference and just replace the actual cell references – Excel will then replace the sheet reference with the workbook name.

About the author
Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping practitioners make effective use of technology. He is also the author of AccountingWEB's Office ProductivITy Kit and '100 Microsoft Office Time-saving Tips'. For more information, visit the The Knowledge Base website.

Related material in ExcelZone

Continued...