Microsoft Office 365 is one of the most popular offerings from the well known software giant.
With cloud based versions of popular Office programs like Excel, businesses can create nearly any type of document. Excel has a number of useful features that make entering or manipulating data easier, for example 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 give them a message as to the type of data you want entered.
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 numerous uses for data validation, the most popular being when you will be sharing a spreadsheet with other users who will input data into an already designed spreadsheet. 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 numbers can be used.
- 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 list.
- 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:
- Setting up your spreadsheet as you want the user to see it. You don’t have to put any numbers in, just the layout.
- Selecting the cells you want to apply the validation to by clicking on the upper most cell and dragging to the lower most.
- Clicking on the Data tab above the spreadsheet
- Selecting 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 will pop-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.