Excel allows you to assign a name to an individual cell or a range of cells. That name can be used in a formula and can help to make the formula easier to understand. Names can also make it easier to navigate around a large spreadsheet. In Excel 2007 and 2010, names have a scope assigned to them, meaning that a name can be a worksheet-level name or a workbook-level name.

Defining a Name – 1

name3.png

Select the cell or cells that the name will refer to

Click the Formulas tab on the Ribbon

Click the Define Name button in the Defined Names group

Defining a Name – 2

name4.png

The New Name dialog box is displayed

Type an appropriate name into the Name field (1). The name should describe the data that will be stored in the cell(s)

Select the appropriate value from the Scope drop down list (2). In the example above, there are 4 worksheets – North, South, East and West. Each sheet is laid out identically. The range B2:E2 in each sheet needs to be assigned a name. In previous versions of Excel, each name would have to be unique, for example, SalesNorth, SalesSouth, etc. The Scope allows you to restrict the use of the name to a specific worksheet or, by selecting Workbook from the list, make it available throughout the workbook. Names must be unique within their scope. So you cannot have two identical names in the same sheet, but you can use the same name in different sheets.

The optional Comment field (3) is used to document the name

Refers to (4) indicates the range that the name refers to

Click the OK button

The Names Box

name1.png

The Names Box is to the left of the Formula Bar, just above columns A and B. Clicking the dropdown arrow will display a list of Names in the current workbook. To quickly navigate to a named cell/range, simply select the name from the list.

To quickly create a name, select the cell/range that the name will refer to, type the name into the Names Box and press Enter. Names created in this way will automatically have Workbook scope applied to them.

If a name has been assigned to a cell or range, when that cell or range is selected, the name will be displayed in the Name Box – in the above screenshot, G1 has been assigned the name VAT

Using Names in a Formula

name2.png

In the above screenshot, G1 has been assigned the name VAT. Instead of typing G1 into the formula in C2, the name VAT has been used. Not only is the name easier to remember than the cell references, but also the formula will make more sense to other users of the worksheet.

An additional benefit is that Names are always absolute. In the example above, when the formula =VAT*B2 is copied down the column, the new formula will read =VAT*C3, etc.