Should you include these in your files?

Data Validation in Excel

Excel’s Data Validation feature ensures that the data collected is clean and to minimize the errors and hence the time it takes to correct them.

This is especially helpful when multiple people input data into the spreadsheet.

Types of Data Validation

By default, data validation allows any value to be placed in the cells.

There are, however, various validation criteria options:

Whole number
Decimal
Date
Time
Text length
Values from a list
Custom (formula-based)

For the first five options enables you to specify ranges or specific values.

The last option allows you to add custom criteria for more flexibility.

Adding Data Validation

These are the general steps in creating a data validation criteria:

Select the cell where the data validation is needed
Go to Data > Data Validation

3. Select the type of validation needed

4. Define the validation or specify a source

To expound further, let’s take a report that requires users to specify a division, project start date, project number, company code and project description.

Below are the ways to setup data validation this scenario:

Cell A5: Select a division from a list (Game, Productivity, Utility)
- Validation criteria type: List
- Source: For static options, use a comma to separate the choices, “Game, Productivity, Utility”

Cell B5: Specify a project start date in 2018 that is after the current date
- Validation criteria type: Date
- Data: greater than

Start date: =TODAY()

Cell C5: Project number should be four digits within a range (1000 to 2000)
- Validation criteria type: Whole number
- Data: between
- Minimum: 1000
- Maximum: 2000

Cell D5: Specify a company code that is at least 5 characters
- Validation criteria type: Text length
- Data: equal to
- Length: 5

Cell E5: Project description that is sufficiently long (more than 20 characters)
- Validation criteria type: Text length
- Data: greater than
- Length: 20

Using the custom type

If there are additional requirements for a cell that are not in the scope of the other data validation criteria types, the Custom type can be used.

An example is when the Company Code field (column D) requires a text for the first character and the remaining characters to be numbers.

This can be programmed using a formula.

Dynamic Data Validation

In cases where the options change often, using a dynamic data validation is ideal.

To do this, place the options in a separate sheet or table and use cell references to point to it as the source.

Any changes made to this data set will automatically be reflected in the data validation criteria.

Setting up the options

Place the options you want to use in your data validation in a separate table.

One way is to have it in a separate tab.

Setting up the data validation source

The same steps are carried out with only a slight modification to the data validation criteria Source.

Validation criteria: List
Source: =MASTER$A$4:$A$6

Note that adding a new row below this range will not be automatically captured unless it is inserted within the existing range.

Adding new options to the dropdown

There are various ways to do this:

a) Expand the data validation reference to include the new row

This means that if the original range is MASTER$A$4:$A$6, this should be manually changed to MASTER$A$4:$A$7 to include the newly added row.

b) Insert a row within the existing specified range

Since the range originally specified was MASTER$A$4:$A$6, insert a row between 4 and 6.

c) Drag the new row into the range using the SHIFT key

Add a new entry at the bottom of the range. If the range is MASTER$A$4:$A$7, add the entry in cell A8.

Click on the new row and hold the SHIFT key as you drag cell A8 into the original range.

d) Using an expandable cell reference

Convert the range into an official Excel table by clicking anywhere inside the data set and press CTRL + T.

Change formatting based on preference under Design > Table Styles.

Rename the table under Design > Table Name. By default, it is called Table1. In this example, it is renamed to TableDiv.

To refer to this range, the syntax is =Table_name[header_name]. In this example, is it TableDiv[Division]. This allows the entire range to expand when new rows are added.
Go back to the cell where the data validation is needed and change the source. Since data validation is unable to process table references, there are two workaround for this: