3.2 Conditional Formatting & Data Validation

Conditional formatting and data validation are useful Excel features for improving data quality and making important information easier to identify. Conditional formatting changes the appearance of cells based on conditions, while data validation controls what users can enter into selected cells.

What You Will Learn

• Understand conditional formatting.
• Highlight cells based on values.
• Identify duplicate values.
• Use data bars and color scales.
• Create basic conditional formatting rules.
• Understand data validation.
• Create drop-down lists.
• Restrict incorrect data entry.

What is Conditional Formatting?

Conditional formatting automatically changes the appearance of cells when specific conditions are met. It can be used to highlight high or low values, identify duplicate information, show trends, or draw attention to important records.

Highlighting Cell Values

Users can create rules to highlight values that are greater than, less than, equal to, or within a specified range.

For example, in a marks worksheet, scores below a selected passing value can be highlighted so that they can be identified quickly.

Duplicate Values

Conditional formatting can be used to identify duplicate entries. This can be useful when checking customer lists, employee records, product codes, invoice numbers, or other datasets where duplicate information may need to be reviewed.

Data Bars

Data bars display horizontal bars inside cells based on the values in those cells. Larger values generally receive longer bars, making it easier to compare values visually.

Color Scales

Color scales can apply different visual formatting to cells according to their relative values. They can help users identify high, medium, and low values in a dataset.

What is Data Validation?

Data validation controls what type of information can be entered into a cell. It can help reduce incorrect entries and maintain consistency across a worksheet.

Drop-Down Lists

A drop-down list allows users to select a value from a predefined set of options instead of typing the value manually. For example, an employee worksheet could contain a Department field with options such as Sales, Finance, HR, and Operations.

Number and Date Validation

Data validation can be configured to allow only specific numbers, ranges, dates, or other supported types of input. This is useful when a worksheet has defined data-entry requirements.

Input Messages and Error Alerts

Data validation can provide instructions to users before they enter data and can display an error message when an invalid value is entered. This helps users understand what information is expected.

Practical Activity

Create a student marks worksheet containing Student Name, Subject, Marks, Grade, and Attendance.

1. Enter information for at least ten students.
2. Apply conditional formatting to highlight marks below a selected passing value.
3. Apply data bars to the Marks column.
4. Identify duplicate student names using conditional formatting.
5. Create a drop-down list for the Subject field.
6. Restrict the Marks field to an appropriate numerical range.
7. Add a suitable input message for the Marks field.
8. Test the validation by entering an invalid value.

Workplace Example

Create an employee status worksheet with Employee Name, Department, Status, Performance Score, and Joining Date. Use a drop-down list for employee status and conditional formatting to highlight performance scores according to selected criteria.

Learning Outcome

After completing this section, learners will be able to use conditional formatting to identify important information visually and apply data validation to control and improve the quality of data entered into Excel worksheets.