Excel Data Validation: Uses, Steps, and Examples

Data visualization is vital to understanding who your intended customers are and how they interact with your company. But data is only helpful if it’s accurate. If multiple people on your team have access to business data you organize on Excel, you might want to limit what people can type into a spreadsheet.

With Excel’s Data Validation function, you can do just that. It’s a useful function that allows you to make sure the information in your Excel table stays relevant.

In this article, we’ll tell you what data validation is and what it does. We’ll also explore a few methods you can follow to use Excel data validation in your own worksheets. Use the jump links below to quickly move from section to section:

• What is Excel data validation?
• When to use data validation
• How to apply data validation in Excel
• How to copy the Excel data validation rule to another cell
• How to remove data validation

What is Excel data validation?

Data validation is a feature that lets you create rules for what information can be added to certain cells in your spreadsheet. With data validation, your information has a better chance of remaining valid and consistent.

‍

You can use data validation to make all sorts of rules. You might want to restrict input to a certain number of letters or digits, set up a predefined list of acceptable date inputs, or even create a range of approved numerical inputs.

You can also set up your data validation to show an error message if users type in unacceptable inputs. The system will flash a warning message when any incorrect inputs are entered, helping you quickly find incorrect inputs in validated cells so you can correct or remove them.

When to use data validation

Data validation has many uses, most of them dealing with restricting data and guiding users.

When you want to restrict data entry on specific cells

If a lot of people are collaborating on an important spreadsheet, one team member entering the wrong information can cause big problems. Data validation lets you decide what users can and can’t enter into a specific cell. This limits the risk that confused users will enter irrelevant information into your spreadsheet.

‍

When you want to warn a user if any out-of-range data is entered

With data validation, you can create a warning that pops up when a user tries to type in out-of-range data. The warning doesn’t stop a user from entering the wrong information, but it does alert them that their input doesn’t fit the guidelines.

This feature makes users aware that specific information belongs in certain cells while also giving you the freedom to type different data in cells if you need to.

‍

When you want to guide users to enter the same data format

You can use data validation to make sure users can only enter information in your intended format. Uniform formatting makes your spreadsheets easier to read and more professional.

Let’s say you have an Excel sheet of employee birthdays and need your staff to fill it out. However, you want everyone’s birthday to be in numerical form and formatted as month/day/year (00/00/0000). ‍

‍

When you want to validate an entry based on another cell

You can use data validation to create a rule for the data in one cell based on the information in other cells. This saves you the time of having to remake the same data validation rules over and over.

‍

When you want to restrict entries to a selection from a drop-down list

If you really want to limit the chance for users to make errors, you can provide a drop-down list of suitable input options for a cell on your worksheet. This feature can come in handy when you’re collecting data and can limit inputs to a few choices.

‍

Browse & Buy Data Analysis services on Project Catalog.

‍

How to apply data validation in Excel

Now that you know what data validation is and when to use it, you might want to know how to activate and customize it. You’re in luck; we cover all that below.

1. Select the cell(s) you want to create a rule for

Choose the cells you need to make a rule for. You can select a single cell by clicking your cursor inside of it, or select an entire row or column of cells by clicking the numbers on the side of your worksheet or the letters on top. You can also select all of the cells by clicking the Select All button at the top-left corner of your Excel sheet.

‍

2. Select the Data tab and click “Data Validation”

Make sure the Data tab is selected, then press the Data Validation button and select Data Validation from the drop-down menu to open the Data Validation dialog box. ‍

‍

3. On the Settings tab, under Allow, select an option

Choose the Settings tab. In the text box under Validation Criteria, click the data validation drop-down list  You’ll see a list of validation options to choose from. Choose the best one for your spreadsheet.

Here’s a list of the options and what they mean:

• Any value: If any value is selected, a user can type whatever information they want into a cell. Any value technically means that there’s no data validation.
• Whole number: Users can only enter whole numbers into selected cells. Decimals aren’t allowed. When you choose “whole number,” you’ll have the option to further limit user input by selecting a named range of acceptable whole numbers. For example, you might want users to choose a whole number greater than 21.
• Decimal: With “Decimal” selected, users can enter whole numbers and decimals into a cell. Just like with whole numbers, “Decimals” lets you choose a range of acceptable numbers.
• List: This function lets you create a predefined data validation list of acceptable responses for users to choose from. You can save your list to your worksheet as a range or under the Settings tab. Let’s say you have a spreadsheet of potential new hires. Your spreadsheet might ask something like, “Where did you hear about us?” The list would include a few job sites and social media pages for candidates to choose from.
• Date: With this function selected, users can only input dates from a specified range into a cell. For instance, if you need your company’s salespeople to select their best sales date in the third quarter of the fiscal year, you could set up a range of acceptable responses between July 1, 2023, and Sept. 30, 2023.
• Time: Only times can be entered into a cell. Let’s say you’re trying to figure out what time your staff members feel most productive during the workday. You might have them choose a time between 8 am and 5 pm.
• Text length: This option limits the number of characters or digits that an input can have. This can be useful when you need users to enter codes with a specific amount of characters or numbers. For example, if you need your clients in the United States to enter their ZIP codes, you could limit their answer to five numbers.
• Custom: This function lets you create your own formula for data validation. You can create all sorts of rules using custom formulas. For example, you might write a formula that will only accept user email addresses that are in all lowercase letters.

‍

4. Under Data, select a condition

Now that you’ve chosen how to sort your data, you’ll need to choose a condition for your input. This sets the range of inputs that you’ll accept. Click the drop-down menu under Data to see a list of conditions and select the best choice.

Not all validation criteria will have condition choices, though. For example, the Lists criteria don’t require a range of values because it has users choose from pre-written responses. ‍

‍

5. Set the other required values based on what you chose for Allow and Data

Next, you’ll need to enter any remaining data Excel needs to complete your rule. The type of data you’ll enter might be different depending on your criteria.

For example, if you choose Whole number as your criteria and Between as your condition, you’ll need to set Minimum and Maximum numbers for acceptable inputs.

‍

6. Select the Input Message tab and customize the message users will see when entering data

An input message pops up when a user clicks on a cell with a data validation rule. It helps users type in an appropriate input. If you’d like to add a custom input message, click the Input Message tab in the dialog box. Type in your input message and create a title for it.

‍

7. Select “Show input message when cell is selected”

For your message to be visible, you’ll need to select the source box next to the phrase Show input message when cell selected. The option may already be selected.

‍

8. Select the Error Alert tab to customize the error message and choose a Style

An error alert will pop up if a user enters an unacceptable input into your spreadsheet. Click the Error Alert tab and type in your custom alert. Like input messages, error alerts are optional.

‍

9. Click “OK”

When you’re happy with your error message, click OK to activate it.

‍

Note: If the user tries to enter a value that isn’t valid, an Error Alert appears with your customized message

If you decide to add an error alert to your data validation, there are a few alert types to choose from. Each will feature your customized message, but different error alerts with different styles give users distinct options for dealing with invalid inputs.

• Stop: The default error alert option completely prevents users from entering invalid data. It lets users select Retry to retype an acceptable input or Cancel to delete their input. ‍

‍

• Warning: A warning tells a user that their input isn’t valid, but it doesn’t make them change it. Users can select Yes to keep their entry as-is or No to change it.‍

‍

• Information: Like a warning, this alert only tells users that an input is invalid but doesn’t stop them from entering it. However, users only have to click OK to make it go away.

‍

How to copy the Excel data validation rule to another cell

Luckily, Excel doesn’t make you rewrite a validation rule every time you want to apply it to another cell. You can quickly transfer data validation from cell to cell using the Paste Special function.

1. Select the cell with the rule you want to copy. Either right-click the cell and choose Copy or press Ctrl + C (Windows) or Control + C (Mac).

‍

2. Click the cell or range of cells where you want to move the rule. Right-click the cell and choose Paste Special. You can select nonadjacent cells, rows, or columns by holding Ctrl (Windows) or Command (Mac) while you select them.

‍

3. Click the cell or range of cells where you want to move the rule. Right-click the cell and choose Paste Special. You can select nonadjacent cells, rows, or columns by holding Ctrl (Windows) or Command (Mac) while you select them.

‍

How to remove data validation

If you want to take validation off of a cell, there are two main methods. First, however, you might need to find which cells actually have data validation.

Make sure the Home tab is selected. Select the Editing Group drop-down list and locate Find & Select.‍

‍

In the drop-down list for Find & Select, click Data Validation.

‍

You should see all the cells with data validation selected on your worksheet.

‍

Here’s the first method for removing data validation:

1. Select the cells that have data validation rules you want to remove.
2. Make sure the Data tab is selected and click the Data Validation symbol.
3. When the dialog box comes up, choose the Data Validation Settings tab and click Clear All.

‍

Here’s the alternate method for removing data validation.

1. Find and select a cell with no values typed into it and no data validation rules applied to it. Use Ctrl + C (Windows) or Control + C (Mac) to copy the empty cell.
2. Choose the cells you’ve decided to remove data validation from.
3. Press the key shortcuts for Paste Special and Data Validation, Ctrl + Alt + V followed by N (Windows) or Control + Command + V (Mac). Click OK to apply the changes.

‍

Need help? Hire an independent data entry specialist

If you want your business to be a success, you need precise data. You have to know things like the buying habits of your target consumers and the amount of revenue your products are bringing in.

We’ve covered some basics here. But Excel is challenging to master—it even has its own certification. And if you need that kind of mastery in Excel, you might want to consider hiring an independent Microsoft Excel expert through Upwork.

To adapt to new technology, businesses are constantly searching for professionals with new tech skills and experience with modern data tools. We'll take the best tech talent from all over the planet and help you find the best match for your business.

‍