Last Updated: June 27, 2019
written by Adrienne Conley
You don’t always have control over the edits and additions that are made to a workbook you’ve created in Excel. But what if you could make sure that, at the very least, certain fields in your dataset are input consistently by anyone who works with your workbook after you? This is where the power of Excel’s Data Validation Tools comes in. The Data Validation Tools allow you to set rules for what can be input into the selected cells. Data validation reduces input errors and results in clean data that is compatible with pre-existing formulas and that can also be easily translated into reports. In this blog, we are going to focus on the most basic of the many Data Validation Tools, the List Tool.
With the List Tool, users are limited to a preset list of inputs for the applicable cells. For example, users adding or editing information to an expense account sheet can only select from a pre-determined list of categories to associate the transaction with.
Creating a Data Validation Drop-Down List
- Select the range of cells you want the drop-down menu to apply to. This could be one cell, a selection of cells, or the entire column. Note: Data validation settings will be applied to new entries to the table. In this example, I will be selecting the cells under the column “Category.”
- Go to the “Data” tab, look at the “Data Tools” section, and click “Data Validation.”
- In the “Allow” box select “list.” There are other ways of limiting the types of entries made in a cell, but for this example we are just using “list.” Make sure the checkbox “In-cell dropdown” is selected or the drop-down box will not appear next to the cell.
- In the “Source” field enter the values that you would like to appear as drop-down menu options separated by a comma.
- Optional: If desired, you can customize the pop-up box message that appears when someone tries to enter invalid data. To do this click the “Error Alert” tab and select the type of error message you would like to appear (Stop, Warning, Informational) and enter the title and error message you would like displayed. Similarly, you can customize an input message box by selecting the “Input Message” tab.
- Click “Ok” and now a drop down menu box will appear when a user enters information into those cells. Prepare yourself for clean, consistent data!
As mentioned, the above settings were only applied to new table entries. But what if we wanted to use this method for existing data, that may already be inconsistent? You can search for these entries by going back to the “Data” tab, clicking the drop-down list for “Data Validation” and then selecting “Circle Invalid Data.” The Data that doesn’t match the options you set in the list will now be circled in red and easily located to be fixed.
With all of this said however, there is one potential flaw in the data validation process. Users are able to copy and paste data into your worksheet, overriding the drop-down options. Keep this in mind when the edited sheet is returned to you and be sure to check the filtering options for the selected range of cells to see if there have been any options added that were not set in your original data validation.
The drop-down list data validation method is a simple way of streamlining the data entry process for a sheet and is especially useful if you are sharing your workbook with other users. Data validation can be set up in a matter of minutes with just a few simple clicks in Excel while saving hours of clean up time later on.