How to Remove Data Validation Restrictions in Excel (3 Ways)

In Microsoft Excel, data validation is one of those tasks that makes it easy to input data. If you want to limit your input data type, you can use this method. But, sometimes, you may need to remove it for various purposes. In this tutorial, you will learn how to remove data validation restrictions in Excel with suitable examples and proper illustrations.

What is Data Validation in Excel?

In Microsoft Excel, Data Validation is a built-in feature that allows you to control the data type documented in your dataset. You can call it a dropdown list also. A user can limit the data entries based on a list or some rules you defined. It may be dates, numbers, texts, etc.

Have a look at the following screenshot:

Here you can see when we click the cell, a drop-down icon beside it. It means this cell contains Excel data validation rules.

Let’s see what kinds of data it can take:

Let’s see another example:

Here, we used custom validation rules implying that data must be under 20. Now, if we try to input 22 in the cell, it will show the following warning box:

This time there are no drop-down icons but the cells contain rules. I hope from this section you got a basic idea about data validation in Excel.

Find Cells with Data Validation in Excel

Before we start to remove data validation from Cells, we need to find the cells that contain data validation. It is a crucial task. Because if your dataset is large, you won’t be able to find one by one. So, if a sheet contains rules for data validation, you need to identify it first.

Have a look at the following dataset:

We have sales data here. Some columns have data validation rules. But, we can’t see them without clicking. So, we will find them first.

📌 Steps

• First, go to the Editing group in the Home tab.
• After that, click on Find & Select.

• Now, click on Data Validation.

After clicking the data validation option, it will select the entire columns or ranges of cells that contain the validation rules.

In the following section, we are providing you with three suitable and effective methods that you can implement into your worksheet to remove data validation. Make sure you learn them all. We recommend you learn and apply all these. It will surely enrich your Excel knowledge.

1. Removing Data Validation Restrictions in Excel (Regular Ways) 

Now, by regular ways, we mean the Data Validation dialog box. It is the most used method to clear Data Validation in Excel. From, here you can follow two ways to remove:

The choice is up to you. Here, we are going for the second option.

1.1 Selecting ‘Clear All’ Option

Follow these simple steps to clear data validation from your dataset:

📌 Steps

• Select the range of cells that contain Data Validation (Read the previous section to identify first).

• After that, go to the Data tab.
• Now, from the Data Tools group, click on the Data Validation.

• After that, a dialog box will show up because it contains data validation.

• Then, click on OK.

• Now, from the Data Validation dialog box, click on Clear All. Next, click on OK.

As you can see, there is no drop-down menu in the dataset. So, we are successful in removing data validation. If there are any custom rules in the validation, it will also clear them.

1.2 Allowing ‘Any Values’ in Data Validation Criteria

This method is similar to the previous one. Just a simple change you can make here to clear data validation.

📌 Steps

• First, select the range of cells that contain Data Validation (Read the previous section to identify first).

• Now, go to the Data tab.
• Then, from the Data Tools group, click on the Data Validation.

• Next, a dialog box will show up because it holds Data Validation.

• Now, click on OK.

• Now, from the Data Validation dialog box, select ‘Any value’ from the Allow drop-down list. After that, click on OK.

In the end, there will be no Data Validation rules in the dataset. This method will work fine as the other one. So, choose according to your will.

2. Using Excel Paste Special Command to Remove Data Validation Restrictions

Another effective way to remove data validation is using the Paste Special command of Microsoft Excel. People don’t use this method too often. But we recommend you learn this method. We suggest you keep it in your arsenal to use it in the future.

📌 Steps

• First, copy any empty cell from the worksheet.

• After that, select the range of cells that contain Data Validation.

• Now, press Ctrl+Alt+V on your keyboard. It will open the Paste Special dialog box.

• Now, select the Validation radio button and click on OK.

Finally, it will remove all the data validation rules from the dataset.

Read More: How to Copy Data Validation in Excel

3. Applying VBA Codes to Remove Data Validation Restrictions in Excel

If you are a VBA freak like me, you can try this method. This code will remove Data Validation from the dataset in Excel with ease. With this simple code, you will be able to perform this operation for an entire column or range of cells efficiently.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert > Module.

• After that, type the following code:

Sub Clear_Data_Validation()
Selection.Validation.Delete
End Sub

• Then, save the file.
• Now, select the range of cells that contain Data Validation rules.

• After that, press Alt+F8 on your keyboard to open the Macro dialog box.

• Next, select Clear_Data_Validation.
• Then, click on the Run.

As you can see, our VBA codes successfully cleared the data validation from the worksheet.

Read More: How to Remove Blanks from Data Validation List in Excel

💬 Things to Remember

✎ If your worksheet contains multiple datasets, use the Find & Select method to select them all. After that, you can remove them easily.

✎ Data Validation will be unavailable for protected sheets. So, unprotect your sheet by removing passwords from the workbook.

Download Practice Workbook

Download this practice workbook.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to remove data validation in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Keep learning new methods and keep growing!

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