How to Remove Data Validation in Excel

The data validation feature in Excel enables you to check for errors in your sheet.  Enabling data validation helps you ensure that the data inputted into the sheet is accurate and conforms to already set standards.

For instance, you can use data validation to restrict the type of data to be entered into a cell.

How to apply data validation in Excel

Here’s a rundown of how to apply data validation to your worksheet in Excel:

Step #1: Select the cells you want to apply data validation from.

Step #2: Navigate to the Data Tab

Step #3: Click on Data Validation.

Step #4: Then go to the Settings tab.

Step #5: Under  “Allow”, select any of these options:

• Whole Number: This will restrict the cell to accept only whole numbers.
• Decimal: This will restrict the cell to accept only decimal numbers.
• List: This will restrict the cells to pick data from the drop-down list.
• Date:  This will restrict the cell to accept only dates.
• Time: This will restrict the cell to accept only time.
• Custom: This will restrict the cell to only a custom formula.

Step #6: Once you have selected any of these, Under Data, select a condition.

Step #7: Then set other required values based on what you chose for Allow and Data.

Step #8: Customize a message users will see when entering data in the “Input Message tab”

Step #9: Check the “Select the Show input’’ checkbox to display the message when you hover over the selected cell(s).

Step #10: Next select the Error Alert tab to customize the error message and choose a Style.

Step #11: Click “OK” when you are done.

With the steps above, when a value that is not valid is entered into the cells, an error alert appears with your customized message restricting that data from being entered.

How to Remove Data Validation in Excel

If you do not want the Data Validation feature, you can remove the validation from the entire sheet. Here’s how to remove data validation in Excel:

Step #1: Select the Cell you want to remove data validation from.

Step #2: Take your mouse to the “Data” tab on the Excel ribbon.

Step #3: Click on the “Data Validation” option.

Step #4: A dialog box will appear, from the dialog box, go to “Settings”.

Step #5: Locate the “Allow” drop-down menu, select “Any value”. 

Step #6: Any restrictions on the type of data allowed in the selected cell will be removed.

Step #7: Then Click “OK” to apply the changes.

The data validation rules you added will now be removed from the cells, so you are free to enter any type of data into the Excel sheet.

Common Issues When Removal Data Validation in Excel

#1. Looked Cells Error

Some cells can be locked to prevent users from entering data or formulas into those cells. When a cell is locked, you cannot remove the data validation rules from it. The hack is to unlock the cells before removing the data validation rules. If the entire worksheet is protected, do the following first:

• Go to the Review Tab.
• Click on “Unprotect Sheet” in the Changes group.
• Click the Protect Sheet button to Unprotect the worksheet.

Now the sheet is unprotected, you can then unlock the locked cells by:

• Select the cells you want to unlock or click the Select All button to select the entire worksheet.
• Go to  the Home tab to locate Format Cells or right click and select “Format Cells”.
• In the Format Cells popup, go to the Protection tab.
• Then uncheck the Locked box and click OK.

All the locked cells will be unlocked, then you can proceed to remove the data validation rules in the cells.

#2. Hidden Rows and Columns Error

Hidden rows and column errors can come up while you are trying to remove data validation rules in your Excel worksheet. The best thing is to unhide the Rows/Columns before removing data validation rules. Here’s how to unhide rows and columns in Excel:

• Highlight the rows and columns surrounding the hidden rows or hidden columns.
• Right-click and click on “Unhide”.

Now that you are done, you can try removing the data validation rules again and see if it works.

Before you go…

That was a smooth ride on how to remove data validation in Microsoft Excel, check out our recommended article on Microsoft Excel below:

How to Switch Rows and Columns in Excel

How to Subtract in Excel

How to Remove Strikethrough in Excel

FAQs