What & Where are Name Manager / Box in Excel & Google Sheets?
This tutorial will demonstrate how to create and edit range names in Excel and Google Sheets.
Range names allow you to name a cell or range with a logical name, thereby making it easier to refer to the range in formulas. For example, instead of referring to a range of cells as C5:C7, you could name the range January.
Using the Name Box
The name box in Excel displays the range name of the current selection.
1. To create a named range, highlight the cells you want to name: for example, C5:C7.
2. Click in the box in the top left-hand corner of the screen (it will currently show the cell address: for example, C5). This box is known as the name box.
3. Type in the text that you want to name that range and press Enter.
When you select the cells that you have named, the name will now appear in the name box.
What is Name Manager?
To view the named ranges in a worksheet, you can access Name Manager.
In the Ribbon, select Formulas > Defined Names > Name Manager.
Name Manager will show all of the named ranges in the worksheet and workbook.
Creating a Range Name From the Name Manager
1. To create a range name from the name manager, click New…
2. Type in the Name of the range and select the Scope. This can either be for the individual sheet or for the entire workbook.
NOTE: If you set the name to be contained in an individual sheet, you cannot use that name on a different sheet.
3. Select the up arrow at the right of the Refers to box and highlight the cells you want to name.
4. Click the down arrow at the right-hand side of the box to return the New Name box and click OK.
5. The new name will appear in the list of range names. Click Close.
6. In the worksheet, highlight the cells that have just had the name assigned to them. The name given to the range of cells will now appear in the Name Box.
7. Click the drop-down arrow to the right of the name box to see a list of all the range names in the workbook.
Editing a Range Name
To edit the range of cells in a named range, use the name manager.
1. In the Ribbon, select Formulas > Defined Names > Name Manager.
2. Select the name to edit and select Edit…
3. Amend the range of cells that the name refers to using the up arrow to the right of the Refers to box and selecting the range of cells required.
4. Click OK to return to Name Manager. The amended cell range of the range name will be shown (e.g., F5:F8 instead of F5:F7).
ALTERNATIVELY
1. Open Name Manager as above.
2. Select the range name in Name Manager, and then select the up arrow to the right of the Refers to box directly in the name manager.
3. Adjust the range of cells that the named range refers to by selecting them in the worksheet, and then click on the down arrow to the right of the Refers to box.
4. Click on the green tick to amend the range name and then click Close.
Deleting a Range Name
To delete a named range, use the name manager.
1. In the Ribbon, select Formulas > Defined Names > Name Manager.
2. Select the Name to delete, and then click the Delete button.
3. Click OK to delete the range name, and then click Close.
Using Named Ranges in Google Sheets
Creating a Named Range in Google Sheets
You can create a range name in Google Sheets using the name box, just as you do in Excel.
Highlight the cells to name, type the name in the name box, and then press Enter to create the range name.
To view all the range names in the Google sheet, click the drop-down arrow to the right of the name box.
To view the Google Sheets equivalent of Excel’s Name Manager, click Manage named ranges.
OR
In the Menu, select Data > Named Ranges.
A list of all named ranges in the sheet will be shown.
Editing a Named Range in Google Sheets
1. In the Menu, select Data > Named Ranges.
2. To edit a named range, click on the range name and click the Edit button.
3. Select the data range box.
4. Amend the range of cells for the range name by selecting the cells in the spreadsheet, and then click OK.
5. Click Done to update the named range.
Deleting a Named Range in Google Sheets
1. In the Menu, select Data > Named Ranges.
2. Click on the range name and click the Edit button.
3. Click the Delete button.
4. Click Remove to remove the range name.
