Isn't it wonderful when our colleagues want to help us? Can you just imagine the joy of being able to delegate a little data entry to a co-worker? Come on, visualize it with me... That big pile of forms magically disappearing as the data is all entered in by someone else's capable hands... Ahhhh isn't that nice? Well... of course it is, until... you view the data that has been entered afterwards and you see IL, ILL, Illinois, Ilinios, etc. and you realize that it really wasn't so helpful now that you have to go through and manually edit every error... Sigh... another bubble burst.

But wait, it doesn't have to be that way. There are some steps you can take to encourage consistency with data entry. One of the starting points is setting a few validation rules.

Data validation allows you to set limits on what information can be entered into a cell. For example, you could impose a maximum dollar limit that someone is permitted to charge or you could insist that it be a whole number. Then when someone enters an incorrect amount, it would have an error message (which you can customize) pop up to tell you that it is not acceptable or it could even stop that data entry person dead in their tracks until they bring the data up to your standards. In addition, validation allows you to enter a prompt to help the user put something in the cell.

Validation is particularly useful if you are going to have someone else (or multiple people) filling in information on a spreadsheet and you want to make sure that they cannot enter something that isn't at least reasonably close to what you want.

First step, select your cells where you want to apply the data validation rule.

Let's say you want to limit your State column to a two letter abbreviation. You can do that clicking the column heading (the letter in the grey box at the top of your column), then Data - Validation. In the Allow: field, click the drop down and choose Text Length. Change the Data: field to Equal To. Type in 2 for the length. You could at this point click OK and it would apply that rule to the cells you have selected, but first, let's look at the other tabs.

The Input Message tab gives you a check box to show the input message when a cell is selected. You enter a title and an input message. Go ahead. Put a little something in there to test it. This will create a small comment on the screen that will come up when somebody clicks on a cell that has data validation applied.

The final tab is Error Alert. Again you get that check box to show the error alert after invalide data is entered. There are three styles:  Stop, Warning, Information. Each style gives you a title and message, which become a pop up window when the information is entered incorrectly.

Stop does just what it says: It stops the data enterer from inputting that data in the cell until they fix it to what you requested.
Warning prompts the data enterer with a message and asks for a Yes or No to Continue.
Information simply tells the data enterer something about the data (for instance, Expenses over $25 require a receipt.)

All that will work nicely to force someone entering data to use the two letter abbreviation for states. But wait, if you REALLY want to get fancy, you could enter on your spreadsheet all fifty states, and then in the Allow: field on the first Data Validation window, choose List from the drop down. Then you can click in the Source field, click and drag to select all those cells where you typed the states. That way, they will get a drop down list of all the states where they can choose the correct state.  That really ensures that it will be more accurate.

Check out data validation rules. I bet they will validate my theory that Excel is a pretty awesome program!