How to Copy a Dropdown or Data Validation Rule to Another Spreadsheet Without Breaking It
You built a clean dropdown in one Google Sheets file, with the right options, chip colors and a rule that rejects anything else, and now you need it in a second file. You copy the cells, paste them in the other spreadsheet, and the dropdown shows no options at all, or the wrong ones. "Data validation not copying between files" is a common complaint, and the cause is almost always the same: the rule travelled, but the list it depends on did not. This guide explains what breaks, how to copy each kind of rule so it survives, and how to move a whole set of rules at once.
What actually happens when you copy a rule
A data validation rule belongs to a range of cells, and copying those cells carries the rule along. Inside one file, Edit > Paste special > Data validation only pastes just the rule, without values or formatting; between two files a plain copy and paste of the cells usually carries the rule too (Google has changed this over the years, so check the destination). Whether the result works depends on the criteria type:
- Dropdown with a fixed list ("Open, Won, Lost" typed into the rule): the items live inside the rule, so the copy is complete and travels well.
- Dropdown from a range (the items come from
Lists!A2:A20): the rule stores a reference to a range, not the items. The other spreadsheet has noListstab, or a different one, so the rule points at a range that does not exist there and the dropdown is empty or wrong. This is the one that breaks. - Text, number, date and checkbox rules: self-contained, they copy cleanly.
- Custom formula: a formula that only looks at its own row, such as
=ISNUMBER(G2), travels fine and shifts with the destination like any pasted formula. One that references another tab breaks for the same reason as the dropdown from a range.
Two more things to know. Pasting a rule onto cells that already hold values does not reject them; they stay and get a red triangle if they do not fit. And the paste replaces whatever rule the destination cells had.
Method 1: Copy and paste special (self-contained rules)
For fixed-list dropdowns, number, date, text and checkbox rules, and row-local custom formulas, the manual route is short:
- In the source file, select one validated cell and copy it; the rule is the same across its range.
- Open the target spreadsheet and select the whole destination range. A single copied cell fills the selection.
- Choose Edit > Paste special > Data validation only. Only the rule arrives; existing values and formatting in the destination stay as they are.
- Click a destination cell and open Data > Data validation to confirm the criteria and the "show a warning" or "reject the input" setting.
This is Google's own copy of the rule, so inside a file dropdown chip colors and the multiple-selection option travel with it. If you then need the rule on several tabs of the new file, updating a dropdown list used on many tabs covers the ways to spread one rule across tabs.
Method 2: Bring the list along, then re-link (dropdown from a range)
A dropdown from a range only works when the range exists in the same file, so the job has two halves: move the list, then point the rule at it.
- In the source file, right-click the tab that holds the list (say
Lists) and choose Copy to > Existing spreadsheet. Pick the target file. The tab arrives with its contents and keeps its name unless the target already has one by that name; then it is called "Copy of Lists" and you should rename it. - Copy the validated cells into the target as in Method 1.
- In the target file, select the pasted cells, open Data > Data validation, click the rule and check the range in the criteria. If it does not read
Lists!A2:A20(or your list range), re-enter it and press Done.
If the target file should not get a lists tab, convert the rule instead: open it in Data > Data validation, switch the criteria from Dropdown (from a range) to Dropdown, and type the items. The rule is now self-contained; future list changes have to be made in the rule itself.
Method 3: Rebuild the rule from scratch
When a copy keeps arriving broken, rebuilding is often faster than debugging. Select the destination range, open Data > Data validation, press Add rule and re-create the criteria. Write down the source rule first: criteria type, the items or range, warning or reject, the help text, and for dropdowns the chip colors and whether multiple selections are allowed. Those last two live in the rule's options and are easy to forget.
Rebuilding guarantees a working rule, but it does not scale: twenty validated columns means twenty rules to type, each a chance for a typo.
Check the copied rule before you rely on it
Whichever method you used, test it the way a user would. Click a destination cell: the dropdown should open with the expected items. Type a value that should be rejected and confirm the warning or rejection appears. Then look at what the destination range already contained. A pasted rule does not retroactively reject existing values, so a column that held free text can now be full of red triangles. Finding all invalid entries at once shows how to list them instead of scrolling for triangles.
Or export the rules as JSON and import them in the other file
For more than a handful of rules, Data Validation Manager, a free Google Sheets add-on, moves rules as text. In the source file, open Extensions > Data Validation Manager > Launch Data Validation Manager. The sidebar lists every rule on every tab as a card with a plain-English line ("Dropdown from Lists!A2:A20", "Custom formula: =ISNUMBER(G2)"). Use Export on one rule, one tab or all rules; the result is JSON you copy. A dropdown from a range carries a snapshot of its list values in the export, which makes the cross-file case workable.
In the target file, open the sidebar, choose Import, paste the JSON and press Preview. The preview maps each exported sheet to a tab in this file (same-named tabs are pre-selected; "Don't import" is a choice for each). For every tab that dropdown-from-a-range rules refer to, you choose Re-link to a tab in this file that holds the list, Convert to fixed lists from the values saved at export, or skip. Then press Import N rules. Imported rules replace any rule already on their cells; imports are capped at 500 rules per file. Dropdowns imported from JSON arrive in the default style, because chip colors can't travel in a file, so re-apply colors in Data > Data validation if you need them. If the result is wrong, Ctrl/Cmd+Z in the sheet undoes the import and Refresh in the sidebar shows the restored state.
Frequently asked questions
Why is my dropdown empty after copying it to another spreadsheet?
Because it is a dropdown from a range, and that range does not exist in the new file. The rule copied; the list did not. Either copy the list tab into the new file and re-enter the range in the rule, or convert the rule to a fixed-list dropdown by typing the items into it.
Do chip colors and multiple selection survive the copy?
Inside a file, Google's own copy of a rule keeps them, so Paste special > Data validation only carries them along. Rules moved as text, such as a JSON export, arrive as plain dropdowns in the default style; set the colors again afterwards. When rebuilding a rule by hand, set both in the rule's options.
Does pasting a rule change the values already in the destination cells?
No. Paste special > Data validation only leaves values and formatting alone; it adds the rule and nothing else. Values that do not fit the new rule stay where they are with a red triangle; they are neither removed nor rejected after the fact.