Excel is a must-have program in almost every office because it allows business users to carry out a wide variety of tasks, from basic finances all the way up to advanced data tracking. Because of this, Excel has a number of features to make entering data easier. One such feature is data validation.

Here is an overview of Excel’s data validation.

What is data validation?
This useful Excel feature allows users to set a limit as to the type of data that can be entered into a cell or cells. If you use this feature you can prevent users from entering invalid data types, warn them when invalid data is entered, or let them know what type of data the sheet is expecting in the selected cells.

You can find this function by clicking on the Data tab in Excel and looking under the Data Tools group. When you click on it, a window will open allowing you to configure and set a data validation.

How can it be used?
There are a number of uses for data validation, the most popular being when you will be sharing a spreadsheet with other users who will be inputting data, and you need to retain a particular design or format. Other uses include:

• Limiting number input – For example, if you have a form that requires users to input a 10 digit phone number, you can set a validation so that only 10 digits can be entered.
• Limit choices to a list – If you have a spreadsheet where users need to pick data from a list of choices, you can set the choices in the validation and users will only be able to select from the values that you have specified.
• Setting maximum dollar amounts – If you have a spreadsheet with salary bonuses for your department, and the maximum bonus is 20% of the yearly salary, you can set a validation that allows for a maximum of 20%. If a user enters 21%, they will get an error message.

How do I create a data validation?
You can create a validation by following these steps:

1. Set up your spreadsheet as you want the user to see it. You don’t have to put any numbers in, just create the layout.
2. Select the cells to which you wish to apply the validation by clicking on the uppermost cell and dragging down to the lowest.
3. Click on the Data tab above the spreadsheet
4. Select Data Validation from the Data Tools group.

A window will open with three options: Settings, Input Message and Error Alert.

Under Settings pressing the arrow under Allow: will let you pick what type of data you want to be entered in that cell. For example, if you are going to limit the cells to only whole numbers, select Whole Number from the drop-down list. The other options will change depending on the type of validation you pick.

Input Message will allow you to set a message that pops up when a user hovers their mouse over the cells. To attach a message, simply enter a title for the message in the Title box and the actual message in the Input message: box. Press Ok and the message should show up when you hover your mouse over the cells.

The Error Alert tab allows you to set and customize an alert that will show when a user enters an invalid form of data. You can select from a number of different styles and icons and even configure the error message that will be displayed.