Data Validation with Excel’s Built-In Data Validation Tools

Many developers will admit that when it comes to creating a user friendly application, more effort is often committed to controlling user inputs than actual functionality and design. Excel’s data validation tools allow you to easily deploy validation features on any spreadsheet project.

Difficulty Beginner
Time Est. 30-45 Minutes
VBA/Macros No
Template Files None
Solution Files 2016-10-17-excel-validation-tool

Spreadsheet Design Exercise 5

This exercise of the Spreadsheet Design series, continues the focus on Data Management.

As discussed in the Data Management summary, one of the goals of an effective Excel application is to separate the user from the raw dataset. However, the user must also be able to read, write, or update when needed, otherwise the Excel application is effectively useless.

This is where control comes into play. While you won’t be able to look over every user’s shoulder to monitor inputs, there are various features you can implement with your Excel application to control inputs. Controlling inputs is usually done through data validation — a process where the program confirms that the data entered by a user conforms to strict dataset guidelines.

As an Excel user, you likely understand very well the importance of clean data. Many of us have spent countless hours trying to manage downloaded datasets with numbers formatted as text, or stuck in VLOOKUP nightmares with category names that are slightly off from another large dataset. While workarounds can help manage these issues, the best practice is to protect data at its source.

Software and web developers will often talk about data validation in layers. Layers refer to which point within the data submission process (from user interacting with the form, to placement in the database) validation takes place. Many applications use a combination of layers, even with some redundancy. Given that Excel is not a database management system (DBMS), there isn’t nearly as much depth to validation processes. In DBMS-speak, Excel’s validation features would exist mostly within validation layer 1, which is the user/form interface, but we could say that a few sublayers exist within layer 1 for us to work with. Here’s an in depth article with more information specific to data validation within databases and software.

This post will discuss data validation using Excel’s built in Data Validation tool. The next post will provide an overview of validation techniques using formulas and conditional formats. The final post on validation will discuss validation techniques in Excel VBA, which provide for even more control and features.

Step 1: Build user input form.

The exercise will start with a simple order form for a winter sport/clothing company. The form asks for personal details, order, and pricing information.

2016-10-17-1

You may not realize it, but if you copied this form exactly how it is in the image, you have already initiated a validation technique. Various cues within the form to help guide the user as to what type of information should be inputted. Many of the fields here are quite obvious: Name, Address, City, Price, and Total. For those fields which are less obvious, a small description of how to interact with the field has been placed in column E.

Step 2: Use built in validation tools to add validation rules.

Excel’s data validation tool allows you to restrict what the user puts in a cell, and can return various warning messages alerting the user to the issue. Let’s start by walking through how the data validation tool can work for the “City” field. With the form input for “City” active, click on Data > Data Validation > Data Validation, as shown below.

Navigate to data validation tools.

The data validation window will appear, as follows:

2016-10-17-3

The approach to take when validating fields is often a judgement call. For a city, the value will almost always be alpha only, and may never be more than 50 characters long. However, unless we get a list of every locale in the country (or world), we actually will not know this for a fact. It might be a good idea to keep this open to any value. However, for the purpose of this form, let’s assume that WinterWares has three locations: New York, Boston, and Washington DC, and it only provides delivery in those cities.

With that, let’s restrict the user to entering 3 city values on the form. Within the Data Validation window, click on the Allow drowpdown and select List. In the source field, type “New York, Boston, Washingon”, as shown below.

2016-10-17-4

Now click on the Input Message tab. This will provide the user with a simple tooltip when they click on the cell.

2016-10-17-5

Sometimes, it may not be obvious that a cell has a dropdown menu, so this can be a helpful feature for users.

2016-10-17-6

Next, click on the Error Alert tab. This will allow you to send a message to the user when an incorrect value is entered.

There are three styles, each with a Title and Error message that will be sent to the user. With dropdown lists, the user still has the ability to manually type values into the cell. If the typed value matches a value in a dropdown list, the validation will pass. If it does not exist, an error message will come up if this feature is active. The error message style is the same for all the other restrictive validation types.

  1. Stop is the most restrict style. The user will not be able to move the cursor from the cell until a correct value is entered, which may be an undesirable feature. I will speak more about this in a later post.

2016-10-17-7

  1. Warning is less restrictive. It will present the user with the error message, but will ask if they want to continue. If the user selects yes, whatever value they manually typed into the field will remain.

2016-10-17-8

  1. Information is the least restrictive style. It will present the user with an error message informing him/her that they must select from the dropdown, however, the user can move on from the cell regardless if he/she presses OK or Cancel

2016-10-17-9

State

We’ll apply the same validation treatment to state, with a slightly different approach. Instead of listing out the values of the list in the Source field, create a named range with listed values. This approach is great for large lists, or lists that may change at a later date.

Create a list of states on Sheet2. Given that our list of cities is very limited, you will only need to include 3 states.

2016-10-17-10

Within the validation window, type “=State_List” into the source field.

2016-10-17-11

Phone

For phone, assume that data entered will be a whole number between 10,000,000,000 and 19,999,999,999. A text cue in column E asks users to enter digits only.

2016-10-17-12

Item

Item can be another dropdown list, but let’s take a somewhat elaborate approach. Create a new sheet (Sheet3) and build a price list, with column A containing a named range, “Item_List” of items, and column B containing their price.

2016-10-17-13

Now add dropdown validation to the cell.

2016-10-17-14

Price

Let’s jump over to price and use our price list to reference the price. This can be accomplished with a VLOOKUP. In the Price field, enter the following formula:

=VLOOKUP(D10,Sheet3!A:B,2,FALSE)

D10 should reference the cell containing the item name. If done correctly, the price of the corresponding item should be shown. The VLOOKUP function will be discussed extensively in future posts.

Quantity

Assume that this will be a whole positive number. 0 to 10,000 is a good range.

2016-10-17-15

That covers the validation requirements for this form. For now, ignore validation requirements for Name and Address, but some advanced web-based programs would check this against USPS records or other directories.

Here’s the final result.

2016-10-17-16

You may notice that the Price and Total fields now have a grey background. Given that these fields are completed through a calulcation, the grey background acts as a visual cue to the user that the field does not require any input. Later in the series, during the part on Security, you will learn about locking cells to prevent users from interacting with any non-input parts of a spreadsheet.

Screen Captures

2016-10-17-16
2016-10-17-excel-validation-tool

Drawbacks and Potential Bugs

While Excel’s built in Data Validation tool is the quickest way to implement validation within your Excel application, it is by far not the most elegant approach. The violation warning messages tend to be clunky and unfriendly, and there are a number of validation requirements which are tricky to implement. The following two posts will discuss more advanced topics and approaches related to data validation.

Other Notes

This post briefly went over Excel’s built-in tooltips feature. You may also be interested in learning about a custom VBA tooltips solution that provides more flexibility with content and design.

Leave a Reply