Excel Tip: Data Validation

The purpose of Data Validation is to control the contents of a cell. For the full details, visit the full text article.

Useful When
Data validation is very useful when the workbook is used by numerous people, or used by someone other than the person who created the workbook.  It is also useful when you require that data be entered in a consistent manner.  For example, you would like months always entered using a 3-letter abbreviation.  Without data validation, it would be difficult if not impossible to ensure that (1) a 3-letter abbreviation is used and (2) the same 3-letter abbreviation is used all the time.  Data validation is also useful when you would like to provide the user a list of choices for a cell.  For example, a chart of accounts listing of valid account numbers.

Detail
Data validation is a built in feature in Excel, and can be used in many ways by the accountant.  Data validation is a method for restricting the data that is entered into a cell.  The user can restrict the data to a range of values, for example, only numbers > 0.  Or, the user can restrict the data to a list of specific values, for example, a list of valid account numbers.

When the user restricts the data to a list, the cell displays a drop down button as shown in Figure 47.

Figure 47

The obvious benefit to ensuring that all data in a column is entered consistently, is that you can sort, group, subtotal and create pivot tables on the data knowing that Cash is always spelled “Cash”, rather than say “Cash and equivalents.”  Having consistent data is crucial.

Exercise
To demonstrate data validation, we will use three exercises. 

Limit to list exercise
This exercise will demonstrate how to restrict the value of a cell to a list of predefined values, an account list.

The first step is to set up the account list, as shown in Figure 48.  Notice that I have named the range, “acct_list”.  (See Named Ranges section above for additional information on naming ranges.)

Figure 48

This account list is on a separate worksheet in my workbook.  The next step is to instruct Excel to limit entries in specific cells to items in the account list.  You will notice in Figure 47, the user obtains a drop down box when the active cell is in the Account column.  To achieve this functionality, simply highlight the cells that you would like to restrict, and then choose Validation from the Data menu.  You will be presented with the data validation dialog box as shown in Figure 49.

Figure 49

In the Allow field, choose “List”.  In the Source field, enter the named range, in our case “acct_list”.  Make sure that the In-cell dropdown field has a check mark in it.  Now, when the user clicks in a cell in which you have set up data validation, they will see the drop down button with the choices of the items in your named range.

As you can see, this functionality has wide application.

Restrict to range of values
In addition to restricting values to a list, you can also set up parameters for values keyed in by the user.  For example, you can instruct Excel to only allow whole numbers between 1 and 1000.  To obtain this functionality, rather than selecting List from the Allow field as shown in Figure 49, select whole numbers as shown in Figure 50.

Figure 50

Then, simply set the range of acceptable values, in our case, 1 to 1000.  Now, Excel will only allow the user to enter whole numbers from 1 to 1000.  Notice, there are many other restrictions you can place on data, as shown in Figure 51.

Figure 51

As you can see, having the ability to restrict data entered is very useful, especially when there will be multiple users on one workbook.

Set input and error messages
One final point is that in order to make the data validation more user friendly, you can set up messages to the user.  For example, when a user clicks on a cell that contains data validation, a message can pop up that states something like “Please select an account number from the list”, or “Please enter a whole number from 1 to 1000.”  You can set up these messages by using the Input Message tab as shown in Figure 52.

Figure 52

If the input message is set up, then the user will see a message when they select the cell, as shown in Figure 53.

Figure 53

You can also establish error messages, in the event the user did not enter a value in the acceptable range.  To set up the error message, use the Error Alert tab, as shown in Figure 54.

Figure 54

After the error message is set up, the user will obtain an error dialog as shown in Figure 55.

Figure 55

Investing the time to set up data validation will help to reduce the risk of error in your workbooks.