Range names in Excel

Range names are a useful tool in Excel that allows you to assign a name to a single cell or a range of cells. This tutorial describes how to create range names including the dynamic range and how to work with them.

For example, you can assign the name “TaxRate” for tax rate assumption in inputs tab cell C10. Then use the name “TaxRate” anytime even in a different sheet in the workbook such as = C3 * TaxRate instead of = C3 * Inputs!C10. The advantage of using range names:

• Formulas are more readable especially if it is used frequently in a complex worksheet
• You don’t have to worry about anchoring the formula when you copy/paste because named ranges always use absolute cell reference

How to create range names in Excel

A range name may contain letters, numbers and underscores but it must be one word with no spaces or special punctuation characters. It also cannot be the same as a normal cell reference such as “A1” (row 1, column A in Excel.).

• Create the cell or ranges to be named
• Click in the Name box (left of the formula bar)
• Type the name and press the Enter key

Screenshot: Define a name for specific cell

Refer to/delete range names

To go to/refer to the cell or range that contained range names press the F5 key, select the desired range name and press OK. For example, by selecting Year as shown in the screenshot below will bring you to range cells C45:C53 in the workbook

Screenshot: Go To range names

The above can also be performed by clicking Insert -> Name -> Define in the Excel menu as shown in the screenshot below. You can also create or delete a range name from this dialog box.

Screenshot: Add/Delete/Refer to range names

Work with range names

Use range names in formulas

Range names can be used in formulas and it is especially useful if it is used frequently in a complex worksheet. Refer to the example in the screenshot below. Lets name cells C45:C53 as “Year” and cells F45:F53 as “NetIncome”. In addition we also named a constant (1000) as “thousand”.

Screenshot: Working with range names

Formulas in cells F55, F57 and F58 are:

• F55 =LOOKUP(F55,Year,NetIncome)/Thousand
• F57 =AVERAGE(NetIncome)/Thousand
• F58 =SUM(NetIncome)/Thousand

Offsheet data validation

Another useful application for range names is in offsheet data validation. This means the range names can be stored in a different sheet from the cell that we would like to apply the data validation. To apply data validation, go to Data -> Validation -> Settings -> click F3 and paste in the range name in Source. Refer to our tutorial titled Data Validation to learn more about this topic.

Dynamic range names

Sometimes it is useful to use dynamic formula to define a range although the concept is harder to grasp for unfamiliar Excel user. As new items are added, the range will automatically expand.

For example, you would like to create dynamic range names of Year and Net Income in a tab called Data.

Screenshot: Creating dynamic range names

• Click Insert -> Name -> Define in Excel menu
• Type in a new range name for example “YearDyna” for dynamic year
• In the Refers to box enter an Offset formula, eg. the formula in YearDyna would be =OFFSET(Data!C6,0,0,COUNTA(Data!C:C),1) 

The arguments used in the Offset function are:

• Reference cell: Data!C6
• Rows to offset: 0
• Columns to offset: 0
• Number of rows: Count the first non-blank entries in column C
• Number of columns: 1 (we can also create a dynamic number of columns, refer to example).

To avoid and recommended

COMMENTS