Named Cells, Ranges and Values

Formulas and functions are usually created using references to other cells on the spreadsheet. Where you need to refer to a fixed cell and still be able to duplicate the formula around the spreadsheet then you can use fixed cell references as explained in the Simple Formulas and Functions reference guide.

These absolute and mixed cell references can sometimes make the formulas difficult to read, especially when you get into creating the likes of VLOOKUP and SUMIFS formulas. Therefore we can name cells and ranges or set a value to a name for use in formulas to avoid this.

There are multiple approaches to naming cells. First you need to know where to see the names you create. All cells in Excel have a default name – their cell reference, which is displayed in the Name Box. The Name Box is located next to the formula bar underneath the ribbon.

To name a cell or range using the Name Box:

1. Select the cell or range to be named
2. Click into the Name Box
3. Type in the desired name This name can be made up of a combination of letters, numbers, full stops or underscores. It cannot be the same as cell references and can’t include spaces. It must be less than 255 characters in length and is case sensitive
4. Press Enter key on keyboard

To name a cell or range using the ribbon:

1. Select the cell or range to be named
2. On the Formula tab, in the Defined Names group click the Define Name button
3. In the New Name dialog box type in the desired name into the Name field
4. If required add a comment
5. Click OK

If you have set out a table in your spreadsheet with titles at the start of each column and row within the table then you can use these titles to create named ranges.

To name cells based on table headings:

1. Select the table containing the cells to be named
2. On the Formula tab, in the Defined Names group click the Create from Selection button
3. In the Create Names from Selection dialog box, choose the options which indicate where the names to be used are
4. Click OK Names need to be unique within a spreadsheet file (workbook) and as such can be used to navigate through the workbook.

To navigate to a cell or range using its name:

1. Click on the arrow head in the Name Box
2. Choose the desired name

Names can also be used for fixed values which you do not want to display on the worksheet.

To create a named value:

1. On the Formula tab, in the Defined Names group click the Define Name button
2. In the New Name dialog box type in the desired name into the Name field
3. If required add a comment
4. In the Refers to box enter the value or formula for the named value
5. Click OK

You can use the name of named cells, ranges or values in any formula in place of a cell reference via the button, on the Formula tab, in the Defined Names group.