Two quick methods for removing data validation rules in Excel

Validation is a powerful feature that lets you control the data users can enter into an Excel sheet. Chances are that once you’ve put these rules into place, you’ll not want to remove it, but if you do, you might go about it the same way you set them:

1. Click the Data tab.
2. In the Data Tools group, click the Data Validation dropdown and choose Data Validation. In Word 2003, choose Validation from the Data menu.
3. On the Settings tab, choose Any Value from the Allow control’s dropdown. Or, simply click Clear All.
4. Click OK.

That’s the mouse way; now, let’s work through another method:

1. Select a blank cell and press [Ctrl]+C.
2. Select the range you’re removing validation from.
3. Press [Alt]+E+S+N, which will launch the Paste Special dialog box with the Validation option selected.
4. Click OK.

Now, I admit that I don’t find this second method any easier than the first. In fact, I find it a bit cumbersome. However, I watched someone use it a few days ago and she was flying—so your mileage may vary!

If you’re working with multiple ranges, you can select them all and remove all of the validation rules at once. Here’s a quick example using the sheet shown below. Ranges DateWorked (A2:A8) and Lunch (D2:D8) both have simple, but different, validation rules applied (the rule itself isn’t important.) If you select both ranges, Excel won’t let you work with the validation rules because they’re different, but it will let you delete the validation rules. Let’s select the ranges and see what happens:

1. From the Name Box dropdown, choose DateWorked.
2. Press [Ctrl].
3. From the Name Box dropdown, choose Lunch. At this point, you 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. When Excel displays the warning message, click OK to delete the validation settings for both ranges. Click Cancel if you don’t want to delete the settings.

Using the Name Box control to select non-contiguous ranges is a neat tip in an of itself!