How to Remove Data Validation in Excel (5 Ways)

To make all data entries accurate and consistent you may use data validation. To allow only particular data in a particular column you also can use data validation. As you can use data validation there may come the need of removing data validation. In this article, I’m going to explain how to remove data validation in Excel.

To make this explanation visible, I’m going to use a sample dataset. There are 4 columns in the dataset representing sales information. These columns are Sales Person, Region, Product Name, and Price. Here I applied data validation in Sales Person, Product Name, and Price column to show how to remove it.

Simple Dataset

Download to Practice

Ways to Remove Data Validation in Excel

Before starting the removing methods let’s get to know what types of Data Validation, I used for this example dataset.

In the Sales Person column of the dataset, I applied a custom formula to only accept text values.

In the Product name column of the dataset, I applied the list in settings.
The listed values are All laptop, Computer, Graphics Card, SSD, and Projector.

In the Price column of the dataset, I applied a custom formula to only accept values greater than 0.

Now, let’s begging with the methods to remove Data Validation.

1. Using Ribbon to Remove Data Validation

Now, you can’t type any numeric value in the cell range of B4:B17 because here custom Data Validation is applied to only accept text values.

Using Ribbon to Remove Data Validation

If you try to type any numeric value, it will show an error message.

To remove Data Validation from any cell or cell range,
First, select any cell or cell range where Data Validation is applied.
Here, I selected cell B8.
Then, open the Data tab >> from Data Tools >> select Data Validation

A dialog box will pop up. From there select Clear All.

Using Ribbon to Remove Data Validation

Finally, click OK.

Now, the Data Validation from the selected cell is removed. Here, you can type any numeric value.

Using Ribbon to Remove Data Validation

To pull out the Data Validation from the cells where the same settings are applied,
Mark on Apply these changes to all other cells with the same settings. Then, click OK.

Now, Data Validation will be removed from the cells where there are the same settings. Thus, you can write any numeric value in those cells.

Using Ribbon to Remove Data Validation

2. Using Paste Special 

To erase Data Validation, you can use Paste Special.
To use this method, first select any cell and press the CTRL + C.

Using Paste Special to Remove Data Validation

Then, select the cell or cell range to remove Data Validation.
Here, I selected the cell range B4:B14
Next, press ALT + CTRL+ V, then press N.

A dialog box of Paste Special will pop up where Validation will be selected (because of pressing N) then click OK.

Using Paste Special to Remove Data Validation

Now, the Data Validation will be removed from the selected cell range, and you can type any numeric value there.

3. Using Go To Special 

By using Go To Special you can remove the same settings or all Data Validation applied in a sheet.
Let’s, start the procedure.

To begin with, open the Home tab >> from Find & Select >> select Go To Special

Using Go To Special to Remove Data Validation

A dialog box of Go To Special will pop up. Select Data Validation then select All.

All the cells where Data Validation applies are selected.
Now, open the Data tab >> from Data Tools >> select Data Validation

Then, a warning message will pop up to ensure that you want to remove more than one type of validation or not. If you agree then click OK otherwise click Cancel.

Using Go To Special to Remove Data Validation

A dialog box will pop up. From the Settings select Clear All. Finally, click OK.

Hence, Data Validation will be removed from every cell.

Using Go To Special to Remove Data Validation


Similar Readings:


4. Using VBA to Remove Data Validation

You also can use VBA to remove Data Validation.

First things first, open the Developer tab >> then select Visual Basic

Using VBA to Remove Data Validation

It will open a new window of Microsoft Visual Basic for Applications.
Now, from Insert >> select Module

A Module will open.
Then, write the following code in the Module.

Sub Remove_Data_Validation()
Selection.Validation.Delete
End Sub

Sub Remove_Data_Validation() Selection.Validation.Delete End Sub

Here, I’ve created a sub-procedure called Remove_Data_Validation
Then used the Delete command to delete the selected validation.

After writing the code, Save the code and go back to the worksheet.
Then, select any cell or cell range to remove Data Validation.
Here, I selected the cell range D4:D14.
Now, open the View tab >> from Macros >> select View Macros

A dialog box will pop up.

Sub Remove_Data_Validation() Selection.Validation.Delete End Sub

Now, from the Macro name select the Remove_Data_Validation also select the workbook within Macros in.
Finally, Run the selected Macro.

Therefore, it will remove the Data Validation from the selected range. Here you won’t see any drop-down list in the Product Name.

5. Using VBA with Find & Select 

You can use the Data Validation option from Find & Select to remove the same settings or all the Data Validation already applied in a sheet.

First, open the Home tab >> from Find & Select >> select Data Validation

Using VBA with Find & Select to Remove Data Validation

Now, it will select all cells with Data Validation.
Then, open the View tab >> from Macros >> select View Macros

A dialog box of Macro will pop up.

Using VBA with Find & Select to Remove Data Validation

Now, from the Macro name select the Remove_Data_Validation also select the workbook within Macros in. Then, Run the selected Macro.

Therefore, it will remove the Data Validation from all the selected ranges.
Here you won’t see any drop-down list in the Product Name as well as you also can write numeric values in Sales Person. In the Price column, you can insert 0 as price.

Conclusion

In this article, I’ve explained 5 ways of how to remove data validation in Excel. You can follow any of the explained ways to remove data validation from any cell or cell range. In case you have any confusion or question regarding these methods you may comment down below.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo