Quickly Remove Data Validation Rules using Paste Special

Posted on March 4th, 2011 in Learn Excel - 12 comments

Excel Data Validation Rules - Example

Here is a quick tip on removing data validation rules.

We all know that data validation is a powerful feature in Excel to control what data is entered. But, sometimes, you may want to remove the rules from a set of cells. How would you do it?

The normal approach is,

  1. Select the cells with data validation rules
  2. Go to Data > Data Validation
  3. Select rule as “any value’
  4. Press OK

In short, you have to move your mouse alot. It would make sense if we accumulate frequent flier miles on the mouse travel too.

Shouldn’t there be a shorter, quicker way?!? Well, of course, there is a way.

We use Paste Special.

What ?!? Paste Special?!?

Remove Data Validation Rules with Paste SpecialThat is right. A simple paste special can remove the data validation rules for us quickly. Here is how.

  1. Select an empty cell without any data validation on it. Press CTRL+C
  2. Now, go back to the cell(s) from which you want to remove data validation rules
  3. Press ALT+E S N (In other words, Paste Special > Data Validation)
  4. Press OK and you are done.

How does it work?

Simple, we took the data validation rules from empty cell in (1) and then pasted them over our original cell(s) using Paste Special option.

What next?

Nothing, Its almost weekend here. We are taking a train to go to a place 400 kilometers away to see a classmate and close friend getting married. Lots of fun and partying awaits. When I am back on Monday, I will surprise you with more Excel awesomeness.

Go ahead and enjoy your weekend…

PS: Paramdeep reminds me that Financial Modeling School enrollments close on March 8th – just 4 more days. Sign-up already.

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

12 Responses to “Quickly Remove Data Validation Rules using Paste Special”

  1. Khushnood Viccaji says:

    This is interesting...
    but it takes 7 - 8 keystrokes to clear validation with paste special
    and 6 keystrokes to clear it via the data validation dialog box

    • David says:

      What the heck is ALT+E S N? Hold down the Alt key while pressing and holding the other keys? Hold down the Alt key while pressing E then release and hit the other two keys?

      I have Excel 2010 and neither works.

  2. Charley says:

    Nice trick! It is always hard to clear all validation in a sheet.

    @Khushnood Viccaji It is a lot harder to clear a sheet with a lots of cells with different validation rules

  3. Luke M says:

    @Charley
    Use the Go-to dialogue (Ctrl+G), Special, select Data Validation (you can choose to select all, or just similar to current active cell). Then it's a quick step to clear all data validation.

  4. Gregory says:

    If you're not worried about keeping the formatting, just copy an empty cell (Ctrl+C) and paste (Ctrl+V) over the cell with conditional formatting. Or better yet, click the cell with conditional formatting and either use Ctrl+D to copy down a blank cell above, or Ctrl+R to copy a blank cell from the left.

  5. Simran says:

    I simply copy and do paste with "paste special" and "values" selected from edit. No formatting of cells, I am done with pasting. Quickest 3 steps.

  6. Jagdish says:

    @ Luke
    Its amazing.. it saves a lot of time by clearing all the Data Validation by combining two tricks..

  7. Charley says:

    @ Luke M
    Nice trick indeed.

  8. Chandoo says:

    @Luke.. interesting tip... thank you for sharing. Here is your donut ....

  9. Abhijit Sharma says:

    Hi chandoo,
    First of all many many thanks, because I subscribed just one month but i improved a lot.
    Once again thanks.
    Regards,
    Abhijit

  10. Manjunath N says:

    hi Chandoo,

    Thank you so much, i've learned lot of from this site.

    Regards,
    Manjunath N

  11. SPEAR says:

    One click only ....add this to your VB module and then create a new button on your toolbar...
    Sub RemoveDataValidation()
    With Selection.Validation
    .Delete
    .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
    :=xlBetween
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowInput = True
    .ShowError = True
    End With
    End Sub

Leave a Reply