Formula Errors

Prevent Duplicate Named Ranges When Copying Excel Sheets

Khadija KhanKhadija Khan Nov 18, 2026 869 views

Question details

The user wants to avoid unwanted duplicate named ranges when copying Excel worksheets.

Prevent Duplicate Named Ranges When Copying Excel Sheets
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.
Before you start

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.

Solution 1Recommended

Copy the Used Range to a New Worksheet

Use this workaround to avoid copying the entire worksheet, which may create unwanted sheet-level names.

1
Add a blank sheet

Click the plus button beside the worksheet tabs to create a new worksheet.

2
Copy the used cells

On the source sheet, select the cells containing your data and formulas, then press Ctrl+C or choose Home > Copy.

3
Paste into the new sheet

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.

4
Check the names

Open Formulas > Name Manager and review the Scope column for unwanted names. Test formulas that depend on names or table references.

Copy the Used Range to a New Worksheet
Check the result: Copying cells may not reproduce every worksheet setting, and pasted formulas or tables can still introduce or depend on names. Verify the result before relying on the new sheet.
Free Microsoft Office alternative

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.

Work with formulas and cell ranges in WPS SpreadsheetsOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceGet a free, lightweight office suite
microsoft office alternative - wps office

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.