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.


Download Practice Workbook

Download this practice workbook.


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:

remove Data Validation in Excel

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

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

Let’s see another example:

remove Data Validation in Excel

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:

remove Data Validation in Excel

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

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.

remove Data Validation in Excel

  • Now, click on Data Validation.

remove Data Validation in Excel

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


3 Effective Ways to Remove Data Validation Restrictions in Excel

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. Regular Ways to Remove Data Validation Restrictions

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:

  • Select a particular range of cells or a column, then clear.
  • Select all the cells or columns, then remove data validation.

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.

Regular Ways to Remove Data Validation

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

  • Then, click on OK.

Regular Ways to Remove Data Validation

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

Regular Ways to Remove Data Validation

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


1.2 Allowing ‘Any Values’ in Validation Criteria

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

📌 Steps

  • At 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.

Regular Ways to Remove Data Validation

  • Next, a dialog box will show up because it holds data validation.

  • Now, click on OK.

Regular Ways to Remove Data Validation

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

Regular Ways to Remove Data Validation

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 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.

Paste Special Command to Remove Data Validation

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

Regular Ways to Remove Data Validation

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


3. 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.

VBA Codes to Remove Data Validation in Excel

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

Regular Ways to Remove Data Validation

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


💬 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.


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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo