Data Validation and Conditional Formatting in Excel

Data Validation

• Purpose: restricts the type of data that can be entered into a cell to ensure accuracy and consistency
• Types of data validation:
  • Whole number
  • Decimal
  • Date
  • Time
  • Text length
  • Custom formula
• How to apply data validation:
  • Select the cell(s) to validate
  • Go to Data > Data Validation
  • Choose the validation type and set the criteria
  • Optional: set an error message to display when invalid data is entered

Conditional Formatting

• Purpose: highlights cells based on specific conditions or rules
• Types of conditional formatting:
  • Highlight cells based on value (e.g., greater than, less than, equal to)
  • Highlight cells based on formula (e.g., A1+B1=C1)
  • Highlight cells based on formatting (e.g., font color, background color)
• How to apply conditional formatting:
  • Select the cell(s) to format
  • Go to Home > Conditional Formatting
  • Choose the formatting rule and set the criteria
  • Optional: set the format to apply when the condition is true

Cell Referencing

• Types of cell references:
  • Relative reference: changes when the formula is copied to a new location (e.g., A1)
  • Absolute reference: remains the same when the formula is copied to a new location (e.g., $A$1)
  • Mixed reference: combination of relative and absolute references (e.g., A$1 or $A1)
• How to use cell references:
  • Use relative references when you want the formula to adjust to the new location
  • Use absolute references when you want the formula to always refer to the same cell
  • Use mixed references when you want to fix one part of the reference and adjust the other part

Note: The $ symbol is used to denote an absolute reference.

Data Validation

• Restricts the type of data that can be entered into a cell to ensure accuracy and consistency
• Offers various validation types, including whole number, decimal, date, time, text length, and custom formula
• Applied by selecting the cell(s) to validate, going to Data > Data Validation, choosing the validation type, and setting the criteria
• Optional error message can be set to display when invalid data is entered

Conditional Formatting

• Highlights cells based on specific conditions or rules to emphasize important data
• Offers various formatting types, including highlighting cells based on value, formula, and formatting
• Applied by selecting the cell(s) to format, going to Home > Conditional Formatting, choosing the formatting rule, and setting the criteria
• Optional format to apply when the condition is true can be set

Cell Referencing

• Relative references change when the formula is copied to a new location (e.g., A1)
• Absolute references remain the same when the formula is copied to a new location (e.g., $A$1)
• Mixed references combine relative and absolute references (e.g., A$1 or $A1)
• Use relative references when you want the formula to adjust to the new location
• Use absolute references when you want the formula to always refer to the same cell
• Use mixed references when you want to fix one part of the reference and adjust the other part
• The $ symbol is used to denote an absolute reference