Last Updated on April 20, 2020
Working in a team can be irritating if some of the team members don’t play by the rules you all agreed when you started a project. Imagine this situation: for this team, you created some Excel templates which the other members need to fill in and return them to you, so you can put them together. Suppose that one of your table fields is the date at which a sale has been done, during the current year. If team members do not pay attention, they don’t use the date format in the way you want, or they make mistakes and write there instead of the current year, some 2023 or whatever other bothering thing that you’ll have to correct?
Well, when you design your template, you can easily restrict what kind of values can be input in every cell of your table. The command is called Validation and you can find it by accessing in the main menu, Data – Validation.
Let’s suppose you want to restrict a column to be used only for inputting dates in the current year.
How to proceed:
- First, you select the cells which you want to restrict.
- Then, in the main menu, go to Data – Validation. A popup menu opens: it has three tabs: settings, input message and error alert.
- In Settings, in the Allow field, you select Date.
- In the Data field you select Between (this is set by default).
- In the Start date field you input the first date you want to be recognized as valid (01/01/2006 or 1 Jan 2006).
- In the End date field you input the last valid date (31/12/2006 or 31 Dec 2006).
- Now press OK and you are finished.
Yet, if you want your users to know about this enhancement you made to the worksheet, instead of pressing OK, you go to the next tab, Input Message. Here you give a title and a message which the users will see as a comment when they click on one of the cells selected by you for validation.
Furthermore, if you go to Error Alert tab, you can input there a message that the user will see when trying to input in those cells something that does not match your criteria.
Data Validation Tip: the validation property is maintained when applying the Copy – Paste command.
If you are interested in more resources, you can find some great ones here.
how many if commands can i take in MS-excel in the Neasted loop.
I want some help in MSExcel….
when u go to menu: data –> Validation–> in settings under the coloum Allow you have “List” option.
Using this we can select things from a Dropdrop list, which we give.
can u tell me, I want a name colom in Excel, Were I am using this list. I also want to enter my own values apart from that list in the same colm. Is it possible.
Thank you for a useful tip about Data Validation.
However, I would like to point out that the Data “validation property” is NOT maintained during Copy Paste. If you copy and paste a value that is outside of the validation range, the validated cell will accept the pasted value! I was looking at this site for a solution. No cigar this time, I’m afraid.
Martin,
Thank you for your input. You’re right about the Copy-Paste. We’ve got a misunderstanding here: I wanted to say that if I copy one of the cells which has a validation rule set, when I paste it elsewhere, it keeps this property.