Defining Names in Excel or Google Sheets

Defining Names in Excel is a great tool for increasing your productivity. Named cells are extremely useful while creating Lists.

They are also very useful when you are working on large sheets, such as financial models and you need to refer to a few cells repeatedly.

By defining Names, you can easily refer to cells by using their names (e.g. tax_rate, discount_rate, etc.) instead of providing their cell references (A5, C10, etc.).

Defining Names in Excel of Google Sheets

Defining Names in Excel is a great tool for increasing your productivity. Named cells are extremely useful while creating Lists.

They are also very useful when you are working on large sheets, such as financial models and you need to refer to a few cells repeatedly.

By defining Names, you can easily refer to cells by using their names (e.g. tax_rate, discount_rate, etc.) instead of providing their cell references (A5, C10, etc.).

A desk with a keyboard, phone, and a chart

The implementation of defined names is similar in Excel and Google Sheets. Wherever there is a difference it has been called out specifically.

In the case of a similar implementation, we have used images from Excel.

There are many ways in which you can define Names for a cell or a range of cells.

Method 1 of Defining Name – Type the name directly in the name box

Select the cell or a range of cells for which you want to define a Name
Click on the Name Box in the formula bar; type the desired Name and press enter. Spaces are not allowed in the names.
You will see that instead of a cell reference (A1, B5, etc.) it will now display the Name that you had provided. In the image below, cell B1 has been given a Name - Tax
In case the Name already exists, then the cursor goes to the cell for which that Name has already been defined. If the Name is in another worksheet of the same workbook, then the cursor goes to that worksheet and highlights the cell with that Name

Define a Name by typing the desired name in the name box for cells

Method 2 of Defining Name – Define Name

Select a cell or a range of cells for which you want to define a Name
Click on Define Name in the Formulas tab. For Google Sheets, you will need to go to Data and click on Named ranges

Define a Name by typing the desired name in the dialog box that appears on clicking Define Name Button

A dialog box will appear and it will contain a default Name. This would be the same as the content of the topmost cell.

If you select an empty cell and click on define Name, then it may show a default or a suggested Name. This will be the same as the content of the cell right above the selected cell or range.

If there are spaces in the content of the cell above, then they are replaced with an _ (underscore). You can keep the suggested Name or replace it with something else of your choice.

If the cell above the one you want to define is blank, then the default or the suggested Name will also be blank, and you will need to type the desired name.

Method 3 of Defining Name – Name Manager

In the Formulas tab, click on Name Manager. The Name Manager is only available in Excel

Click on New…
Provide a 'Name' and the cell or range in the 'Refers to'
Click OK
Click Close

Define a Name by typing the desired name in the dialog box that appears on clicking New from the Name Manager Dialog Box

Scope of Names

A defined Name can be available to all the worksheets in a workbook, or you can restrict the scope to a worksheet. When you are defining a Name, use the ‘Scope’ field to define whether the name should be available only in the worksheet or the entire workbook. You can define the scope of Names in Excel. In Google Sheets, the name is available on all worksheets within a workbook.

Define the scope of accessibility of Name in the Define Name dialog box

All Excel & Google Sheets Resources