Two Quick Methods for Removing Data Validation Rules in Excel
Data validation rules are great tools to prevent users from entering incorrect data into your spreadsheet, but they also have their drawbacks. For instance, you might want to remove the data validation rule on a certain cell so that you can enter any data you want without having to worry about it being correct. The good news is that there are two ways you can easily remove the data validation rule in Excel, so let’s get started!
Editing the Data Validation Source
The quickest way to remove the data validation rules is to right-click on any cell with a validation rule, select Data Validation from the menu, and then click Clear All. If you do not see this option, right-click again and select Show All. Then make sure the options are set to use an input message or validation rule. Once selected, click on the validation box with a red X beside it. Once clicked, you should be able to edit and delete them from there.
The second method of removing data validation rules entails finding the cell where you have set up your initial data entry criteria for that particular spreadsheet. Right-click on the cell and select Data Validation > New Rule…> Edit…> Edit Rule Settings. Next, uncheck all boxes except Error Alerts and Blank Cells. For Error Alerts: choose one of three options (e.g., Stop or Scroll Off) > OK > OK > Done. You may now enter blank cells without having the error alert pop up each time you do so!
Using Remove Name
Validation rules can be a useful tool when they are used correctly. However, if you find that the validation rules are not working as you intended, there are two quick methods to remove them from your workbook. Method 1: Remove one by one. Open up the name of the validation rule and delete it from the list box on the right side of the screen. When you have deleted all of them, save your file and close it. You should now see all cells with no rules or prompts. Method 2: Delete every cell with a data validation rule. To do this, first select all cells with data validation rules by clicking on column A at the top of the screen then holding down shift and pressing column D at the bottom left corner of the screen (see image below). Once selected, go to the menu bar and click on Edit -> Delete -> Columns -> Selected Columns -> Current Selection -> OK. All cells with data validation rules will now be gone!
Using VBA to Remove the Rule
A quick way to remove a data validation rule in Excel is to use the VBA editor. To do this, go to Tools>Macro>Visual Basic Editor. Then go to Module1 and put the following code:
MsgBox This is the description of your rule & vbNewLine & &Now it’s gone!
Hit Ctrl+S on your keyboard and then close the Visual Basic Editor window. Now when you try to apply validation rules in Excel, they won’t be there. If you want to add them back, simply reopen the VBA window and paste them into it again. Using Kutools for Excel: The other option is using Kutools for Excel which has an Add-In that removes all your validation rules. There are two ways to do this as well. Either click on Sheet Operations > Delete All Validations or click on Data > Remove all Validations > Select All Rules
