Any time that we use a lookup function in Excel, we open the spreadsheet up to potential problems with errors. This is because lookup functions such as VLOOKUP (and the closely related function, HLOOKUP) are dependent on absolutely correct spelling. If anyone using the spreadsheet in the future misspells an input, the spreadsheet will return errors because the lookup function cannot find the misspelled word in the data table. But there is a way to ensure that anyone using the spreadsheet (including yourself) always enters the inputs correctly. We can use the Excel data validation tool to ensure that the inputs to the spreadsheet always match the existing data in any lookup tables.

Using the Excel Data Validation Tool

For this example we will be using the thermal expansion calculator created in a previous post.

First, select the input cell where you need to ensure that data is entered correctly.

In the ribbon, navigate to the “Data” tab, then select “Data Validation” from the Data Tools section.

From the “Allow” drop-down, choose the type of validation you would like to enforce.

In this example, we are dealing with text rather than numbers. Selections such as “whole number” or “decimal” won’t do us any good. For text input cells the best selection is usually “List”.

For ultra-convenience, let’s leave the option “In-cell dropdown” selected. That way if we click in the input cell, a drop-down box will appear and we don’t have to do any typing at all. We only need to select a value from the list.

Finally, select the source that will make up the list. In our case, it is the first column of the table in Sheet2. This table contains thermal expansion coefficients obtained via a web query. The first column contains the names of the materials.

After clicking “OK” to close the dialog box, we can see that there is an arrow next to the “Aluminum” (Cell C5). Clicking the arrow exposes a drop down containing all of the entries from the first column of the data table. Selecting (almost) any of these inputs updates the value in C5 as well as the calculated change in length in C9. (There is still an issue with some of the rows in the data table containing text values instead of numbers. We will correct that in a future post.)

You can override the drop-down selection and manually type a value into the cell. If it matches one of the chosen input values, everything works as expected. Otherwise, Excel returns an error. Just to try it out, I entered “Aluminium” (as our European friends like to pronounce it :)), and Excel returned the following:

Conclusion

As you’ve seen, you can use the Excel data validation tool to make your spreadsheets a little less prone to errors. This makes them much more valuable, as they can be passed around your organization and more easily used by others.Have you ever used data validation in an interesting way? Let me know in the comments.