Defined Names in Excel

A Defined Name is a text descriptor created by the user/developer to describe content, meaning or use of a cell or range of cells, a constant, or a formula. Once defined this can be used in place of cell addresses or other content and makes formulae much easier to understand and/or maintain. 

As an example the formula:

=B2*C2

explains nothing to the user unless they already know the meaning of the cell content. If, however, these cells are assigned Defined Names, as below, the formula could be re-written such that it can be clearly understood e.g.:

=iChargeableDailyRate*iDaysWorked

Quite clearly, even a new user would immediately be able to understand and maintain this formula. For further detail on named ranges, I recommend reading Chip Pearson’s page on Defined Names, rather than have me repeat it here.

In the same way, although the user interface for working with Defined Names in Excel 2003 and earlier is relatively primitive, Jan Karel Pieterse has created a nice add-in, available for free at www.jkp-ads.com/officemarketplacenm-en.asp, and there is no reason for me to cover it in any detail here.

I heartily recommend using Defined Names as frequently as possible, at least when creating a workbook or application which will be used more than once, simply because of the advantages inherent in doing so:

  1. formulae are easier to read and understand
  2. VBA code is not adversely affected by moving cells
  3. range references can be changed globally in a single place

Further, when defining names, it is best to follow a structured method of nomenclature. Ideally names should explain both what type they are and what their function is e.g. I might define the following:

  • iChargeableDailyRate: a numeric value, defining the daily rate charged
  • iDaysWorked: a numeric value, defining the number of days worked
  • fAmountCharged: a formula, used to calculate the amount charged
  • rChargeableDailyRate: a range, defining the cell containing the value of iChargeableDailyRate etc

Defined Names can be used in many ways, some of which are covered elsewhere e.g.

  • Sheet Level Named Ranges
  • Named Constants
  • Named Formulae
  • Relative Named Ranges
  • Dynamic Named Ranges
  • Further Dynamic Named Ranges

Applies to Excel 2003, Excel 2007, Excel 2010

Advertisements

One thought on “Defined Names in Excel

  1. Pingback: Dynamic Named Ranges | More from…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s