Conditional formatting, data validation and protecting sheets

Three tools that turn a spreadsheet into a reliable business tool: conditional formatting highlights what matters, data validation stops wrong entries, and protection stops accidents.

Conditional formatting (Home → Conditional Formatting)

Highlight a whole row with a formula

To colour the entire row of students who owe fees (balance in column D):

1. Select the data rows, e.g. A2:E41.
2. Conditional Formatting → New Rule → Use a formula.
3. Formula: =$D2>0 (dollar before D locks the column; the row stays relative).
4. Choose a light red fill → OK.

More formula ideas:

=$E2="Not paid"                     status text
=$C2<TODAY()                        past-due dates
=AND($B2>=80, $F2="Form 4")         top Form 4 students
=MOD(ROW(),2)=0                     stripe every other row

Manage or delete rules in Conditional Formatting → Manage Rules.

Data validation (Data → Data Validation)

Stop mistakes at entry instead of cleaning them later.

Use the Input Message tab to show a hint when the cell is selected ("Enter 10 digits, e.g. 0712345678"), and the Error Alert tab for a clear message.

Drop-down lists from a range: put the choices on a sheet named Lists, make them an Excel Table, and use it as the source. Adding a new choice updates every drop-down.

Protecting sheets and workbooks

• Unlock input cells first: select cells people should edit → Ctrl + 1 → Protection → untick Locked.
• Review → Protect Sheet: everything else (formulas, headers) is now locked. Add a password if needed.
• Review → Protect Workbook: stops adding, deleting or renaming sheets.
• File → Info → Protect Workbook → Encrypt with Password: needed to open the file at all.

Sheet protection prevents accidents, not determined attackers. For confidential data (salaries, IDs), use file encryption and share carefully.

Putting it all together: a fee collection sheet

1. A Table with Student, Form, Fee, Paid, Balance (=[@Fee]-[@Paid]), Status (=IF([@Balance]<=0,"Cleared","Owing")).
2. Data validation: Form as a drop-down (Form 1–4), Paid as a decimal ≥ 0.
3. Conditional formatting: rows with Balance > 0 in light red; data bars on Paid.
4. Unlock only the Paid column, then Protect Sheet so formulas can't be overwritten.
5. A summary area with COUNTIF, SUMIFS and a chart of collections by form.

That's a tool a school bursar could use tomorrow.

Why formatting rules, validation and protection matter

A spreadsheet shared with a team gets typed into by many people, and mistakes multiply: a phone number typed into the amount column, "Nrb" instead of "Nairobi", a deleted formula, a fee balance that nobody notices is overdue. Conditional formatting makes problems visible instantly, data validation stops bad data being entered, and protection prevents accidental damage to formulas. Together they turn a fragile sheet into a reliable tool that schools, SACCOs, chamas, shops and offices can trust.

Conditional formatting types and when to use them

Use colour meaningfully and sparingly: if half the sheet is highlighted, nothing stands out. Add a short legend ("Red = overdue") and don't rely on colour alone for colour-blind users; combine with a status column.

Formula rules: powerful examples

Select the data rows (for example A2:F200) and use New Rule → Use a formula. Write the formula for the first row; Excel applies it to every row:

=$F2="Overdue"                       whole row red when Status is Overdue
=$E2<TODAY()                         due date passed
=AND($E2>=TODAY(), $E2<=TODAY()+7)   due within the next week
=$D2>$C2                             spent more than the budget
=MOD(ROW(),2)=0                      shade every other row
=COUNTIF($B$2:$B$200,$B2)>1          duplicate customer in column B
=ISBLANK($C2)                        required field is empty
=$B2=$I$1                            highlight rows matching a search value typed in I1

The $ before the column letter keeps the rule checking the same column as it moves across the row, while the row number changes for each row.

Manage Rules (Home → Conditional Formatting → Manage Rules) shows all rules, their order and their ranges. Rules higher in the list win; tick "Stop If True" to prevent lower rules applying.

Data validation options in depth

Useful custom validation formulas:

=COUNTIF($B:$B,B2)=1                    unique values only (e.g. admission numbers)
=AND(LEN(C2)=10, LEFT(C2,2)="07")       phone starting 07 with 10 digits (format column as Text)
=ISNUMBER(SEARCH("@",D2))               email must contain @
=E2<=TODAY()                            date can't be in the future
=F2<=G2                                 amount paid can't exceed the amount due

Use the Input Message tab to show a hint when the cell is selected ("Enter the 10-digit number, e.g. 0712345678") and the Error Alert tab for a clear message. Style "Stop" blocks entry; "Warning" and "Information" allow overriding.

Validation doesn't check data that is pasted over it or that existed before. Use Data → Data Validation → Circle Invalid Data to find existing problems.

Dependent drop-downs

A second drop-down that changes based on the first (County → Sub-county):

1. Put sub-counties in columns with the county as header (Nairobi | Mombasa | Kisumu...).
2. Name each column range with the county name (Formulas → Create from Selection → Top row).
3. First drop-down: List with source =Counties.
4. Second drop-down: List with source =INDIRECT(A2).

In Excel 365 you can instead use =FILTER(SubCounties[Name], SubCounties[County]=A2) in a helper area as the list source.

Protection levels explained

Important: sheet protection is about preventing accidents, not real security; it can be bypassed. For confidential data (salaries, medical records, ID numbers), use file encryption, store files in access-controlled locations (OneDrive/SharePoint/Google Drive permissions), and share only with people who need it, in line with Kenya's Data Protection Act. Keep passwords in a password manager: a lost encryption password usually means the file is lost.

Building a professional input form sheet

1. Put inputs on the left in a clearly shaded colour (light yellow is a common convention for "type here").
2. Add data validation and input messages to every input cell.
3. Put calculated results in a different colour, locked.
4. Add conditional formatting for warnings (red when over budget).
5. Unlock input cells, protect the sheet (allow sorting and filtering if needed).
6. Test it as a user would: try typing wrong data, pasting, and deleting.

Common mistakes

Practice

1. Make a fee register where overdue rows turn red and fully paid rows turn green, based on a Status column.
2. Add a drop-down for Payment Method (M-Pesa, Bank, Cash) and validation that amounts are greater than 0.
3. Prevent duplicate admission numbers with custom validation.
4. Create County → Sub-county dependent drop-downs for three counties.
5. Lock all formulas, protect the sheet, and test that inputs still work.