A.N.M. Mohaimen Shanto
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:
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:
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
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.
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.
- 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 the Clear All Next, click on OK.
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.
- 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.
Read More: How to Clear Formula in Excel (7+ Methods)
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.
- Now, select the Validation radio button and click on OK.
Finally, it will remove all the data validation rules from the dataset.
Read More: Data clean-up techniques in Excel: Replacing or removing text in cells
Similar Readings
- How to Remove Carriage Returns in Excel: 3 Easy Ways
- Remove the Page Break Lines in Excel (3 Ways)
- How to Remove Encryption from Excel (2 Methods)
- Remove Dashes from SSN in Excel (4 Quick Methods)
- How to Remove Negative Sign in Excel (7 Methods)
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.
- 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: Data clean-up techniques in Excel: Fixing trailing minus signs
💬 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!
Related Articles
- How to Remove Leading Zeros in Excel (7 Easy Ways + VBA)
- Remove Strikethrough in Excel (3 Ways)
- How to Remove Macros from Excel (5 Suitable Ways)
- Remove Print Lines in Excel (4 Easy Ways)
- How to Remove Carriage Returns in Excel: 3 Easy Ways
- How to Remove Numbers from a Cell in Excel (7 Effective Ways)
