Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

ExcelZone Compendium: Working with named ranges (Excel 2007 update)

by
18th Aug 2008
Save content
Have you found this content useful? Use the button above to save it to your profile.

Excel's subtleties in handling references - for example, with absolute or relative locations depending on whether there is a $ sign before the cell figures - can make it difficult to interpret or link to specific blocks of cells. Learning the ways in which you can manage cells and data as named ranges can make life a lot simpler and easier, explains Simon Hurst.

Practical issue: Speed navigation and improve reliability or references and formulae
Use Excel range names to:

  • Make it easier for users to understand a spreadsheet formula and easier to set up and work with absolute formula references
  • Make linking to Excel cells or ranges from another workbook or a different application more reliable
  • Allow Excel functions to adjust to changing numbers of rows and columns
  • Make it easier and quicker to select areas for printing or formatting for example.

Solutions: Readable formula

A typical Excel formula might contain references such as =C5*data!B7. Without opening the data sheet and referring to the contents of B7 or hopefully an adjacent label, it would be difficult to know what the formula seeks to achieve. However, we can give the cell a name by clicking on it, and then typing the name (without spaces) into the Name box to the left of the formula bar:

We could then construct our formula as =C5*VATrate. If range names are used sensibly they can help a formula become "self-documenting" making it much easier for users to interpret and understand what the spreadsheet is doing.

Easier absolutes

If you are using normal cell references, then you will need to use the dollar signs to convert relative formulae to absolutes - ie formulae that refer to a fixed cell position rather than how many rows and columns the cell is away from the formula cell. However, if you use a range name for a single cell, then any reference to it will automatically be absolute.

Including range names in a formula

When constructing a formula you can just type in the range name or you can press F3 to bring up the Paste name dialog and select your name from the list of available names. In Excel 2007, the formula AutoComplete feature will include range names in the list of matching items. You can double-click on the name, or use the arrows to select it and then press the tab key to insert it into your formula.

Not just references to cells

Although range names are generally used to refer to individual cells or blocks of cells, they can also be used to refer to a formula. For example, you may want to refer frequently to the current time and date. To allocate a formula to a range name choose Insert, Name-Define (Excel 2007: Formulas ribbon tab, Defined Names section, Define name) then type in the required range name and enter the formula in the "Refers to:" text box (note that this screen shot is from Excel 2007 and includes the Scope of the name, the scope field is not present in previous versions of Excel. See below):

This could then be used in a formula such as:
="Printed on " & CurrTime
and would return:
Printed on 13 August 2008 15:31

Defining the scope of a range name

By default, a name applies to the whole workbook, but it is also possible to define a name so that it just applies to a particular sheet. In versions prior to Excel 2007 you need to include the name of the sheet in the range name to limit the scope of the name to that sheet. So, for example a range name of "sheet2!rate" will only show up in the name box as "rate" in sheet2. Range names limited to sheet scope can be accessed from other sheets by using the full name, including the sheet name part:

The Excel 2007 Define Name dialog (shown above) includes the ability to directly define the scope to the workbook or to any sheet in the workbook.

Another key addition to Excel 2007 is the Name Manager which can be found in the Formulas ribbon tab, Defined Names section. Before the introduction of the Name Manager, anyone who made extensive use of Excel range names either had to make do with the minimal management tools include in the Insert, Name options or use one of several third-party Excel Add-ins. The Excel 2007 Name Manager allows you to see all the names in use together with their scope and any comments that you have entered. In addition, it includes a 'Filter' button that lets you display names by whether they are 'scoped' to the worksheet or the workbook, whether they have errors or not and whether they are defined names or table names. You can click on a name and click the Edit button to display the 'Edit Name' dialog for that particular name.

Linking to Excel cells or ranges

If you create a dynamic link to an Excel cell from Microsoft Word for example, then if you change the structure of the Excel workbook so the linked cell is moved, Word will still point at the original cell reference, which will now contain different data. If you name the cell before you link to it, the other application should then be linked to the name. This allows Excel to keep track of the name, making sure that, even after a change in structure, you still link to the right information. However, do be aware that there is an issue with Office 2003, Service Pack 3 that can cause Word to create a link to the absolute cell reference, even if that cell has been given a range name.

Similarly, if you use functions such as Vlookup() or Sumif() that refer to ranges of cells, then naming the ranges before you refer to them from the functions, not only makes the function more understandable in the same way as a normal cell reference incorporating range names, but also means you can vary the range that the functions refer to just by changing the range of cells to which the names apply:
=SUMIF(Analysis, Sales, Amount)

Two click selections

So far we have concentrated on naming individual cells or allocating a formula to a range name. You can also apply a range name to a block of cells. This allows you to select that block for formatting, copying, printing or whatever by clicking the name box dropdown from anywhere within your workbook and then clicking on the relevant name.

Further reading

10 quick range name tricks by Gail Perry, AccountingWEB.com
Creating local range names
New Range Name features in Excel 2007
Excel Compendium: worksheets and ranges
ExcelZone Compendium Archive - further articles and queries on worksheets and ranges

Subscribe to the ExcelZone newswire
To keep up with spreadsheet issues, click the graphic to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.