Data validation rules in Excel - Step-by-Step Guide

Introduction

Data validation rules help you control what users can type into cells. This keeps your data clean and avoids mistakes by allowing only certain values or formats.

When you want to allow only numbers between 1 and 100 in a score column

When you need users to pick a choice from a list like Yes, No, or Maybe

When you want to prevent typing dates outside a specific range

When you want to make sure email addresses follow a pattern

When you want to stop users from leaving a cell blank

Steps

Step 1: Select the cells where you want to apply the rule

- Excel worksheet

The selected cells are highlighted

💡 You can select a whole column or a specific range

Step 2: Click the Data tab

- Ribbon at the top of Excel

Data tab options appear

Step 3: Click Data Validation in the Data Tools group

- Data tab

The Data Validation dialog box opens

Step 4: In the Settings tab, click the Allow dropdown

- Data Validation dialog box

A list of validation types appears

Step 5: Select the type of validation you want, for example, Whole Number or List

- Allow dropdown in Data Validation dialog

Options for the selected type appear below

Step 6: Set the specific rule details, like minimum and maximum numbers or the list items separated by commas

- Data Validation dialog box

The rule is defined for the selected cells

Step 7: Click OK

- Data Validation dialog box

The rule is applied and cells will only accept allowed values

Before vs After

Before

Cells in column B accept any text or numbers without restriction

After

Cells in column B only accept whole numbers between 1 and 10; typing anything else shows an error

Settings Reference

Allow

📍 Data Validation dialog box > Settings tab

Choose the type of data allowed in the cells

Default: Any Value

Data

📍 Data Validation dialog box > Settings tab

Set the condition for the allowed data range or values

Default: between

Source

📍 Data Validation dialog box > Settings tab (only for List type)

Define the list of allowed values for selection

Ignore blank

📍 Data Validation dialog box > Settings tab

Allow or disallow blank cells

Default: Checked

Input Message

📍 Data Validation dialog box > Input Message tab

Show a message when the cell is selected to guide the user

Error Alert

📍 Data Validation dialog box > Error Alert tab

Choose how Excel responds when invalid data is entered

Default: Stop

Common Mistakes

Entering list items in the Source box without commas

Excel treats the whole text as one item, so the dropdown shows only one choice

Separate each list item with a comma, like Yes,No,Maybe

Not selecting the correct cells before applying validation

The rule applies only to selected cells, so other cells remain unrestricted

Select all cells you want to restrict before opening Data Validation

Leaving Ignore blank checked when blanks should not be allowed

Users can leave cells empty even if you want data entered

Uncheck Ignore blank to force users to enter data

Summary

Data validation rules control what users can type in cells to keep data clean.

You set rules like number ranges, lists, or dates using the Data Validation dialog.

Remember to select the right cells and set error alerts to guide users.