The Ultimate Guide to Data Validation in Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Data validation is an Excel feature that lets you control what users enter into a cell.

Most users know about data validation as a way to create dropdowns.

However, it can do more than that. With data validation, you can literally make users enter data only in your specified format.

In this article, we’ll show you what you can do with data validation and how to do them.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get started! 😊

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

What is data validation?

Data validation is an Excel feature that allows you to restrict the type of data or values users can enter in a cell.

To be precise, you’re able to set up “validation” rules on cells and decide what kind of data the cell would accept.

In addition, you can set up errors whenever the data entered is against the validation rules you set. There’s even an option where you can circle invalid data on the worksheet.

Kasper Langmann, Co-founder of Spreadsheeto

We’ll show you below the basic options and settings in using the feature.

Data Validation Dialogue Box

Using this feature is simple.

To create and apply a validation rule, simply select the cell(s) and click ‘Data’ from the tab list.

data from the tab list

Then, click ‘Data Validation’ from the ‘Data Tools’ group in the ‘Data’ tab.

data validation icon under the data tab

You’ll immediately see the data validation dialogue box.

data validation dialogue box

Basically, there are seven (7) types of data you can set:

  • Whole number
  • Decimal
  • List
  • Date
  • Time
  • Text length
  • Custom

Depending on the chosen data, there are various criteria from which you can select from:

  • between
  • not between
  • equal to
  • not equal to
  • greater than
  • less than
  • greater than or equal to
  • less than or equal to

Once you choose a criterion, you’ll see additional options you have to fill out:

  • between
  • not between
  • equal to
  • not equal to
  • greater than
  • less than
  • greater than or equal to
  • less than or equal to

Once the entry violates the validation rule, the user will receive a warning similar to the one below:

default error alert in data validation

The sections below will show you how to create a validation rule depending on the type of data as well as data validation examples.

Kasper Langmann, Co-founder of Spreadsheeto

How to allow whole numbers and decimals only

To allow only whole numbers and decimals:

  • Select the cell(s) to add the rule
  • Open the data validation dialogue box
  • Select either data type under ‘Allow’
  • Set the criteria under ‘Data’
  • Provide the additional information needed

Example:

An employee roster where ID numbers are between 100000 and 999999

validate whole numbers or decimals only

How to validate dates and time

Validation dates and time is easy. What you have to watch out, however, is the criteria.

To validate dates and time:

  • Select the cell(s) to add the validation
  • Open the data validation dialogue box
  • Select ‘Date’ or ‘Time’ under ‘Allow’
  • Choose the appropriate criteria (there are lots of them; choose the best that meets your needs)
  • Supply the additional information needed (preferably, write the dates in the worksheet and refer to them like in the example)

Example:

On an employee timesheet, a user can only input a date that belongs to the current or specified week (September 2 – 8, 2019) on the timesheet

validate dates and time

How to create text length-based rule

You can also set a rule where the cell will only allow texts within the specified character length.

Here are the steps:

  • Select the cell(s) to apply the rule
  • Open the data validation dialogue box
  • Select ‘Text length’ under ‘Allow’
  • Select the appropriate criteria from the ‘Data’ option
  • Provide the necessary details

Although this type is called ‘text length’, users can enter both numeric and non-numeric data as long as the character count is within the specified range.

Kasper Langmann, Co-founder of Spreadsheeto

Example:

On an employee database, users can only input usernames within 3-10 characters

validate entry based on text-length

Other text-related data validation

Aside from the specific data types, it’s also possible to create custom data validation rules with the use of functions.

How to allow only text or numbers

As of now, there is no direct way to allow text or numbers only in a cell aside from using special functions (‘ISTEXT’ and ‘ISNUMBER’) with data validation.

To do this, follow these steps:

  • Open the data validation dialogue box
  • Choose ‘Custom’ under ‘Allow’
  • Enter the ‘ISTEXT’ function to allow text only or ‘ISNUMBER’ to allow numbers only
  • Don’t forget to integrate the range or cell reference of the cells you’d like to apply the rule into

The syntax of the ‘ISTEXT’ function is ‘=ISTEXT (value)’ while the ‘ISNUMBER’s syntax is ‘=ISNUMBER (value)’.

Kasper Langmann, Co-founder of Spreadsheeto

Example:

Allow only texts for the ‘First Name’ column of the employee roster

validate texts or numbers only

How to allow entries only with specific texts

This one needs a combination of two functions — ‘ISNUMBER’ and ‘FIND’/’SEARCH’.

Both ‘FIND’ and ‘SEARCH’ functions will search the entered text for the specified characters. When the specified text or characters are found, the function will return TRUE. If not, it will return an error.

If you’re wondering which to use, the ‘SEARCH’ function isn’t character-case sensitive so it’s good for general use. But if you need a case-sensitive rule, use the ‘FIND’ function.

Kasper Langmann, Co-founder of Spreadsheeto

The ‘ISNUMBER’ function is inserted in case the characters you’re looking for are numeric in nature. If the numeric values are found, the function returns TRUE. If not, you’ll see an error.

For the formula, all we have to do is write the ‘FIND/’SEARCH’ syntax on the ISNUMBER’s parameter (syntax is shown in the previous section).

Case-insensitive formula:

=ISNUMBER(SEARCH(text, cell))

Case-sensitive formula:

=ISNUMBER(FIND(text, cell))

To apply such a rule:

  • Open the data validation dialogue box
  • Choose ‘Custom’ under ‘Allow’
  • On the ‘Formula’ input, enter either of the formulas shown above

Example:

The employees’ ID number on the roster should have “AC”.

validate entries with specific texts

How to validate entries with specific characters at the beginning

But what if the ‘certain’ characters that need to be in the entry should be at the beginning of the string?

If you were able to grasp the previous one, this one should be a piece of cake.

All you need to use in this rule is the ‘COUNTIF’ function and working knowledge about Excel wildcards.

The syntax of ‘COUNTIF’ is ‘=COUNTIF (range, criteria)’.

To use the function to validate characters at the beginning of the entry, use the asterisk (*) wildcard.

=COUNTIF(cell(s),”text*”)

The asterisk wildcard is the most general of all wildcards. It can take on the value of any number of characters.

Kasper Langmann, Co-founder of Spreadsheeto

To make a rule for this purpose:

  • Open the data validation dialogue box
  • Choose ‘Custom’ under ‘Allow’
  • On the ‘Formula’ input, enter either of the formula described above

Example:

The “AC” or “ac” on the employees’ ID numbers should be in the front.

validate with specific characters at the beginning

But what if the number of employees increased dramatically and you decide to add “BC” as one of the prefixes?

All you need to do is add another instance of ‘COUNTIF’ by using a plus (+) sign.

validate with more than specific texts at the beginning

How about if the prefixes should be case-sensitive? Like “ac” is not accepted, only “AC”?

To resolve this, you need to use a combination of the ‘EXACT’ and ‘LEFT’ functions.

Basically, the formula will look like this:

=EXACT(LEFT(cell(s), number_of_chars), text)

validate specific case-sensitive texts

Other date and time-related data validation

With the ‘Custom’ data type, you can also set up dates and time-related validation rules.

How to allow weekdays only

It’s possible to set up a rule that only allows users to enter a ‘weekday’ date with the use of Excel’s ‘WEEKDAY’ function.

This function takes a date and returns a number between 1-7 which represents the day of the week.

The syntax of ‘WEEKDAY’ is ‘=WEEKDAY (serial_number, [return_type])’ where ‘serial_number’ is the date.

The parameter ‘return_type’ is optional. But this is an important matter if we will use this function in data validation.

By default, the ‘return_type’ value is set to 1, which means in representing the days of the week, 1 is Sunday while 7 is Monday.

To make things easier, we have to change the ‘return_type’ value to 2 which sets 1 as Monday and 7 as Sunday.

That means weekdays are now 1-5 and weekends are 6-7.

Kasper Langmann, Co-founder of Spreadsheeto

So in setting up a rule to allow weekdays only, we can simply manipulate the formula to accept dates below ‘6’ or Saturday:

=WEEKDAY(cell(s),2)<6

  • Open the data validation dialogue box
  • Choose ‘Custom’ under ‘Allow’
  • On the ‘Formula’ input, enter either of the formula described above

Example:

The deadline for the school’s project should only be during school days, which are weekdays.

validate weekdays only

How to allow weekends only

If we can set up a rule to allow weekdays only using the ‘WEEKDAY’ function as explained above, it’s only natural to be able to set up a rule to allow weekends only.

This time, instead of “<6”, let’s use “>5”. 5 is Friday when the ‘return_type’ value is set to 2. Greater than Friday points to Saturday and Sunday.

=WEEKDAY(cell(s),2)>5

  • Open the data validation dialogue box
  • Choose ‘Custom’ under ‘Allow’
  • On the ‘Formula’ input, enter either of the formula described above

Example:

The deadline for a graduate school’s project should only be during school days, which are usually weekends.

validate weekends only

Setup a rule based on today’s date

What if you need to validate dates that are based on today’s date?

Simple. All you need to do is use the ‘TODAY’ function with data validation.

Its syntax is ‘=TODAY ()’ and you can manipulate it by addition or subtraction.

To use it with data validation:

  • Select the cell(s) to apply the rule
  • Open the data validation dialogue box
  • Select ‘Date’ under Allow’
  • Select ‘between’ under ‘Data’
  • On the ‘Start date’, enter the syntax of ‘TODAY’
  • On the ‘End date’, enter the desired date based on today’s date

Example:

The deadline is due 7 days starting today.

validate based on today's date

Setup a rule based on the current time

It’s possible to validate the time entered based on the current time.

In most cases, this is done to prevent cheating by the user like in cases when an employee enters a time earlier than the current one to prevent being marked as late.

Kasper Langmann, Co-founder of Spreadsheeto

The functions that can be used for this is the ‘NOW’ and ‘TIME’ functions along with ‘HOUR’, ‘MINUTE’, and ‘SECOND’.

To validate both date and time based on the current date and time, use:

=NOW()

To validate time based on the current time, use:

=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

To create a data validation rule:

  • Select the cell(s) to apply the rule
  • Open the data validation dialogue box
  • Select ‘Time’ under ‘Allow’
  • Select the appropriate criterion under ‘Data’ (greater than, less than, or their variation with the ‘or equal to’)
  • On the ‘Start date’, enter either of the formulas listed above

Example:

On an employee timecard, only allow time that’s greater than or equal to the current time.

validate based on current time

How to set a rule against duplicate entries

One important use of data validation is to avoid duplicates.

To do this, you’ll need the ‘COUNTIF’ function to identify duplicates.

It’s syntax is:

=COUNTIF(range, criteria)

When using data validation, the ‘criteria’ is the top-most cell of the range. And then, you need to add a snippet at the end to make sure that each count is an individual value:

=COUNTIF(range, topMostCell)<=1

What happens is that the rule sees to it that the values added on the range are unique. If the entry already exists in the range, the function will return FALSE showing an error to the user.

Kasper Langmann, Co-founder of Spreadsheeto

For this rule to work, you have to remember 2 things:

  • Use absolute reference for the ‘range’ parameter to lock the range
  • Use relative reference for the ‘topMostCell’

To create a data validation rule:

  • Open the data validation dialogue box
  • Select ‘Custom’ under ‘Allow’
  • On the ‘Formula’ text input, write the formula listed above with the appropriate values

Example:

To ensure an employee’s information isn’t repeated on the roster

validate unique entries

Edit existing data validation rules

To edit or change an existing data validation rule on a cell, simply follow these steps:

  • Select the cell with the rule
  • Open the data validation dialogue box
  • Check the ‘Apply these changes to all other cells with the same settings’ box and hit ‘OK’.
how to edit data validation

Copying the validation rule from another cell

If the cell with the validation rule has no entry yet, you can directly copy and paste it into other cells.

The alternative is copying the cell and pasting it as special.

After you copy (‘Ctrl’ + ‘C’), right-click on the target cell(s) and click ‘Paste Special’.

right clicking for paste special

On the window, select ‘Validation’ and click ‘OK’.

validation on paste special window

Delete a validation rule on Excel

If you tried pressing ‘Backspace’ or ‘Delete’ on a cell with a data validation rule, you must’ve noticed you can only delete the entry and not the rule.

To delete a data validation rule on a cell or group of cells, select the cell(s) and open the data validation dialogue box.

On the box, press the ‘Clear All’ box on the bottom-left part and click ‘OK’.

clear all data validation checkbox

This action immediately takes effect.

Delete all data validation rules on the worksheet

It’s also possible to delete all the data validation rules on all cells on the worksheet.

The first thing you have to do is find all the cells with data validation.

Fortunately, there’s no need for you to click on them individually. You can simply use the ‘Find & Select’ tool.

To do so, head over to the default tab, ‘Home’, from the tab list on the Ribbon.

home tab on tab list

On the right-most part, click the ‘Find & Select’ icon and choose ‘Data Validation’ from the dropdown.

data validation on find and select tool

All cells with data validation in it will be selected.

all cells get selected from find and select tool

Once you do that, simply follow the steps described in the previous section:

  • Open the data validation dialogue box
  • Press ‘Clear All’
  • Click ‘OK’

After that, all the data validation within the worksheet will be erased.

How to add an input message

Once you enable and add an input message, it shows up whenever a user selects a cell where the input message has been enabled.

sample input message

If you wish to add your own input message:

  • Select the cell(s) you’d like to add an input message
  • Open the data validation dialogue box
  • Click the ‘Input Message’ tab
  • Check if the input message is enabled (by default, it’s enabled)
  • Write the title and the body of your message (title will be in the bold format on the message)
  • Click ‘OK’
settings for input message

How to add an error alert

First off, there are three (3) styles or types of error messages:

1. Stop – the strictest alert; prevents invalid data; the ‘default’ error alert

default error alert in data validation

2. Warning – warns the user that the data entered is invalid but doesn’t prevent it

default warning on data validation

3. Information – only informs about the user about the entered invalid data

default information message on data validation

Excel allows you to write your own alert message.

Kasper Langmann, Co-founder of Spreadsheeto

To customize your own alert:

  • Select the cell(s) to add the alert
  • Open the data validation dialogue box
  • Go to the ‘Error Alert’ tab
  • Write the title and body of your error message (title will be the error alert window’s name)
how to set up customized error messages

Once a user enters a data that’s against the rule, a message like the one below will pop up:

sample customized error message

What to do if the data validation doesn’t work?

Formula-based data validation rules are prone to error.

If your data validation rules don’t work, check for the following:

  • Formula: Using a formula in data validation could be tricky. Check for any mistakes in the formula.
  • References: Make sure you’re using absolute references for cell-dependent formulas. If not, the rule will only work correctly for the first cell.
  • Ignore blank: By default, rules ignore blank. Watch out for this since formula-based rules that use references could potentially allow any values, even invalid ones.

Wrapping things up…

One of the advantages of data validation is that it minimizes errors. Users won’t be able to enter data that’s against the validation rule.

If you’re having trouble learning how to use data validation, download the free exercise file and see if you can reverse-engineer what’s being done on some of the cells. 😊

Kasper Langmann, Co-founder of Spreadsheeto