See all How-To Articles

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

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

 

EditRangeNames Intro

 

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.

 

EditRangeNames Ribbon

 

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

 

EditRangeNames Name Manager

 

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…

 

EditRangeNames EditButton

 

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.

 

EditRangeNames EditRange

 

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

 

EditRangeNames HighlightRange

 

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

 

EditRangeNames AmendedName

 

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.

 

EditRangeNames RefersTo

 

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

 

EditRangeNames Amend RefersTo

 

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

 

EditRangeNames ConfirmChange

 

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

 

EditRangeNames ConfirmClose

 

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…

 

EditRangeNames EditButton

 

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

 

EditRangeNames ChangeName

 

The amended range name shows up in Name Manager.

 

EditRangeName ChangedName

 

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.

 

EditRangeNames GS Ribbon

 

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

 

EditRangeName GS Edit

 

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

 

EditRangeNames GS Amend

 

4. Click OK then Done to confirm the amendments.