Data Validation and Conditional Formatting with Practical Examples

Incorrect data entry is one of the main reasons Excel reports become unreliable. A spelling difference, missing value, invalid date or duplicate ID can affect formulas, PivotTables, lookups and dashboards.

Data Validation controls what users can enter, while Conditional Formatting highlights important values automatically. In this chapter, you will create dropdown lists, dependent dropdowns, input messages, validation rules, duplicate checks, overdue alerts, KPI indicators and formula-based formatting.

Learning Objectives

After completing this chapter, you should be able to:

• Restrict data entry using validation rules.
• Create standard and dynamic dropdown lists.
• Create dependent dropdown lists.
• Display input instructions and error messages.
• Validate numbers, dates, text and unique IDs.
• Identify duplicates, blanks and invalid records.
• Highlight complete rows using formulas.
• Create overdue and deadline alerts.
• Apply data bars, colour scales and icon sets.
• Build a controlled employee data-entry system.

1. What Is Data Validation?

Data Validation is an Excel feature that controls the type of information users can enter into a cell. It helps maintain accurate and consistent datasets.

Common Uses of Data Validation

• Create dropdown lists.
• Allow only whole numbers.
• Restrict percentages to a selected range.
• Prevent future or past dates.
• Limit the length of an ID or phone number.
• Prevent duplicate values.
• Allow entries only when related cells are complete.
• Display instructions before data entry.
• Show an error message for invalid data.

2. Types of Data Validation

Validation type	Purpose
Whole Number	Allows only integers within selected limits
Decimal	Allows decimal values within selected limits
List	Creates a dropdown list
Date	Restricts entries to a valid date range
Time	Restricts entries to a time range
Text Length	Controls the number of entered characters
Custom	Uses a formula to validate the entry

3. Create the Employee Data-Entry Sheet

Create a worksheet named Employee_Entry with the following columns:

EmployeeID	EmployeeName	Department	Designation	JoiningDate	Salary	Attendance	Status	Email	Phone
EMP-1001	Rahul Sharma	Sales	Sales Executive	10-Jun-2024	35000	94%	Active	rahul@example.com	9876543210
EMP-1002	Priya Singh	Finance	Accountant	15-Jan-2023	42000	88%	Active	priya@example.com	9123456789
EMP-1003	Imran Khan	Marketing	Marketing Executive	01-Mar-2025	38000	76%	Probation	imran@example.com	9000011111

Convert the range into an Excel Table and name it EmployeeData.

4. Creating a Simple Dropdown List

Create a dropdown for the Status column containing Active, Inactive, Probation and Resigned.

Steps

1. Select the Status cells.
2. Go to Data → Data Validation.
3. Under Allow, select List.
4. Enter the following values in the Source box:

Active,Inactive,Probation,Resigned

5. Ensure In-cell dropdown is selected.
6. Click OK.

Benefits of a Dropdown

• Prevents spelling differences
• Speeds up data entry
• Creates consistent categories
• Improves filtering and reporting
• Reduces manual typing

5. Creating a Dropdown from a Cell Range

Create a worksheet named Support_Lists and enter the following departments in A2:A6:

Sales
Finance
Marketing
Human Resources
Operations

Create the Department Dropdown

1. Select the Department entry cells.
2. Open Data Validation.
3. Select List.
4. Enter the following source:

=Support_Lists!$A$2:$A$6

If Excel does not accept a direct reference to another worksheet in your setup, create a named range and use that name as the validation source.

6. Creating a Named Dropdown Source

Create a Named Range

1. Select Support_Lists!A2:A6.
2. Click the Name Box.
3. Enter DepartmentList.
4. Press Enter.

Use the Named Range

=DepartmentList

Enter this name in the Data Validation Source box.

7. Creating a Dynamic Dropdown List

A dynamic dropdown expands when new values are added.

Method 1: Use an Excel Table

Convert the support list into an Excel Table named DepartmentTable. Create a named range with:

=DepartmentTable[Department]

Use the defined name as the Data Validation source.

Method 2: Use UNIQUE and SORT

Enter this formula in Support_Lists!D2:

=SORT(
UNIQUE(
EmployeeData[Department]
)
)

The formula creates a sorted, unique and automatically expanding list.

Use the Spill Range

=Support_Lists!$D$2#

If direct spill references are not accepted in Data Validation, define a name such as DynamicDepartments referring to:

=Support_Lists!$D$2#

Then use this validation source:

=DynamicDepartments

8. Creating Dependent Dropdown Lists

A dependent dropdown changes according to another selection. For example, the available Designations should depend on the selected Department.

Department and Designation Structure

Department	Designation
Sales	Sales Executive
Sales	Sales Manager
Finance	Accountant
Finance	Finance Manager
Marketing	Marketing Executive
Marketing	SEO Specialist
Human Resources	HR Executive
Human Resources	HR Manager
Operations	Operations Executive
Operations	Operations Manager

Convert this list into a table named DesignationData.

Generate Designations for the Selected Department

Suppose the Department selection is stored in C2. Enter this formula in Support_Lists!F2:

=SORT(
UNIQUE(
FILTER(
DesignationData[Designation],
DesignationData[Department]=Employee_Entry!C2,
"No Designations Found"
)
)
)

Create a Name for the Spill Range

=Support_Lists!$F$2#

Name this range DesignationList and use the following source for the Designation dropdown:

=DesignationList

9. Traditional Dependent Dropdown with INDIRECT

Older Excel versions can create dependent dropdowns using named ranges and INDIRECT.

Steps

1. Create separate designation lists for every department.
2. Name each range according to its department.
3. Use the Department dropdown as the first selection.
4. Use the following formula as the Designation dropdown source:

=INDIRECT(SUBSTITUTE(C2," ","_"))

If C2 contains Human Resources, SUBSTITUTE converts it to Human_Resources, which can match the named range.

Important Limitation

INDIRECT is volatile and can make large workbooks slower. The FILTER-based method is usually more flexible in modern Excel.

10. Restricting Whole Numbers

Suppose Quantity must be between 1 and 100.

1. Select the Quantity cells.
2. Open Data Validation.
3. Select Whole Number.
4. Select Between.
5. Set Minimum to 1.
6. Set Maximum to 100.

Useful Whole-Number Rules

• Quantity between 1 and 1,000
• Marks between 0 and 100
• Experience between 0 and 50 years
• Number of installments between 1 and 60

11. Restricting Decimal Values

Suppose Attendance must be entered as a percentage between 0% and 100%.

1. Select the Attendance cells.
2. Select Decimal.
3. Choose Between.
4. Set Minimum to 0.
5. Set Maximum to 1.

Because Excel stores 100% as 1, the maximum value should be 1.

12. Restricting Date Entry

Allow Joining Dates Up to Today

1. Select the Joining Date cells.
2. Choose Date.
3. Select Between.
4. Set the Start Date to 01-Jan-1990.
5. Set the End Date to:

=TODAY()

Allow Dates in the Current Year

Start Date:
=DATE(YEAR(TODAY()),1,1)

End Date:
=DATE(YEAR(TODAY()),12,31)

Allow Only Future Dates

Use Custom validation:

=A2>TODAY()

Allow Only Working Days

=WEEKDAY(A2,2)<=5

This prevents entries falling on Saturday or Sunday.

13. Restricting Text Length

Allow Exactly 10 Characters

1. Select the required cells.
2. Choose Text Length.
3. Select Equal to.
4. Enter 10.

Possible Uses

• Ten-digit mobile number
• Fixed-length employee code
• Account number
• Registration number
• Postal code

14. Using Custom Data Validation

Custom validation uses a formula that returns TRUE for an accepted entry and FALSE for a rejected entry.

Allow Only Unique Employee IDs

Select the Employee ID range beginning with A2 and use:

=COUNTIF($A:$A,A2)=1

Allow an ID Beginning with EMP-

=LEFT(A2,4)="EMP-"

Validate Prefix and Length Together

=AND(
LEFT(A2,4)="EMP-",
LEN(A2)=8
)

Adjust the expected length according to your actual ID format.

Allow Only Text

=ISTEXT(B2)

Allow Only Numbers

=ISNUMBER(F2)

Allow a Valid 10-Digit Phone Number

=AND(
LEN(J2)=10,
ISNUMBER(--J2)
)

Basic Email Validation

=AND(
ISNUMBER(SEARCH("@",I2)),
ISNUMBER(SEARCH(".",I2)),
SEARCH("@",I2)>1,
SEARCH(".",I2)>SEARCH("@",I2)+1
)

This checks basic email structure, but it is not complete internet-level email validation.

15. Allow Entry Only When Another Cell Is Complete

Suppose Salary in F2 should be entered only after Department in C2 has been selected.

=OR(
F2="",
C2<>""
)

This allows Salary to remain blank, but a salary value can be entered only when Department is not blank.

Require Joining Date Before Status

=OR(
H2="",
E2<>""
)

16. Input Messages and Error Alerts

Create an Input Message

1. Open Data Validation.
2. Select the Input Message tab.
3. Enter a title such as Employee ID.
4. Enter a message such as:

Enter a unique ID in the format EMP-1001.

Error Alert Styles

Style	Behaviour
Stop	Blocks the invalid entry
Warning	Warns the user but allows continuation
Information	Displays information but allows the entry

Example Error Message

Title: Invalid Employee ID

Message:
Enter a unique Employee ID beginning with EMP-.

17. Finding Invalid Existing Data

Data Validation primarily controls new entries. To find existing values that violate the rules:

1. Select the validated range.
2. Go to Data → Data Validation.
3. Select Circle Invalid Data.

Excel draws red circles around invalid entries. Use Clear Validation Circles after correcting them.

18. What Is Conditional Formatting?

Conditional Formatting automatically changes the appearance of cells when selected conditions are satisfied.

Common Uses

• Highlight high and low sales.
• Identify duplicate IDs.
• Find blank required fields.
• Highlight overdue invoices.
• Show attendance performance.
• Identify low stock.
• Create traffic-light indicators.
• Highlight complete rows based on status.

19. Highlighting Cells Based on Values

Highlight Salary Greater Than ₹50,000

1. Select the Salary column.
2. Go to Home → Conditional Formatting.
3. Select Highlight Cells Rules → Greater Than.
4. Enter 50000.
5. Select the required format.

Highlight Attendance Below 75%

1. Select the Attendance column.
2. Select Less Than.
3. Enter 75%.
4. Apply a red fill.

20. Highlighting Duplicate Values

Built-In Duplicate Rule

1. Select the Employee ID column.
2. Go to Conditional Formatting → Highlight Cells Rules.
3. Select Duplicate Values.
4. Choose a red format.

Formula-Based Duplicate Rule

Select the range beginning with A2 and use:

=COUNTIF($A:$A,A2)>1

Highlight Every Occurrence After the First

=COUNTIF($A$2:A2,A2)>1

The first occurrence remains unchanged, while later duplicates are highlighted.

21. Highlighting Blank Required Fields

Select the Employee Name range beginning with B2 and use:

=B2=""

Highlight Blank Cells Only When Employee ID Exists

=AND(
$A2<>"",
B2=""
)

This avoids highlighting unused rows at the bottom of the data-entry sheet.

22. Highlighting an Entire Row

To format an entire record, select the complete data range and use a formula based on one column.

Highlight Resigned Employees

=$H2="Resigned"

Highlight Employees on Probation

=$H2="Probation"

Highlight Low Attendance Rows

=$G2<75%

How the Formula Works

The dollar sign fixes the Status or Attendance column, while the row number remains relative. Excel evaluates each row separately.

23. Highlighting Overdue Dates

Highlight Past Due Dates

Suppose Due Date is in column E and Status is in column H.

=AND(
$E2<TODAY(),
$H2<>"Completed"
)

Highlight Items Due Today

=AND(
$E2=TODAY(),
$H2<>"Completed"
)

Highlight Deadlines Within Seven Days

=AND(
$E2>TODAY(),
$E2<=TODAY()+7,
$H2<>"Completed"
)

Suggested Colours

• Red: Overdue
• Orange: Due today
• Yellow: Due within seven days
• Green: Completed

24. Alternate Row Highlighting with a Formula

Select the complete data range and use:

=MOD(ROW(),2)=0

This formats every even-numbered row.

Alternate Group Colour by Department

For complex grouped formatting, use helper columns or Power Query. Standard banded rows are usually easier to create with an Excel Table style.

25. Highlighting Top and Bottom Values

Built-In Top/Bottom Rules

• Top 10 Items
• Top 10%
• Bottom 10 Items
• Bottom 10%
• Above Average
• Below Average

Highlight the Highest Salary with a Formula

=F2=MAX($F$2:$F$100)

Highlight the Lowest Attendance

=G2=MIN($G$2:$G$100)

Highlight Salaries Above Average

=F2>AVERAGE($F$2:$F$100)

26. Using Data Bars

Data Bars display horizontal bars inside cells. The bar length represents the value’s relative size.

Useful Applications

• Monthly sales
• Employee performance
• Stock quantity
• Attendance percentage
• Project completion percentage

Apply Data Bars

1. Select the numerical range.
2. Go to Conditional Formatting → Data Bars.
3. Select a colour and style.
4. Use Manage Rules to adjust minimum and maximum values.

27. Using Colour Scales

Colour Scales use two or three colours to show low, medium and high values.

Example Attendance Scale

• Red: Low attendance
• Yellow: Average attendance
• Green: High attendance

Important Consideration

Colour scales show relative values. When exact thresholds are important, formula-based rules or icon sets may be more appropriate.

28. Using Icon Sets

Icon Sets display symbols such as arrows, flags, circles or traffic lights.

Attendance KPI Example

Attendance	Indicator
90% or above	Green
75% to below 90%	Yellow
Below 75%	Red

Configure the Icon Rule

1. Select the Attendance range.
2. Apply a three-icon set.
3. Open Conditional Formatting → Manage Rules.
4. Change Type from Percent to Number.
5. Set the green threshold to 0.90.
6. Set the yellow threshold to 0.75.
7. Select Show Icon Only if appropriate.

Excel stores 90% as 0.90 and 75% as 0.75.

29. Creating Multiple Formula-Based Rules

Green Rule: Excellent Attendance

=$G2>=90%

Yellow Rule: Acceptable Attendance

=AND(
$G2>=75%,
$G2<90%
)

Red Rule: Low Attendance

=$G2<75%

Use Manage Rules to arrange the priority and check the Applies To ranges.

30. Conditional Formatting with Text

Highlight Cells Containing “Pending”

=$H2="Pending"

Highlight Text Containing “Manager”

=ISNUMBER(
SEARCH(
"Manager",
$D2
)
)

Highlight Gmail Addresses

=ISNUMBER(
SEARCH(
"@gmail.com",
$I2
)
)

31. Highlight Search Results

Suppose the user enters a search word in B1 and employee names are stored in B4:B100.

=AND(
$B$1<>"",
ISNUMBER(
SEARCH(
$B$1,
B4
)
)
)

The matching employee names are highlighted as the user types.

32. Stop Conditional Formatting from Affecting Blank Cells

A rule such as =F2<50000 may format blank cells because Excel can treat blanks like zero in some comparisons.

Improved Formula

=AND(
F2<>"",
F2<50000
)

33. Managing Conditional Formatting Rules

1. Go to Home → Conditional Formatting → Manage Rules.
2. Select the correct worksheet or current selection.
3. Review rule order.
4. Check each Applies To range.
5. Edit incorrect formulas.
6. Remove duplicate or unnecessary rules.
7. Use Stop If True when one rule should prevent later rules from applying.

Why Rule Order Matters

If the same cell satisfies multiple rules, the rule priority can determine the final formatting. Place the most specific rules above broader rules.

34. Data Validation vs Conditional Formatting

Data Validation	Conditional Formatting
Controls data entry	Changes appearance
Can block invalid values	Does not prevent entry
Creates dropdowns	Creates visual alerts
Displays input and error messages	Displays colours, bars and icons
Improves data accuracy	Improves data interpretation

Professional workbooks often use both features together. Data Validation prevents errors, and Conditional Formatting identifies exceptions that still require attention.

35. Create a Controlled Employee Entry System

Employee ID Rule

=AND(
LEFT(A2,4)="EMP-",
LEN(A2)=8,
COUNTIF($A:$A,A2)=1
)

Employee Name Rule

=AND(
B2<>"",
ISTEXT(B2)
)

Joining Date Rule

=AND(
E2>=DATE(1990,1,1),
E2<=TODAY()
)

Salary Rule

=AND(
ISNUMBER(F2),
F2>=10000,
F2<=500000
)

Attendance Rule

=AND(
G2>=0,
G2<=100%
)

Phone Rule

=AND(
LEN(J2)=10,
ISNUMBER(--J2)
)

36. Common Data Validation Mistakes

• Applying validation only to one cell instead of the complete input range
• Using relative references incorrectly in custom formulas
• Allowing blanks when a field is mandatory
• Typing dropdown items differently in separate places
• Using static source ranges that do not include new values
• Copying unvalidated cells over validated cells
• Assuming validation automatically corrects existing data
• Using INDIRECT unnecessarily in very large workbooks
• Failing to test boundary and invalid values

37. Common Conditional Formatting Mistakes

• Applying rules to the wrong range
• Using incorrect absolute and relative references
• Creating many duplicate rules through copying and pasting
• Formatting blank cells unintentionally
• Using too many colours without a clear meaning
• Using relative colour scales when fixed thresholds are required
• Ignoring rule priority
• Using formatting as a replacement for correcting invalid data

38. Best Practices

• Keep dropdown source lists on a separate worksheet.
• Convert support lists into Excel Tables.
• Use named ranges for readable validation sources.
• Use input messages to explain required formats.
• Use Stop alerts for critical fields.
• Use custom validation for IDs and complex business rules.
• Combine validation with conditional formatting.
• Use consistent colours across the workbook.
• Use red for errors, yellow for warnings and green for successful results.
• Avoid excessive decorative formatting.
• Review conditional formatting rules regularly.
• Test the workbook using valid, invalid and blank values.

39. Practical Assignment: Employee Data-Entry and Monitoring System

Create a controlled employee-management workbook containing at least 50 employee records.

Required Worksheets

• Employee_Entry
• Support_Lists
• Designation_Master
• Validation_Report
• Employee_Summary

Required Features

1. Create a unique Employee ID validation rule.
2. Create a Department dropdown.
3. Create a dependent Designation dropdown.
4. Restrict Joining Date to valid past dates.
5. Restrict Salary to an approved range.
6. Restrict Attendance to 0–100%.
7. Create a Status dropdown.
8. Create basic email validation.
9. Create 10-digit phone validation.
10. Add input messages to important columns.
11. Add Stop error alerts.
12. Highlight duplicate Employee IDs.
13. Highlight missing required fields.
14. Highlight low-attendance records.
15. Highlight resigned employees.
16. Add traffic-light indicators for attendance.
17. Create a validation summary showing invalid records.

40. Practice Exercises

Exercise 1: Student Admission Form

Create dropdowns for Class, Gender and Admission Status. Validate Roll Number, Date of Birth, Marks and Phone Number.

Exercise 2: Inventory Entry System

Create Category and dependent Product dropdowns. Restrict Quantity and Unit Price to positive values and highlight stock below 20.

Exercise 3: Invoice Tracker

Validate Invoice Number and Due Date. Highlight overdue, due-today and upcoming invoices using separate rules.

Exercise 4: Attendance Sheet

Create a dropdown containing Present, Absent, Leave and Holiday. Apply different colours to each status.

Exercise 5: Sales Target Monitor

Apply data bars to sales, icon sets to target achievement and formula-based formatting to highlight employees below target.

41. Chapter Quiz

1. What is the main purpose of Data Validation?
2. Which validation type creates a dropdown list?
3. How can a dropdown source expand automatically?
4. What is a dependent dropdown?
5. What does a Custom validation formula need to return?
6. Which error-alert style blocks invalid entries?
7. How can duplicate Employee IDs be prevented?
8. What is the main purpose of Conditional Formatting?
9. How can an entire row be highlighted based on one status cell?
10. What causes multiple formatting rules to conflict?
11. Why should blank cells be excluded from some formula rules?
12. What is the difference between Data Validation and Conditional Formatting?

42. Frequently Asked Questions

Does Data Validation remove existing invalid data?

No. It primarily controls new or edited entries. Use Circle Invalid Data, formulas or Power Query to identify existing problems.

Can users paste invalid data over validated cells?

Some paste operations can overwrite validation or introduce invalid values. Protect important worksheets and review imported data regularly.

Can a dropdown update automatically?

Yes. Use an Excel Table, a dynamic named range or a spilled UNIQUE and SORT result.

Why is my conditional formatting formula affecting the wrong row?

The formula may use incorrect relative or absolute references. Write the formula for the first row of the Applies To range and lock only the required column or row.

Does Conditional Formatting change the actual value?

No. It changes only the appearance of the cell. The underlying value remains unchanged.

Can Conditional Formatting slow down Excel?

Yes. Many duplicated rules, full-column formulas and complex volatile calculations can reduce workbook performance.

Conclusion

Data Validation prevents incorrect entries by controlling numbers, dates, text lengths and category selections. Dropdown lists improve consistency, while custom formulas handle unique IDs, phone numbers, email structures and business-specific rules.

Conditional Formatting converts workbook data into visual alerts. It can identify duplicates, blanks, overdue dates, low attendance and high-performing records. Data bars, colour scales and icon sets make reports easier to understand.

In the next chapter, we will study PivotTables and PivotCharts, including grouping, filtering, calculated fields, value display options, slicers, timelines and interactive management summaries.