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.
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.
11 Responses to “Who is the most consistent seller? [BYOD]”
The Date column in the sample file is Text not Dates
[…] http://chandoo.org/wp/2015/02/18/calculating-consistency-in-excel/?utm_source=feedburner&utm_med… […]
Great Chandoo. Keep it up, Looking forward more from BYOD..
Thanks
With Excel 2013 the pivot table could be connected to the data model which provides a distinct count.
This will do for invoice count
=COUNTIF(F:F,H12)
Instead of
=COUNTIFS(sales[SELLER],$H12)
Excellent document. How did you make the last graphic? Witch app. Thanks for answer.
Can someone tell me what =countif(sales[date],sales[date]) is counting? The value is 19. Its found in the =SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))
Hi Chris,
=countif(sales [date],sales[date]) function is counting the unique dates in the table.
Vândalo
Excellent document!
Can you explain more about the calculation on Weighted consistency? More specific the small number is 0,00001 ?
How come the number should be smaller if there is more sellers?
Hi,
Not understood this formula: {=SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))}
Please explain.
Thanks.