Copy, Find and Remove Data Validation in Excel: Go To Special Guide

Part of the free Module 6: Data Validation · Lesson 14 of 14 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.

To remove data validation in Excel, select the cells, open Data > Data Validation and click Clear All. To copy a rule, copy the cell and use Paste Special > Validation. To find every validated cell, press F5 > Special > Data validation. This lesson covers all three jobs, the paste behaviour that silently deletes rules, and a two-line macro for bulk clean-up.

Why copying, finding and removing rules matters

Rules are easy to create and easy to lose track of. A tracker that started with ten rows now has three hundred, and only the first ten have drop-downs. A colleague pastes a block from another file and the rules on those cells disappear without a message. Or a template inherited from someone else refuses an entry and nobody knows where the rule lives. Excel gives you one tool for each problem: Paste Special copies a rule anywhere, Go To Special reveals every rule on a sheet, and the Data Validation dialog clears rules from one cell or the whole sheet in a single click.

Task Command Keyboard
Copy a rule to other cells Copy, then Home > Paste > Paste Special > Validation Ctrl + C, then Ctrl + Alt + V, N, Enter
Extend a rule down a column Drag the fill handle, or Fill Down Ctrl + D
Find every validated cell Home > Find & Select > Data Validation F5, Alt + S, V, Enter
Find cells with the same rule as the active cell Go To Special > Data validation > Same F5, Alt + S, V, E, Enter
Remove rules from a selection Data > Data Validation > Clear All Alt, A, V, V, then Alt + C
Remove every rule on the sheet Select all, then Clear All Ctrl + A, Ctrl + A, then Alt, A, V, V

Copy data validation to other cells with Paste Special

A normal paste copies everything in a cell: value, formats, comments and the validation rule. That is fine when the target cells are empty, but it also overwrites data you want to keep. Paste Special lets you copy only the rule.

  1. Select a cell that already has the rule you want and press Ctrl + C.
  2. Select the target cells. They can be on another sheet or in another open workbook.
  3. Press Ctrl + Alt + V to open the Paste Special dialog, or go to Home > Paste > Paste Special.
  4. Choose Validation and click OK. Existing values and formats in the target cells are untouched; only the rule is added.
  5. Press Esc to clear the marching ants.

Relative references inside the rule shift exactly as they do in a formula. A custom rule written as =COUNTIF($A$2:$A$500,A2)=1 on A2 becomes =COUNTIF($A$2:$A$500,A50)=1 when pasted to A50, which is what you want. A List rule with the source =Lists!$A$2:$A$10 keeps its absolute source. The Format Painter does not copy validation, only cell formatting, so use Paste Special instead.

To extend a rule to new rows in a column, select the validated cell together with the rows below it and press Ctrl + D, or drag the fill handle. Both copy the rule along with the content, so use them on empty rows or use Paste Special where the rows already hold data. If the column is part of an Excel Table, a rule applied to the whole column extends to new rows automatically.

Find cells that have data validation

Validation leaves no visible mark on a cell, so finding rules needs the Go To Special dialog.

  1. Select one cell on the sheet, or select a range to limit the search.
  2. Press F5 or Ctrl + G and click Special. You can also go to Home > Editing > Find & Select > Go To Special.
  3. Choose Data validation. Leave All selected to find every validated cell, or choose Same to find only the cells whose rule matches the active cell.
  4. Click OK. Excel selects every matching cell at once. Give them a fill colour if you want a permanent map of where the rules are, and remove the colour later.

Home > Find & Select > Data Validation is a shortcut for the All option. The Same option is the one to use before editing a rule: select one cell with the rule, run Go To Special with Same, and the whole group is selected so a change in the Data Validation dialog applies to all of them. If nothing is selected and Excel says no cells were found, the sheet has no validation at all.

Edit one rule for every cell that shares it

You do not need to reselect the group by hand. Select any one cell with the rule, open Data > Data Validation, change the settings and tick Apply these changes to all other cells with the same settings at the bottom of the Settings tab. Excel updates every cell on the sheet that had the identical rule, including cells you had forgotten about. The tick box appears only when other cells share the rule, so its absence tells you the rule is unique to the selection.

Remove data validation from cells, a sheet or a workbook

Clearing rules is the same dialog in reverse.

  1. Select the cells to clear. For the whole sheet press Ctrl + A twice, or click the triangle above row 1.
  2. Open Data > Data Tools > Data Validation.
  3. If the selection holds different rules, Excel asks whether to erase the current settings and continue. Click OK.
  4. Click Clear All, then OK.

The values stay; only the rules, input messages and error alerts go. Pressing Delete on a cell does the opposite: it removes the value and keeps the rule. Home > Clear > Clear All removes both. There is no single command for a whole workbook, so repeat the steps on each sheet or run the macro below.

Sub RemoveValidationFromSelection()
    Selection.Validation.Delete
End Sub

Sub RemoveValidationFromWorkbook()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Cells.Validation.Delete
    Next ws
End Sub

Press Alt + F11, insert a module, paste the code and run it with Alt + F8. Save the file first: validation removed by a macro cannot be undone with Ctrl + Z.

Stop paste from wiping your rules

Pasting a cell that has no validation over a cell that has some replaces the rule with nothing, because a normal paste includes the validation setting, even an empty one. This is the most common reason a drop-down disappears from a form. Sheet protection does not prevent it on unlocked cells. Three habits keep rules in place.

  • Paste values only. Use Ctrl + Alt + V then V, or Ctrl + Shift + V in current Excel 365 builds. Values paste; the rule survives.
  • Audit after every import. Run Go To Special > Data validation and compare the selection with the area that should be covered, then re-copy the rule where gaps appear.
  • Check existing data. Pasted values are never tested against a rule. Use Data Validation > Circle Invalid Data to mark entries that break it.

Worked example: growing and cleaning a leave tracker

A leave tracker has Employee in column A (drop-down from a named list), Leave Type in column B (drop-down: Annual, Sick, Unpaid) and Days in column C (whole number 0.5 to 30). Rules cover rows 2 to 50, but the sheet now has 250 rows, and the summary block in E1:G5 was copied from a validated row and carries rules it should not have.

Step Action Result
1 Select A2:C2, Ctrl + C, select A51:C250, Ctrl + Alt + V, Validation, OK All three rules now cover rows 2 to 250; existing entries in rows 51 to 250 are unchanged
2 F5 > Special > Data validation > All A2:C250 and E1:G5 are selected, confirming the stray rules in the summary block
3 Select E1:G5, Data Validation, OK on the mixed-rules prompt, Clear All Summary block is rule-free; its values and formats remain
4 Select C2, Data Validation, change Maximum to 25, tick Apply these changes to all other cells with the same settings Every Days cell now allows 0.5 to 25
5 Data Validation > Circle Invalid Data Two existing entries of 28 and 30 days are circled for review

Tips and common mistakes

  • Paste Special > Validation is the only safe copy. Every other paste, fill or drag also copies content and formats.
  • Select from the top cell before pasting a custom rule. The relative references shift from the first target cell, so paste to B2:B200, not to B200:B2.
  • Same means identical. Two List rules with the same items but different Ignore blank settings are not the same to Go To Special.
  • Cross-workbook copies can break list sources. A list that points at a range in the original file becomes invalid in the new file; repoint it or use a typed list.
  • Clear All does not clear values. If a column must be emptied and de-ruled, select it and use Home > Clear > Clear All.
  • Grouped sheets disable the dialog. If Data Validation is grey, check that only one sheet tab is selected and the sheet is not protected.
  • Save before running the macro. Undo does not reverse changes made by VBA.

Errors and how to fix them

Symptom Cause Fix
Data Validation button is greyed out Sheet is protected, several sheets are grouped, a cell is in edit mode, or the workbook uses legacy sharing Unprotect the sheet, right-click a tab and choose Ungroup Sheets, press Esc, or turn off Share Workbook
Drop-down vanished after pasting A normal paste replaced the rule with the source cell’s empty rule Undo, then paste values only; re-copy the rule with Paste Special if it is already lost
Go To Special finds cells you did not expect Rules were copied along with content when rows or blocks were duplicated Select those cells and Clear All
Prompt about more than one type of validation The selection contains different rules Click OK to continue to the dialog, then Clear All to remove all of them
Tick box for other cells with the same settings is missing No other cell has an identical rule Select the full range yourself and re-enter the rule
Pasted rule refers to the wrong cells Relative references shifted because the target started at a different row Edit the rule on the first target cell and use the tick box to push it to the rest

Practice exercise

  1. Create a drop-down list in B2 with the items Yes and No. Type values in B3:B20, then copy the rule to B3:B20 with Paste Special so the values stay.
  2. Copy a cell with no validation and paste it normally over B5. Confirm the drop-down is gone, undo, and paste values only instead.
  3. Run Go To Special > Data validation > All and count the selected cells. Colour them yellow.
  4. Change the list in B2 to Yes, No and Maybe and use the tick box to update B3:B20 in one step.
  5. Remove every rule from the sheet with Ctrl + A and Clear All, then confirm with Go To Special that none remain.

Key takeaways

  • Paste Special > Validation copies a rule without touching values or formats; Ctrl + D and the fill handle copy the rule with the content.
  • Go To Special > Data validation selects every validated cell (All) or every cell sharing the active rule (Same).
  • Data Validation > Clear All removes rules from any selection, including the whole sheet after Ctrl + A.
  • Tick Apply these changes to all other cells with the same settings to edit one rule everywhere at once.
  • A normal paste from an unvalidated cell deletes the rule; paste values only and audit after imports.

Related lessons

Frequently asked questions

How do I remove data validation from a whole sheet in Excel?

Press Ctrl + A twice to select every cell, open Data > Data Validation, click OK if Excel warns that the selection holds more than one type of validation, then click Clear All and OK. Values, formulas and formatting stay; only the rules, input messages and error alerts are removed. Repeat on each sheet, or use a short macro for the whole workbook.

How do I copy data validation to another sheet or workbook?

Copy a cell that has the rule, switch to the destination, select the target cells and press Ctrl + Alt + V, then choose Validation and click OK. Only the rule is pasted. If the rule is a List that points at a range in the source workbook, edit the source after pasting, because the reference will not follow into a different file.

How can I see which cells have data validation?

Press F5, click Special, choose Data validation with All selected and click OK. Excel selects every cell on the sheet that has a rule. Apply a temporary fill colour to keep the map visible. Home > Find & Select > Data Validation does the same in one click. Choose Same instead of All to find only cells that share the active cell’s rule.

Why did my drop-down list disappear after pasting?

A normal paste carries the validation setting of the copied cell, and a cell with no rule carries an empty setting that replaces yours. Undo, then paste with Ctrl + Alt + V and Values, or Ctrl + Shift + V in current Excel 365. To restore a lost rule, copy it from a cell that still has it and use Paste Special > Validation.

Does deleting the contents of a cell remove its validation?

No. Pressing Delete or Backspace clears the value and leaves the rule in place, which is why an emptied form still shows drop-down arrows. To remove both, use Home > Clear > Clear All, or use Data Validation > Clear All to remove the rule and keep the value.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems with validation already in place are available at NextGenTemplates.com.

← PreviousSearchable Drop-Down List in Excel: Autocomplete and FILTER MethodAll lessonsData Validation