Validating Input in an Excel Spreadsheet - Why and how it’s done

So, you have just put together a masterpiece of an Excel worksheet, which your colleagues will now use to record and submit various information… well, there’s one very important issue you must tackle before calling the job done: input validation. Whether you have built an expense form, a budget form, or an inventory sheet, data validation is an important part of any spreadsheet that accepts input. If your spreadsheet makes calculations based upon that input, validation is absolutely essential!

Validating input means to ensure the data entered into your program or spreadsheet by the end user matches a certain set of predefined criteria. If you have a cell that is expected to contain a number, allowing the user to enter a date may break any formulas referencing that cell. Here are some examples of invalid data:

• A decimal entered in a date cell.
• Text entered into an integer cell.
• A percentage entered into a currency cell.

Trusting your users to enter the correct data is never a safe assumption. Whether you are developing software or just building spreadsheets, a good design must include safeguards against failure.

C’mon, really? What could go wrong?

Let us assume you have designed a budget spreadsheet for your department’s use. This spreadsheet asks the user to enter spending data, and calculates your department’s total spending based on that input. Your colleague accidentally enters text into a currency field, which causes your spending calculations to be incorrectly under budget. Consider the following outcomes:

1. Your colleague doesn’t notice his error, and sends the results off to accounting. Shortly thereafter, you receive notice that your department’s budget has been cut due to lack of spending.
2. Your spreadsheet sounds an alarm, and pops up an error message that reads “Invalid entry: please enter only dollar amounts into this cell.”. Since you designed the spreadsheet, your colleague asks you what the error message means.

If your spreadsheet does not validate input, you will be stuck with outcome #1. After tracking down the error, you will be stuck making appointments with various other departments in order to explain the error and rectify the situation.

Had your spreadsheet been correctly designed to validate input, you will encounter the much more favorable outcome #2. After a quick glance at the spreadsheet, you tell your colleague that he entered text in a column that expects a dollar amount, and the mistake is caught before it becomes a problem. Everyone goes out for drinks.

How to Validate Input with Excel

Validating input in an Excel spreadsheet is quite simple, and requires only a few clicks of the mouse. The first step is two click on the cell whose data should be validated. Then, in the menu bar across the top of the Excel window, click Data → Validation.

After you click on Validation, the Data Validation window will appear. Here’s what it looks like:

This data validation window contains three tabs:

1. Settings - This is where you define what sort of data is expected. Any data that does not match the criteria you specify here is considered invalid.
2. Input Message - When the user clicks on your validated cell, the title and description you enter here will appear in a tooltip box. If the user should see any special instructions before entering data, include them here.
3. Error Alert - This is where you define the error message shown when Excel detects invalid data. The title and error message entered here will be shown in a pop-up error dialog. Make this message as informative as possible.

Under the Settings tab the box labeled “allow” contains a number of data types, such as “date”, “time”, “decimal” and “whole number”. Only the data type that you select will be allowed in your validated cell(s). In addition to selecting a specific data type, you may also define the minimum and maximum values that are considered acceptable. Entering any other type of data into the validated cell(s) will cause the error message of your own choosing to appear on-screen.

Here is a screenshot of some sample validation criteria I have set up:

As you can see, only decimal values between 5.0 and 10.0 are allowed. If I enter “11.0″, the following error message stops me dead in my tracks: