Prevent Duplicate Named Ranges When Copying Excel Sheets
Question details
The user wants to avoid unwanted duplicate named ranges when copying Excel worksheets.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Copying worksheets that contain formulas referring to workbook-level named ranges, including names that refer to table ranges.
- Observed behavior
- Excel may create additional sheet-level names when a worksheet is copied.
Open Formulas > Name Manager and check the Scope and Refers To columns for names used by the sheet. Identify any names that refer to Excel tables and note whether those tables need to expand automatically.
Copy the Used Range to a New Worksheet
Use this workaround to avoid copying the entire worksheet, which may create unwanted sheet-level names.
Click the plus button beside the worksheet tabs to create a new worksheet.
On the source sheet, select the cells containing your data and formulas, then press Ctrl+C or choose Home > Copy.
Select the corresponding starting cell on the new sheet and choose Home > Paste. Check the pasted formulas, formatting, and any tables; recreate table formatting or other sheet settings if needed.
Open Formulas > Name Manager and review the Scope column for unwanted names. Test formulas that depend on names or table references.

Replace a Table-Based Name With an Absolute Range
Try this when a duplicated name refers to a table range and a fixed range is suitable.
Work With Spreadsheet Ranges in WPS Office
WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Open compatible Excel files in WPS Spreadsheets to continue your work and move between office suites more easily.

Frequently Asked Questions
Why does Excel add a sheet-level name when I copy a worksheet?
Excel may create a local name to preserve references used by the copied sheet. A name referring to a table range can be associated with this behavior in some workbooks.
How can I tell whether a name is local or global?
Open Formulas > Name Manager and look at the Scope column. A worksheet name indicates local scope; Workbook indicates global scope.
Can I delete the duplicate names from Name Manager?
You can select a name in Formulas > Name Manager and click Delete, but first check whether formulas depend on it. Deleting a used name can cause formula errors.
Will an absolute range grow when I add rows to a table?
A fixed reference such as =Sheet1!$A$2:$A$100 does not automatically expand like a table reference. Update the range when needed or retain the table reference if automatic expansion is essential.
Does copying cells preserve everything from the original sheet?
Not necessarily. After using Home > Copy and Paste, check formulas, table formatting, column widths, and other worksheet settings on the new sheet.




