How To Remove Data Validation Restrictions In Excel

If you’ve ever tried to enter data into an Excel worksheet and received an error message stating that the entry is not valid, you’ve encountered data validation restrictions. While these restrictions can be useful for ensuring data accuracy and consistency, they can also be used for limiting the data range when you need to make changes or add new data to your worksheet. 

Fortunately, removing data validation restrictions in Excel is a simple process that can be done in just a few steps. In this article, we’ll walk you through the process of removing data validation restrictions so that you can work more efficiently with your Excel worksheets.

Let’s use the below dataset for our example.

Method 1 – Remove Validation Through Clear Option

Step 1 – Find Cells With Data Validation

• To remove data validation restrictions, we’ll first need to find where the data validations are placed in the data sheet.
• To find data validation restrictions, go to the Home Tab, under the Editing Group, and click on the Find & Select button.
• Select Data Validation.
• All the cells, rows, or columns with data validation will be highlighted.

Step 2 – Go To Data Tab

• Select the highlighted cells, rows, or columns from where you want to remove the validation.
• Go to the Data Tab, under the Data Tools group, and click on the Data Validation Button.

Step 3 – Data Validation Dialog Box

• Data validation dialog box will appear on your screen with Data validation details.
• Click on the checkbox.
• Click Clear All and then the OK button.

Step 4 – Data Validation Cleared

• All the validations will be removed by following the above steps.

Method 2 – Remove Validation Through Paste Special Option

Step 1 – Find Cells With Data Validation

• To remove data validation restrictions, we’ll first need to find where the data validations are placed in the data sheet.
• To find data validation restrictions, go to the Home Tab, under the Editing Group, and click on the Find & Select button.
• Select Data Validation.
• All the cells, rows, or columns with data validation will be highlighted.

Step 2 – Copy Blank Cell

• Copy any blank cell without any formatting or validation.
• Now, select the cells, rows, or columns that have validation and right click on them.
• From the context menu, select Paste Special.

Step 3 – Paste Special Dialog Box

• In the Paste Special dialog box, select Validation and click the OK button.

Step 4 – Data Validation Removed

• All the data validations will be removed.