Software

How to use Excel's Data Validation feature to prevent data entry mistakes

Your application is only as strong as your data. Learn how to restrict input values using Excel's Data Validation feature.

hero
Image: iStockphoto.com/© ra-photos
When accuracy is important (and when isn't it?), validating new data is imperative. This process can be lengthy and complex—or it can be simple. Fortunately for Excel users, the Data Validation feature is more than adequate for most requirements. Use it when you want to limit users to specific values to avoid typos that don't sort and group correctly and to guarantee that input values are the right data type. For instance, you might want to limit choices to a specific range of dates, a list of classes or departments, or integers or decimal values. Over the next few months, we'll review this built-in feature, from the basics to enhancing with VBA. This month, we're going to start with the basics: how to populate the validation list using static and dynamic lists.

I'm using Excel 2016 on a Windows 10 64-bit system, but most everything in this article applies to earlier versions (specific instructions for 2003 are limited). For your convenience, you can download the example demonstration .xlsx, and .xls files.

The basics

Excel's Data Validation feature is a powerful tool with options that allow for flexibility. We'll start with a quick example that limits input to a static list of departments:

  1. Select the input cell.
  2. On the Data tab, choose Data Validation from the Data Validation dropdown (Figure A) in the Data Tools group. A single click in the option will open the Data Validation dialog, bypassing the dropdown list, but you should know that other options are available. In Excel 2003, choose Validation from the Data menu.
  3. To display a list of options, choose List from the Allow dropdown.
  4. To display a static list, enter values separated by commas in the Source control (Figure B). If the list needs to be in a specific order, enter the items in that order.
  5. When warranted, you might want to display descriptive information to help users. Click the Input Message tab and enter a title and a meaningful explanation of what's expected (Figure C).
  6. Click OK and use the dropdown list in the Data Validation control to see the static list (Figure D). Selecting an item sets the control's value.

Figure A

Figure A

Access the Data Validation feature.

Figure B

Figure B

The List option accepts a static list of input values.

Figure C

Figure C

Guide the user with meaningful information.

Figure D

Figure D

Choose an item from the dropdown list to set the control's value.

We didn't set an error message, as doing so is often unnecessary. If you try to enter a non-list item, Excel displays the generic message shown in Figure E. If you need a specific error message, use the Error Alert tab.

Figure E

Figure E

The generic message is adequate for most situations.

A static list is probably the most limited. If you want to add or delete an item, you must open the Data Validation dialog and alter the list accordingly. Let's move on to a more dynamic solution, using a named range as the control's source list.

Dynamic solutions

The above approach is simple and gets the job done. But if you need to update the list occasionally, you might want a more reasonable solution. The trick is to make the list dynamic so you don't have to update the source reference via the Data Validation dialog—ever. There are two ways to do this: Use a named range or use a Table.

A named range comes with inherent problems; some are easily managed, others aren't. A Table is your easiest option and I can't imagine not using this option if available. I'm including the named range solution for 2003 users only. If you're using an .xlsx version, you don't have to work this hard and you should feel free to skip to the next section.

SEE: Master sorting basics in Excel

Using a named range

To get started with the dynamic range solution, enter the list and then name it as follows:

  1. Select the list and header.
  2. Click the Data tab and click Create From Selection in the Defined Names group.
  3. Accept the default, Top Row, and click OK.

Now you can base your list on the source as follows:

  1. Launch the Data Validation dialog box and choose List from the Allow dropdown.
  2. In the Source control, enter the name of the list (Figure F).
  3. Click OK to close. Figure G shows the resulting dropdown.

Figure F

Figure F

Specify the named range as the source.

Figure G

Figure G

Excel uses the values in the named range to populate the list.

Now let's discuss the problems you might run into. If you delete an item, you end up with a blank in your list. If you add an item without updating the name's range, the list won't include it. As is, this type of source list isn't much better than the static list until you make the named range dynamic using an OFFSET() function that defines the size of the range. In this example, there's only one column, but an indefinite number of rows. We can best support this configuration using the following syntax:

=OFFSET(sheetname!firstcellinrange,0,0,COUNTA(sheetname!absolutecolumnreference),1)

That's probably clear as mud. In a nutshell, the COUNTA() function counts the number of cells that contain a value, allowing the named range to update its reference automatically. For this reason, you must not enter data below your named range. Nor should you reference the list's header cell.

To make the Chocolates_List named range dynamic, use the Name Manager to change the reference to:

=OFFSET('Named Range'!$B$4:$B$8,0,0,COUNTA('Named Range'!$B:$B),1)

as shown in Figure H. Then, click Close and confirm the change to return to the sheet. Now, when you update the source list at the sheet level, the validation control also updates, as you can see in Figure I.

Figure H

Figure H

Change the reference to create a dynamic range.

Figure I

Figure i

The validation list automatically reflects changes made to the source.

Using a Table source

Using a Table as a list source is the easiest solution, but it only works in .xlsx versions. The validation list updates as you update the Table source without any extra work on your part. First, let's create the Table as follows:

  1. Click anywhere inside the list.
  2. Click the Insert tab and the click Table in the Tables group.
  3. In the resulting dialog, check the My Table Has Headers option (Figure J) and click OK.
  4. With the Table selected, enter a meaningful name for the Table using the Table Name control in the Properties group.

Figure J

Figure I

Create the Table.

Although Excel automatically assigns a range name to a Table, you can't directly reference the object as a validation control's source list. But there is an easy workaround—reference the Table as a second named range, as follows:

  1. Click the Formulas tab.
  2. In the Defined Names group, click Define Name.
  3. In the resulting dialog, enter a name for the list.
  4. In the Refers To control, reference the Table by name (Figure K). The [Chocolates List] reference is the column (in the Table) that contains the list of input values.

Figure K

Figure K

Reference the Table.

Next, build the validation control as you normally would but reference the second named range, ChocolatesList, as shown in Figure L. Updating the Table automatically updates the validation list, as you can see in Figure M. After adding Carob to a new row in the Table and sorting the column, the validation list displays an updated and sorted list. The Table solution took little setup and little specialized knowledge.

Figure L

Figure M

Reference the Table range.

Figure M

Figure N

Updating the Table updates the validation list.

Quick tip

You might have noticed in Figure M that the cell with the validation control is easily visible because of its bright Fill color. This is an easy way to help users find these controls. Unless the validation control cell is selected, the cell looks like all the rest.

SEE: Create a quick and effective dashboard using Excel's PivotChart and Slicer objects

Next month

Knowing the basics open up the possibilities for using Data Validation to protect the validity of your data and therefore, the validity of your application. In this article, you learned how to create a validation control and how to update its list by using a dynamic source. Over the next few months, we'll expand on the basics with some more advanced topics:

  • How to alphabetize a list using VBA
  • How to display a default item at the top of the list
  • How to populate a validation list with symbols
  • How to support business rules using VBA
  • How to make the dropdown list temporarily wider
  • How to zoom in on the validation list

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Also read...

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox