How to Edit / Change Named Ranges in Excel & Google Sheets

This tutorial will demonstrate how to edit named ranges in Excel and Google Sheets.

 

 

When you have existing range names in Excel, you can edit these range names with Name Manager.

Name Manager

Start by opening the Excel file that contains the named ranges. Then, in the Ribbon, select Formulas > Defined Names > Name Manager.

 

 

Name Manager contains all the range names that are contained in the current workbook.

 

 

There are two ways you can edit a range name in Name Manager.

Amending a Range Name With the Edit Button

1. Select the range name and then select Edit…

 

 

2. To edit the range of cells that the name refers to, click on the small up arrow to the right of the Refers to box.

 

 

3. Highlight the new range in the range name, then click the little down arrow to return the Edit Name box.

 

 

4. Click OK to return to Name Manager to confirm the amendment.

 

 

Amending a Range Name With Refers to

1. In Name Manager, select the range name then click on the small up arrow to the right of the Refers to box.

 

 

2. Then highlight the new range you want the name to refer to and click the little down arrow to return to Name Manager.

 

 

3. To confirm the amendment, click the little green check mark to the left of the box showing the selected range.

 

 

If you skip this step, then when you click Close, a message box will appear.

 

 

You can click Yes to save the amendment, or No to revert back to the original selected range of cells for the range name.

Amending the Name of a Range

Name Manager also allows you to amend the name of the named range.

In Name Manager, (1) select the range name that needs amending and (2) click Edit…

 

 

You can then amend the name of the range as required in the Name box and click OK.

 

 

The amended range name shows up in Name Manager.

 

 

How to Edit Named Ranges in Google Sheets

Amending or renaming range names in Google Sheets is similar to making those changes Excel.

1. With the Google sheet that contains the range names open, in the Menu, select Data > Named ranges.

 

 

2. Select the range name that needs amending and click the Edit button.

 

 

3. You can then (1) change the name and/or (2) amend the range of cells that are required.

 

 

4. Click OK then Done to confirm the amendments.