Course contents · Data validation and conditional formatting

Data validation and drop-down lists

Open the practice in the trainer →

Data → Data Validation restricts what can be entered in a cell: whole numbers in a range, dates, text length or values from a list.

Drop-down list

  1. Prepare a list of allowed values (e.g. F2:F4).
  2. Select the input cells → Data Validation → Allow: List → Source: =$F$2:$F$4.
  3. On the “Error Alert” tab set the message the user sees after an invalid entry.

Dependent drop-down lists

The second list depends on the choice in the first one. Create named ranges whose names match the values of the first list (e.g. “Fruit”, “Vegetables”) and use =INDIRECT(A2) as the source of the second list. The function turns text into a reference to a range.

In the trainer, B2:B7 has data validation — pick a department from the drop-down (arrow to the right of the cell) or type it. An invalid value will be rejected.

Tasks in the trainer

  1. Fill the departments in B2:B7 with values from the list (any, as long as they are valid).
  2. In I2:I4 calculate the payroll of each department.
  3. In J2:J4 count the employees in each department.
  4. In K2:K4 — the department’s average salary (payroll / headcount); 0 if the department is empty.
  5. In C9 find the highest salary.

Solve the tasks in the trainer →

Check yourself

  1. Which function is used in the source of a dependent list?
  2. Where do you set the message shown after an invalid entry?