Excel Template
Excel Tips and Tricks

Excel Template with Protection and Validation

We often required an Excel Template to send some persons or teams so that they can enter the data in the template and can sent it to us and then we can consolidate the data from all the templates. But in this case only challenge is this if your template does not have proper protection and validation then people can enter the invalid data in the template.

Here we have create an Excel template of Overtime payout with Protection and Validation.

Excel Template

If you enter the invalid employee id then it will display below given error

Invalid Emp Id error
Invalid Emp Id error

If you enter the invalid date then it will display below given error

Invalid Date Error
Invalid Date Error

For overtime hours we have put the validation that it will not take over time greater than 10 hours. If you will enter overtime greater than 10 or any text then it will display below given error.

Invalid Overtime Error
Invalid Overtime Error

We have put the formula on Payout column and have hidden and protected this.

Click here to download this excel template

 

Watch the step by step tutorial of this excel template-

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com

Leave a Reply

Your email address will not be published. Required fields are marked *