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.

Practice

1. What is the main purpose of using Data Validation in Excel?

easy

A. To restrict the type of data users can enter in a cell

B. To format cells with colors and fonts

C. To create charts from data

D. To sort data alphabetically

Solution

1. Step 1: Understand Data Validation purpose

   Data Validation is used to control what data can be entered in cells to avoid errors.

2. Step 2: Compare options with purpose

   Only To restrict the type of data users can enter in a cell describes restricting data entry, which matches Data Validation's purpose.

3. Final Answer:

   To restrict the type of data users can enter in a cell -> Option A

4. Quick Check:

   Data Validation = Restrict data entry [OK]

Hint: Data Validation controls input type, not formatting or sorting [OK]

Common Mistakes:

• Confusing Data Validation with cell formatting
• Thinking it creates charts
• Assuming it sorts data

2. Which of the following is the correct way to set a data validation rule that only allows whole numbers between 1 and 10?

easy

A. Allow: List; Source: 1,2,3,4,5,6,7,8,9,10

B. Allow: Whole number; Data: between; Minimum: 1; Maximum: 10

C. Allow: Decimal; Data: greater than; Minimum: 1; Maximum: 10

D. Allow: Text length; Data: less than; Minimum: 1; Maximum: 10

Solution

1. Step 1: Identify rule for whole numbers 1 to 10

   The rule must allow whole numbers only, between 1 and 10 inclusive.

2. Step 2: Match options to rule

   Allow: Whole number; Data: between; Minimum: 1; Maximum: 10 correctly sets Allow to Whole number and Data to between with min 1 and max 10.

3. Final Answer:

   Allow: Whole number; Data: between; Minimum: 1; Maximum: 10 -> Option B

4. Quick Check:

   Whole number between 1 and 10 = Allow: Whole number; Data: between; Minimum: 1; Maximum: 10 [OK]

Hint: Whole numbers need 'Allow: Whole number' with min and max [OK]

Common Mistakes:

• Choosing Decimal instead of Whole number
• Using List instead of number range
• Confusing Text length with number validation

3. If you apply a data validation rule with the formula =ISNUMBER(A1) on cell A1, what will happen when you enter the text "Hello" in A1?

medium

A. The entry will be rejected with an error message

B. The entry will be accepted without error

C. The cell will automatically convert "Hello" to a number

D. The cell will be cleared automatically

Solution

1. Step 1: Understand the formula =ISNUMBER(A1)

   This formula returns TRUE if A1 contains a number, FALSE otherwise.

2. Step 2: Entering "Hello" in A1

   "Hello" is text, so ISNUMBER returns FALSE, violating the validation rule.

3. Final Answer:

   The entry will be rejected with an error message -> Option A

4. Quick Check:

   ISNUMBER rejects text input = The entry will be rejected with an error message [OK]

Hint: ISNUMBER allows only numbers; text causes rejection [OK]

Common Mistakes:

• Thinking text is accepted by ISNUMBER
• Assuming automatic conversion to number
• Believing the cell clears on invalid input

4. You set a data validation rule with the formula =AND(A1>=1, A1<=5) but users can still enter 10 without error. What is the most likely mistake?

medium

A. The formula was entered as text, not as a formula

B. The cell A1 is formatted as Text, not Number

C. The data validation was applied to the wrong cell

D. The formula uses incorrect logical operators

Solution

1. Step 1: Analyze why validation fails

   If users enter 10 and no error shows, validation is not working on that cell.

2. Step 2: Check common causes

   Most likely, the validation rule was applied to a different cell, not the one users edit.

3. Final Answer:

   The data validation was applied to the wrong cell -> Option C

4. Quick Check:

   Wrong cell validation = The data validation was applied to the wrong cell [OK]

Hint: Always confirm validation applies to correct cells [OK]

Common Mistakes:

• Entering formula as text disables validation
• Ignoring cell format effects
• Assuming formula logic is wrong without checking range

5. You want to create a data validation rule that allows only dates in the current year. Which custom formula should you use?

hard

A. =AND(ISNUMBER(A1), YEAR(A1)=TODAY())

B. =AND(ISDATE(A1), YEAR(A1)=YEAR(TODAY()))

C. =AND(ISNUMBER(A1), MONTH(A1)=YEAR(TODAY()))

D. =AND(ISNUMBER(A1), YEAR(A1)=YEAR(TODAY()))

Solution

1. Step 1: Understand the goal

   Allow only dates in the current year, so check if A1 is a number (dates are numbers) and year matches current year.

2. Step 2: Evaluate options

   =AND(ISNUMBER(A1), YEAR(A1)=YEAR(TODAY())) uses ISNUMBER (correct for dates), and YEAR(A1)=YEAR(TODAY()) which matches current year.

3. Final Answer:

   =AND(ISNUMBER(A1), YEAR(A1)=YEAR(TODAY())) -> Option D

4. Quick Check:

   Check date number and year match = =AND(ISNUMBER(A1), YEAR(A1)=YEAR(TODAY())) [OK]

Hint: Use ISNUMBER and YEAR to validate dates in current year [OK]

Common Mistakes:

• Using ISDATE which is not an Excel function
• Comparing MONTH to YEAR
• Comparing YEAR to full date instead of year only