Lab 09
Data Validation Practice
A student marks file with two reference lists — teachers and classes. Your job is to build the table, apply validation to four columns, write meaningful error messages, and make the teacher list dynamic so adding a name updates the dropdown automatically.
Download marks.xlsxWhat's in the file
- Students sheet — columns: Student Name, arabic, english, history, teacher, class. Data rows have students but all score/teacher/class cells are empty.
- Reference data — a teachers list (8 names) and a class list (8 classes: 1-a through 4-a) on the same or a separate sheet.
Foundation
Convert the student data to a Table
Before applying any validation, convert the student data to an Excel Table. This makes column references work correctly and sets up the structure for the rest of the tasks.
1
Click anywhere in the student data.
2
Press
Ctrl + T → confirm headers → OK.3
Table Design → Table Name → type
marks → Enter.Whole Number
arabic, english, history — scores between 0 and 100
Select the data cells for all three score columns at the same time — you can apply the same validation rule to all of them in one step.
| Setting | Value |
|---|---|
| Allow | Whole Number |
| Data | between |
| Minimum | 0 |
| Maximum | 100 |
Error Alert tab
StyleStop
TitleInvalid Score
MessagePlease enter a whole number between 0 and 100.
Select all three columns at once
Hold
Ctrl and click each column header to select multiple non-adjacent columns, then apply the validation. One dialog sets the rule for all three.List
teacher column — names from the teachers list
The teacher column should only accept names that exist in the teachers reference list. Before setting up this validation, complete Task 6 first — converting the teachers list to a Table makes this dynamic.
| Setting | Value |
|---|---|
| Allow | List |
| Source | =teachers[teachers] (after completing Task 6) |
| In-cell dropdown | ✓ Checked |
Error Alert tab
StyleStop
TitleInvalid Teacher
MessagePlease select a teacher name from the dropdown list.
List
class column — values from the class list
The class column accepts only the predefined class codes (1-a, 1-b, 2-a, 2-b, 2-c, 3-a, 3-b, 4-a). Reference the class list as a range — classes rarely change so a fixed range is fine here.
| Setting | Value |
|---|---|
| Allow | List |
| Source | Reference to the class list range (e.g. =$H$2:$H$9 — verify against your file) |
| In-cell dropdown | ✓ Checked |
Error Alert tab
StyleStop
TitleInvalid Class
MessagePlease select a valid class from the dropdown list.
Error Messages
Verify all error messages are set and meaningful
Review all four validation rules and confirm each one has a Stop-type error alert with a clear title and message. Test each column by trying to enter an invalid value.
arabic / english / history — rejects anything outside 0–100
arabic / english / history — rejects decimal numbers like 85.5
teacher — rejects any name not in the list
class — rejects any value not in the list
All four show a descriptive Stop error — not just the default Excel message
Dynamic List
Make the teacher list dynamic — new names appear automatically
A plain range reference like
=$E$2:$E$9 is static — if you add a teacher below row 9, the dropdown won't include them. The fix is to convert the teachers data to a Table and reference the column by name. Tables expand automatically when new rows are added.
The approach — Table reference
Convert teachers to a Table, then reference the column
1
Click anywhere in the teachers list (the column with the 8 names).
2
Press
Ctrl + T → confirm headers → OK. The header should read teachers.3
Table Design → Table Name → type
teachers → Enter.4
Go back to the teacher column validation (Data → Data Validation on the teacher cells). Change the Source to:
=teachers[teachers]5
Test it — add a new teacher name at the bottom of the teachers table, then click the dropdown in the teacher column. The new name should appear.
Why this works — Topic 01 in action
A Table column reference like
teachers[teachers] always includes every row in the table — even rows added later. This is the same dynamic behaviour that makes SUM(Sales[Desktop]) update automatically when new data is added. The same principle now drives the validation dropdown.