See all How-To Articles

How to Resolve a Name Conflict for a Named Range in Excel

This tutorial will demonstrate how to resolve a name conflict in Excel.

 

NameConflict Intro

 

When you try to copy two or more sheets from one file to another, if the files both have the same range names in them, a name conflict error could occur. Excel will warn you that the range name already exists and ask if you wish to rename the range name or use the existing version of that name.

Resolve the Name Conflict

Using Existing Versions

1. Select two or more sheets in the workbook that contain range names that are identical in the destination workbook.

2. Right-click on the sheet tabs and select Move or Copy.

 

NameConflict CopySheets

 

The Name conflict box will appear.

 

Name Conflict Dec

 

3. Click Yes to use the existing version of the name. This mean that the range name coming in from the copied sheet will be removed and the original name in the destination sheet will be used.
Click Yes to All to use all the existing range names.

Rename Ranges

If you select No to rename the version of the range name, you can give the existing range name from the copied sheet a new name in the current sheet. This is useful if the range name has been used in formulas.

1. Click No to rename the range name.

 

NameConflict No

 

2. Type in the New name.

 

NameConflict Rename

 

3. Click OK.

 

NameConflict New Name

 

As shown in the picture above, the original named range of December from the source sheet is renamed to Dec.

Duplicate Range Names

If you select a single sheet to copy or move to a book where some destination names are identical, you will not receive this warning. Excel will copy the sheet with its range names but alter the scope of each name to the sheet instead of the workbook.

Right-click on one sheet to copy to another open workbook and select Move or Copy.

 

NameConflict DuplicateSheet

 

No warning will appear. However, if you look in the name manager of the destination book, you will see that there are two versions of some of the range names. See the picture below. The original names have Workbook as the scope, while the copied names have a scope of just their own sheet (e.g., 3rdQuarter).

 

NameConflict Scope