If you use Excel spreadsheets to compile other people's data, but you find that they often fill your carefully planned cells with the wrong kind of information, data validation can help.
This tool enables you to restrict specific cells to allow only properly formatted data. If someone enters something that is not supposed to be there, What “lunch at the airport” instead of “$ 15.68” in an expense report, Excel rejects input until successful. Think of it as a passive-aggressive way to make sure people don't waste their time.
As an example, here is the basic expense report worksheet for How-To Geek. Let's say we want to make sure people only enter numeric values formatted as currency (In other words, some digits, followed by a decimal point, followed by two more digits) in certain cells.
First, select all cells you want to restrict.
Switch to tab “Data” different first page “Data validation”. If your window is not full size and you cannot see the labels, is the icon with two horizontal squares, a green check mark and a crossed red circle.
In the Data Validation window, in the tab “Setting”, click drop down menu “Let”. Here, you can determine a specific type of input to allow selected cells. For our expense report, we will insist that users enter a number with two decimal values, so we would select the option “Decimal”. You can also choose other criteria, how to make sure a cell contains text, a time or date, text of a specific length or even your own custom validation.
Regardless of the type of data you select from the drop-down menu “Let”, will change the alternatives available in the rest of the tab “Setting”. Since we want a numerical value relative to the currency, we are configuring the drop-down menu “Data” in settings “Come in”. Subsequently, we set a minimum value of 0.00 and a maximum value of 10000.00, which is much more than enough to cover our needs.
To prove it, click on “To accept” to apply the validation settings and then try to set an incorrect value. As an example, if we write “pancakes” for the value of Breakfast instead of the cost of the meal, we will get an error message.
Although that restricts people to entering only the correct type of data, does not give them any comment on what kind of data is required. Then, let's set that further.
Return to the Data Validation window (Data> Data validation on the ribbon). You have two options here (and you can use both if you want). You can use the tab “Input message” for a tooltip pop-up to show people the type of data they want each time they select a cell for which data validation is turned on. You can also use the tab “Error Alert” to customize the error they see when they enter the wrong data type.
First let's switch to the tab “Input message”. Here, make sure the option “Show input message when cell is selected” is activated. Subsequently, give a title and some text to the data on input tools. As you can see below, just clicking one of the cells brings up the message so people know what to expect.
In the tab “Error Alert”, you can customize the error message people see when they enter the wrong data type. Make sure the option “Show error alert after entering invalid data” is activated. Choose a style for your error message from the drop-down menu “Style”. Can go with a stop (the red circle with the X), an advert (a yellow triangle with an exclamation point) or some information (a blue circle with a “i” lower case), depending on how strongly you want the message to get across.
Write a title for your message, the text of the message itself, and then press “To accept” to end.
Now, if someone tries to enter wrong data, that error message is a bit more helpful (the sarcastic, If you prefer).
It's a bit more field work to set up data validation, but it can save you a lot of time later if you use spreadsheets to compile other people's data. It is even useful to prevent your own mistakes. And this is doubly true if you have set up formulas or any kind of automation tasks that focus on that data.
