Defining Microsoft Excel Named Reference

Defining MS Excel Named Reference

A cell reference refers to a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data that you want to use in a formula. With cell references, you can use data that is contained in different parts of a worksheet in one formula, or you can use the value from one cell in several formulas. You can also refer to cells on other worksheets in the same workbook.

Although cell numbers are at the foundation of everything Excel does, it's much easier to remember names, such as Item Number and Quantity, than it is to remember cell numbers, such as A1:C100. Excel makes this easy.

A name is a meaningful shorthand or a label that makes it easier to understand the purpose of a cell reference or constant. For example, the number 210 and the text "Quarterly Earnings" constants but Quarterly Earning makes more meaning. You can use the labels on a table to refer to the cells within its columns and rows. Examples of labels are “unit Price”, “Quantity” and “Total.” Alternatively, you can create descriptive names to represent cells, ranges of cells, formulas, or constant values.

Creating Microsoft Excel Named Range

1. To name a cell or range, select the range of cell(s) you want define name for.
2. Type the desired name in the Name box and press ENTER key.

OR

1. Right-click the selected range, click Name a Range.
2. Type the desired name and click Ok.

Change Cell Reference to a Named Range

If you have defined a name to a cell reference after you have entered a cell reference in a formula, you would need to update the existing cell references to the defined names. Do one of the following:

1. Select the range of cells that contains formulas in which you want to replace cell references with defined names.
2. Select a single, empty cell to change the references to names in all formulas on the worksheet.
3. On the Formulas tab, in the Defined Names group, click the arrow next to Define Name, and then click Apply Names.
4. In the Apply names box, click one or more names, and then click Ok.