Shamima Sultana
To make all data entries accurate and consistent you may use Data Validation. To allow only particular data in a specific 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. So, let’s get started.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly
5 Ways to Remove Data Validation in Excel
To make this explanation visible, I will 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 columns to show how to remove it.
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 only to accept text values. In the Product name column of the dataset, I applied the list in settings. The listed values are All laptops, Computers, Graphics Cards, 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 B5:B18 because here custom Data Validation is applied to only accept text values.
If you try to type any numeric value, it will show an error message.
To remove Data Validation from any cell or cell range follow the below steps.
Steps:
- First, select any cell or cell range where Data Validation is applied.
Here, I selected cell B9.
- Then, open the Data tab >> from Data Tools >> select Data Validation.
- Sequentially, a dialog box will pop up. From there select Clear All.
- Finally, click OK.
- Now, the Data Validation from the selected cell is removed. Here, you can type any numeric value.
2. Applying Paste Special Command
To erase Data Validation, you can use Paste Special.
- To use this method, first, select any cell and press the CTRL + C.
- Then, select the cell or cell range to remove Data Validation.
Here, I selected the cell range B5:B15.
- Next, press ALT + CTRL+ V, then press N.
- Apparently, a dialog box of Paste Special will pop up where Validation will be selected (because of pressing N) then click OK.
- Now, the Data Validation will be removed from the selected cell range, and you can type any numeric value there.
3. Employing Go To Special Feature
By using Go To Special you can remove the same settings or all Data Validation applied in a sheet.
Let’s, start the procedure.
Steps:
- To begin with, open the Home tab >> from Find & Select >> select Go To Special.
- 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.
- consequently, a dialog box will pop up. From Settings select Clear All. Finally, click OK.
- Hence, Data Validation will be removed from every cell.
Similar Readings:
- How to Remove Formulas in Excel: 7 Easy Ways
- Data clean-up techniques in Excel: Replacing or removing text in cells
- How to Remove Numbers from a Cell in Excel (7 Effective Ways)
4. Incorporating VBA Macros to Remove Data Validation
You also can use VBA Macros to remove Data Validation. Follow the steps.
Steps:
- First things first, open the Developer tab >> then select Visual Basic
- Momentarily, it will open a new window of Microsoft Visual Basic for Applications.
- Now, from Insert >> select Module >> Module 1.
- After that, A Module will open.
- Then, write the following code in the Module.
Sub Remove_Data_Validation()
Selection.Validation.Delete
End Sub
Code Breakdown:
- 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 D5:D15.
- Now, open the View tab >> from Macros >> select View Macros
- After that, a dialog box will pop up.
- 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. Utilizing 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. Follow the below steps.
Steps:
- Firstly, open the Home tab >> from Find & Select >> select 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.
- 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 the price.
Practice Section
Moreover, we have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Conclusion
That’s all about today’s session. And these are some easy methods to remove data validation in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Visit our website ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of excel methods. Thanks for your patience in reading this article.
