Use named ranges

Names are meaningful notations that can be assigned to a cell or cell range and used to simplify working with formulas. Creating a formula, you can insert a name as its argument instead of using a reference to a cell range. For example, if you assign the Annual_Income name to a cell range, it will be possible to enter =SUM(Annual_Income) instead of =SUM(B1:B12). Thus, formulas become clearer. This feature can also be useful in case a lot of formulas are referred to one and the same cell range. If the range address is changed, you can make the correction once by using the Name Manager instead of editing all the formulas one by one.

There are two types of names that can be used:

  • Defined name: an arbitrary name that you can specify for a certain cell range. Defined names also include the names created automatically when setting up print areas.

  • Table name: a default name that is automatically assigned to anew formatted table(Table1,Table2etc.). You can edit this name later.

If you have created a slicer for a formatted table, an automatically assigned slicer name will also be displayed in the Name Manager (Slicer_Column1, Slicer_Column2 etc. This name consists of the Slicer_ part and the field name corresponding to the column header from the source data set). You can edit this name later.

Names are also classified by Scope, i.e. the location where a name is recognized. A name can be scoped to the whole workbook (it will be recognized for any worksheet within this workbook) or to a separate worksheet (it will be recognized for the specified worksheet only). Each name must be unique within a single scope, the same names can be used within different scopes

Create new names

To create a new defined name for a selection:

  1. Select a cell or cell range you want to assign a name to.

  2. Open a new name window in a suitable way:

    • Right-click the selection and choose theDefine Nameoption from the contextual menu,

    • or click theNamed ranges icon on the Home tab of the top toolbar and select theDefine Nameoption from the menu.

    • or click the Named rangesbutton on theFormulatab of the top toolbar and select theName manageroption from the menu. Choose optionNewin the opened window.

    The New Name window will open:

  1. Enter the necessaryNamein the text entry field.

    Note: A name cannot start with a number, contain spaces or punctuation marks. Underscores (_) are allowed. Case does not matter.

  2. Specify the nameScope. TheWorkbook scope is selected by default, but you can specify an individual worksheet selecting it from the list.

  3. Check the selectedData Rangeaddress. If necessary, you can change it. Click the icon - theSelect Data Range window will open.

    Change the link to the cell range in the entry field or select a new range on the worksheet with the mouse and click OK.

  4. Click OK to save the new name.

To quickly create a new name for the selected cell range, you can also enter the desired name into the name box located to the left of the the formula bar and press Enter. The name created in such a way is scoped to the Workbook.

Manage names

All the existing names can be accessed via the Name Manager. To open it:

  • click theNamed ranges icon on the Home tab of the top toolbar and select theName manageroption from the menu,

  • or click the arrow in the name field and select theName Manageroption.

Use names when working with the spreadsheet

To quickly navigate through cell ranges, you can click the arrow in the name box and select the necessary name from the name list : the data range that corresponds to this name will be selected in the worksheet.

Note: The name list displays the defined names and table names scoped to the current worksheet and to the whole workbook.

To add a name as an argument of a formula:

  1. Place the insertion point where you need to add a name

    Make one of the following steps:

    • enter the name of the necessary named range manually using the keyboard. Once you type the initial letters, the Formula Autocomplete list will be displayed. As you type, the items (formulas and names) that match the entered characters are displayed in it. You can select the necessary defined name or table name from the list and insert it into the formula by double-clicking it or pressing the Tab key.

    • or click the Named ranges icon on the Home tab of the top toolbar, select the Paste name option from the menu, choose the necessary name from the Paste Name window and click OK:

    Note: The Paste Name window displays the defined names and table names scoped to the current worksheet and to the whole workbook

To use a name as an internal hyperlink:

  1. Place the insertion point where you need to add a hyperlink.

  2. Go to theInserttab and click the Hyperlinkbutton.

  3. In the openedHyperlink Settingswindow, select theInternal Data Rangetab and choose a named range.

  4. Click OK.