Data validation in MS Excel
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.
