Data Validation in Excel

Okay so let's say you have an Excel sheet and you want your user to type into a column the month something happened on. The problem with the general approach that people take with Excel sheets is that they don't explain exactly what they are expecting. Months could be January, Jan, or 01. Without spelling it out to users, you could have all kinds of results. Enter Data Validation... What this allows you to do, is specify what values are valid and which are not. You can provide an error message box for invalid values, you can provide a popup explaining what the user is to do when the cell is selected. You can also limit input to a list and then have that list provided as a drop down. Data validation provides a way to get consistent input for a column, which is very important! Let's take a look at where data validation is location on the Ribbon.

As you can see the Data Validation button is located on the Data tab, inside the Data Tools group. To use data validation, select the cell that you want to apply validation to and click the button. You will get a pop up box that presents three tabs: Settings, Input Message, Error Alert. The last two tabs (Input Message and Error Alert) basically show a popup message. The input message has a title and message and the message appears when the user clicks on the cell. The input message appears comment style, aka like a little yellow bubble coming out of the cell. The error alert appears when invalid data is entered and appears pop-up dialog style, stealing the focus from Excel proper. The error alert also has title and message, but also has dialog icon. The real meat and potatoes of this pop-up is in the Settings tab. By default, no data validation is equal to setting the Allow drop box to "Any value". Here's a list of the valid things you can select from in the Allow drop down list:

• Whole number (this is a non-decimal number)
• Decimal (this is a decimal number which means a non whole number)
• List (this is a predefined list.) **I will explain this one a little more in a bit.
• Date (this is a valid date, no time)
• Time (this is a valid time, no date)
• Text length (limit entry to a number of characters)
• Custom (allows you to specify a formula that will return true or false)

Now the Whole number, Decimal, Date, Time, and Text length all allow you to specify if you want a range of valid values (a < x < b), less than (or equal to) a value (x < a), greater than (or equal to) a value (x > a), not between values (a > x > b), equal to a specific value (x = a), and so on... Basically all those basic algebraic expressions. Again, custom is basically just using a formula. So that leaves us with the last option List. Now list is the one that I use the most often because it is the one that grants a drop-down box when you click the cell. You can either use a range on the sheet or you can provide a static list in the source box. Note: If you decide to use a range, it must be a range on the same exact sheet as the data validation. Excel will note let you choose a range on a different worksheet. To use a static list, just type the values you want to appear and put a comma between each value. On most of the validation options you can choose to ignore blanks or not. Ignoring blanks, means that when someone clicks the cell, if they don't enter anything, it won't count as an error. If you stop ignoring blanks then as soon as someone clicks the cell, they must enter a value that is valid to escape. If you decided to turn off ignoring blanks, make sure you make it crystal clear what is and is not valid. Nothing is more annoying than to not be able to escape a cell in an Excel worksheet. So that's cell validation. In the next go-round, I'll show you how to use VLOOKUP and data validation to make some Excel magic. Cheers!