Should you include these in your files?

Data Validation in Excel

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. 

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

1. Select the cell where the data validation is needed 
2. 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 

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. 

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. 

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.  

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. 

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:  

1. Use the INDIRECT() function to convert this text into an address. 

This now becomes =INDIRECT(“TableDiv[Division]” 

2. Use the Formulas > Name Manager to give the table reference a name to be used as a data validation criteria 

This method allows you to add a new entry at the bottom of the range without the need to manually expand the data validation through methods 1 or 3. 

This will serve as a guide for users by displaying a message as they select the cell or if the value specified is invalid. 

1. Select Data > Data Validation 
2. Go to the Input Message tab 
3. Tick the box to show an input message when the cell is selected 
4. Specify the message to be displayed 

1. Select Data > Data Validation 
2. Go to the Error Alert tab 
3. Tick the box to show an error message after invalid data is entered 

4. The default message shown if invalid data is entered looks like this 

5. To setup a custom alert, select a style and specify the message to be displayed 

In order to apply the data validation criteria to other rows: 

1. Highlight the area with existing data validation set up 

2. Press CTRL + C 

3. Highlight the rows where you want to paste the data validation criteria 

4. Right click > Paste Special 

5. Select Validation. 

From Excel Beginner to Professional

Learn Excel from Scratch

OR Improve Your Excel Skills to Become More Confident

Check out our best-selling course