Quickly Remove Data Validation Rules using Paste Special
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,
- Select the cells with data validation rules
- Go to Data > Data Validation
- Select rule as “any value’
- 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?!?
That is right. A simple paste special can remove the data validation rules for us quickly. Here is how.
- Select an empty cell without any data validation on it. Press CTRL+C
- Now, go back to the cell(s) from which you want to remove data validation rules
- Press ALT+E S N (In other words, Paste Special > Data Validation)
- 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.
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.
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« 3+1 Ways to Learn Advanced Excel||Financial Modeling School is closing in a Few Hours – Join Now! »|