fbpx
Search
Close this search box.

Quickly Remove Data Validation Rules using Paste Special

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

14 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

  12. Tammy says:

    How can i remove data used for the validation after i am done

  13. Sandeep Kothari says:

    Great tip!

Leave a Reply