Business Solutions:   Excel-Validated Input

        by James D. Duncan, CPA     October 2004

 

Input of correct data is the foundation of spreadsheets.  We all need all the help we can get with input.  Excel has a data validation tool that assists us with getting the correct data into the cells.  You want dates in date fields, numbers in number fields and so forth.  It is obvious to us building the sheet, but may not be so clear to the next person to use the sheet.

 

I used Excel 2003 for this example.  First in cell A1 enter “Description”, then in A2 enter “Date”, and in A3 enter “Number between 10 and 20”.  In cell B1 enter “Data”.  You will have a sheet that appears as so:

 

 

Click into cell B2; click on the Data icon at the top, and then click on Validation.  You will get the Data Validation window that looks like this:

 

 

Now we are ready to validate (restrict) the input into cell B2.  We want this cell to allow dates.  So click on the drop-down button in the Allow box and select date.  We get the following window: 

 

 

We now need to choose the Start date and End date.  I will use January 1, 2004 and December 31, 2004, for example. 

 

 

Now click on the Input Message tab and we will help the user with some messages.  I entered “Date” into the Title field and “Input date here” in the Input Message box.  You can be more creative than that.  See the example below for the screen and input.

 

 

Now click on the Error Alert tab and we get this cute little window.  You can see how I have filled in the Title and Error Message boxes.

 

 

Click on OK and enter an erroneous date in B2. You get the following box with the Date Error in the title and “Not a valid date” in the message….How about that?

 

 

Key in 01/01/04 or a valid date and you are ready for the next validation.

 

Click into cell B3, click on Data and then on Validation and we will put in the restrictions for a number between 10 and 20. The screen will begin like this:

 

 

Fill in the screen by picking Decimal in the Allow box, Between in the Data box, enter 10 in the Minimum field and 20 in the Maximum field.  Click on the Input Messages and fill in appropriate messages and do the same for the Error Alert tab. Then press OK and enter a bad value in B3.

 

 

 You can see there are many ways to help the user get the correct data entered into the sheet.  Put the validations in and help all you can.  Even if you are the only one using the data, why chance errors.  May the force (of Excel) be with you.

 

James is a member of the CCOKC and an expert in Access and Excel. Click here to reach James.