Why Use Data Validation in Excel
Data validation is a great way to ensure your data is entered accurately and consistently by adding a validation check – by restricting the type and/or content of data that can be entered – as well as an optional input message that appears when the user selects the cell. If you are using Excel as a small database, this can save a lot of time tracking down miskeyed data, especially when typing names, labels, etc. Data validation types can restrict dates, numbers, text length, or entire values via lists, which can be linked to a range of cells.
It can also expedite typing in items from a list when there is a defined list of values you want users to select from. Combining data validation with dynamic named ranges can give you a list that grows over time. The video below and the accompanying example files show this in action. When using data validation lists, you can click the Alt+Down Arrow to show the values. The optional input message can be a helpful and non-cluttered way to offer instructions on how to fill in the required data inputs.
The screenshots below show a data validation input message and drop-down list.
How to Use Data Validation in Excel
To add data validation to the selected cells, go to the Data ribbon tab, and click the Data Validation button. On the prompt box, there are three tabs. The first one contains the type settings, and the second and third tabs contain the optional input and error messages you can define, respectively. You simply select the type from the “Allow:” list, then define the restrictions. I tend to use the list type the most. You can manaully enter the values, or reference a cell range, which again can be set to a dynamic named range. When you do this, you can add to the list without having to redefine the range as the named range will grow as it finds other values in the list. The XLEV8 Add Validation From Named Range macro helps apply this quickly. It even defaults to the named range if it’s copied to the clipboard! See the video below, which covers this.
Recent Comments