Validation rules in Excel enable users to control the data which users enter into Excel worksheet. Usually, if you put these rules in, most of you will not want to remove it. But, if some of you want to remove Excel validation rules, there are 2 solutions for you.
Solution 1 - Remove Excel Data Validation Rules via Mouse Way
1. Click the Data tab.
2. In the Data Tools group, click the Data Validation dropdown and select Data Validation. In Word 2003, go to Data menu and choose Validation.
3. Click the Settings tab. Choose Any Value from the Allow control’s dropdown. Or, simply click Clear All.
4. Click OK.
Solution 2 - Remove Excel Data Validation Rules via Paste Special dialog
1. Select a blank cell and copy it by pressing [Ctrl] + C.
2. Select the range you want to remove its validation.
3. Press [Alt]+E+S+N, which will launch the Paste Special dialog box with the Validation option selected.
4. Click OK.
I confess that the second solution is a bit cumbersome. Anyway, it works well.
If we’re working with multiple ranges as the sample below shows. Ranges DateWorked (A2:A8) and Lunch (D2:D8) both have simple, but different, validation rules applied. We can select them all and remove all of the validation rules at once. Select the ranges and do as the follows.
1. From the Name Box dropdown, choose DateWorked.
2. Press [Ctrl].
3. From the Name Box dropdown, choose Lunch to have a multi-range selection.
4. Click the Data tab.
5. From the Data Validation dropdown, choose Data Validation. In Word 2003, choose Validation from the Data menu.
6. Excel will pop up a warning message. Click OK to delete the validation settings for both ranges.
Solution 1 - Remove Excel Data Validation Rules via Mouse Way
1. Click the Data tab.
2. In the Data Tools group, click the Data Validation dropdown and select Data Validation. In Word 2003, go to Data menu and choose Validation.
3. Click the Settings tab. Choose Any Value from the Allow control’s dropdown. Or, simply click Clear All.
4. Click OK.
Solution 2 - Remove Excel Data Validation Rules via Paste Special dialog
1. Select a blank cell and copy it by pressing [Ctrl] + C.
2. Select the range you want to remove its validation.
3. Press [Alt]+E+S+N, which will launch the Paste Special dialog box with the Validation option selected.
4. Click OK.
I confess that the second solution is a bit cumbersome. Anyway, it works well.
If we’re working with multiple ranges as the sample below shows. Ranges DateWorked (A2:A8) and Lunch (D2:D8) both have simple, but different, validation rules applied. We can select them all and remove all of the validation rules at once. Select the ranges and do as the follows.
1. From the Name Box dropdown, choose DateWorked.
2. Press [Ctrl].
3. From the Name Box dropdown, choose Lunch to have a multi-range selection.
4. Click the Data tab.
5. From the Data Validation dropdown, choose Data Validation. In Word 2003, choose Validation from the Data menu.
6. Excel will pop up a warning message. Click OK to delete the validation settings for both ranges.
It’s a neat tip to use the Name Box control to select non-contiguous ranges to remove Excel data validation rules with multiple ranges.