Data Validation is a built-in feature that allows you to control the type of data documented in your dataset; it is also called a drop-down list. In other words, you can limit whether data can be entered in a particular cell – such as dates, numbers, or text – via a defined list of rules.
For example, when you click on a cell and an arrow appears, it means that cell contains data validation rules.
Here, the rule stipulates that only certain preselected names can be used in this dataset.
Conversely, here the rule stipulates that all data must be less than 20.
If we then try to input 22, a warning box will appear.
This second example differs from the first in that, despite the absence of drop-down icons, rules are still in effect.
How to Find Cells Containing Data Validation
Before we can remove data validation, we need to be able to locate it; and if your dataset is large, you won’t be able to do so one by one.
The following dataset contains some columns with data validation rules, except we can’t tell by looking which ones they are.
Steps
- Click Home → Editing → Find & Select → Data Validation
The entire range of cells containing validation rules is now grayed out.
How to Remove Data Validation Restrictions
Method 1 – Remove Restrictions Using the Data Validation Box
This is the most common method, composed of two options. The first option (outlined in section 1.1) is simply to select the range of cells containing validation rules, then click Clear to remove all rules.
A second option (outlined in section 1.2) is to select the range of cells containing validation rules and then remove the criteria.
1.1 Select the ‘Clear All’ Option
Steps
- Select the range of cells containing validation rules (as previously explained).
- Click Data → Data Validation.
- When the following dialog box appears, click OK.
- The Data Validation dialog box will then appear; click Clear All, then OK.
And with that, the drop-down icon is gone. If there are any custom rules in the validation, they will be gone as well.
1.2 Allow ‘Any Values’ in Data Validation Criteria
Steps
- Select the range of cells containing validation rules (as previously explained).
- Click Data → Data Validation.
- When the following dialog box appears, click OK.
- The Data Validation dialog box will then appear; select “Any value”, then click OK.
And with that, the drop-down icon is gone. If there are any custom rules in the validation, they will be gone as well.
Method 2 – Use the Paste Special Command
Steps
- Select an empty cell and press Ctrl+C.
- Select the range of cells containing validation rules (as previously explained).
- Press Ctrl+Alt+V to open the Paste Special dialog box.
- Click Validation, then OK.
And with that, the drop-down icon is gone. If there are any custom rules in the validation, they will be gone as well.
Read More: How to Copy Data Validation in Excel
Method 3 – Apply VBA Codes
Steps
- Press Alt+F11 to open the VBA editor.
- Click Insert → Module.
- Enter the following code:
Sub Clear_Data_Validation()
Selection.Validation.Delete
End Sub- Click Save.
- Select the range of cells containing validation rules (as previously explained).
- Press Alt+F8 to open the Macro dialog box.
- Click Clear_Data_Validation, then Run.
And with that, the drop-down icon is gone. If there are any custom rules in the validation, they will be gone as well.
Read More: How to Remove Blanks from Data Validation List in Excel
Things to Remember
✎ If your worksheet is composed of multiple datasets, click Find & Select → Data Validation.
✎ As Data Validation is unavailable for protected sheets, you must unprotect your worksheet by removing passwords from the workbook.
Download Free Practice Workbook
Related Articles
- How to Use Custom VLOOKUP Formula in Excel Data Validation
- How to Use IF Statement in Data Validation Formula in Excel
- Apply Custom Data Validation for Multiple Criteria in Excel
- How to Apply Multiple Data Validation in One Cell in Excel
- Data Validation Based on Another Cell in Excel
<< Go Back to Data Validation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
