Document Editing Problems

How to Copy Excel Drop-Down Lists to Another Workbook

Muhammad Talha Sep 30, 2026 872 views

Question details

The user needs to copy an Excel worksheet containing data-validation drop-down lists into a different workbook while preserving the functionality of those lists.

How to Copy Excel Drop-Down Lists to Another Workbook

Product: Microsoft Excel
Device & OS: not provided
Scenario: Transferring a fully formatted worksheet that includes active drop-down menus to a separate, new, or existing workbook.
Observed behavior: The user wants the drop-down lists to remain functional in the destination workbook. In some cases, the lists break or fail to open if they reference cells on an uncopied worksheet.

Before you start

Ensure both the source workbook containing your drop-down lists and the destination workbook are open in Excel before attempting to copy the worksheet.

Solution 1Recommended

Use the Move or Copy Feature to Duplicate the Worksheet

The most reliable method to transfer drop-down lists with hard-coded sources is to copy the entire worksheet directly to the new workbook.

This method safely transfers all cell formatting, formulas, and data validation rules associated with the worksheet. It works flawlessly for drop-down lists where the list items were manually typed into the Data Validation menu.

Open both workbooks

Launch Excel and open both the source workbook containing your drop-down lists and the destination workbook where you want to copy them.

Access Move or Copy

Right-click the worksheet tab at the bottom of the source workbook and select 'Move or Copy' from the context menu.

Select the destination workbook

In the dialog box, click the 'To book' drop-down list and choose your destination workbook. Select the specific location where you want to insert the sheet.

Create a copy

Check the 'Create a copy' box at the bottom of the window, then click 'OK'. The sheet and its drop-down lists will be successfully duplicated into the new workbook.

Use the Move or Copy Feature to Duplicate the Worksheet

Tip: If you are copying to a brand new file, you can select '(new book)' from the 'To book' drop-down list to create a new file automatically.

Solution 2

Fix Broken Drop-Down Lists by Copying Source Data Sheets

If your copied drop-down lists do not work, they likely rely on cell references from a separate data sheet that wasn't copied.

Easily Manage and Copy Drop-Down Lists with WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive platform for creating, managing, and copying data-validation drop-down lists across multiple workbooks without losing any functionality.

1. Open files in WPS Office: Launch WPS Office and open both your source and destination spreadsheets. They will conveniently open in the same tabbed workspace.
2. Copy the worksheet: Right-click the sheet tab containing the drop-down lists, select 'Move or Copy Sheet', and choose your destination workbook from the drop-down menu.
3. Confirm and copy: Ensure the 'Create a copy' checkbox is ticked and click 'OK'. Your drop-down lists and formatting are now perfectly duplicated.

Fully compatible with Microsoft Excel (.xlsx) files and advanced data validation rules.Intuitive 'Move or Copy' interface for seamless and error-free worksheet transfers.Multi-tab workspace makes it easy to drag and drop sheets between open workbooks.Free, lightweight, and fast-loading alternative for daily spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why did my Excel drop-down list disappear when pasting into another workbook?

If you simply copy and paste cells using standard shortcuts (Ctrl+C and Ctrl+V), Excel might only paste the cell values and basic formatting, leaving the underlying data validation rules behind. Using the 'Move or Copy' sheet method, or using 'Paste Special > Validation', ensures the drop-down rules are transferred.

Can I copy just the drop-down list without copying the entire worksheet?

Yes. You can copy the specific cell containing the drop-down list, navigate to the destination workbook, right-click the target cell, select 'Paste Special', and then choose 'Validation'. This will paste only the drop-down rules without altering your existing cell values or formatting.

How do I fix a '#REF!' error in my copied drop-down list?

A '#REF!' error occurs when your drop-down list relies on a range of cells from a worksheet that was not transferred to the new workbook. To fix this, you must either copy the missing source data sheet into your new workbook or edit the data validation rule to reference an existing cell range in the new workbook.

Document & File Issues