Courses / Application of ICT (GEN-103) – BS 1st Semester

Week 13 Lab 13 – MS Excel: Filtering, Data Validation, Protection and Conditional Formatting

Week 13 – Number Systems and Conversions · 90 min read · 15 MCQs to check yourself

Lab objectives

Filter and sort data; restrict entries with data validation; protect a worksheet while unlocking input cells; highlight data with conditional formatting.

Task 1 – Filtering data (and sorting)

Filter shows only the rows that meet criteria and hides the others (data is not deleted).

  1. Click any cell in the data → Data → Filter (or Ctrl + Shift + L). Drop-down arrows appear in the heading row.
  2. Click an arrow:
    • Tick/untick values (e.g. show only "Lahore").
    • Text Filters – Contains, Begins With, Equals.
    • Number Filters – Greater Than (marks > 80), Between, Top 10, Above Average.
    • Date Filters, Filter by Color.
  3. Filtered row numbers turn blue; the arrow shows a funnel icon.
  4. Clear the filter: Data → Clear; turn off: click Filter again.

Sort arranges rows: Data → Sort A to Z / Z to A (ascending/descending) or Sort dialog for multiple levels (e.g. by City, then by Marks descending).

Task 2 – Data validation

Data validation restricts what can be typed in cells and prevents invalid entries.

  1. Select cells (e.g. B2:B50 marks) → Data → Data Validation.
  2. Settings tab → Allow:
    • Whole number between 0 and 100 (marks).
    • Decimal, Date (between two dates), Time, Text length (e.g. CNIC = 13 characters).
    • List – drop-down list: Source Pass,Fail or Lahore,Karachi,Islamabad or a range.
    • Custom – formula rule.
  3. Input Message tab – a tip shown when the cell is selected ("Enter marks 0–100").
  4. Error Alert tab – message when invalid data is entered; styles: Stop (blocks entry), Warning (asks), Information (allows).
  5. Circle Invalid Data marks existing wrong entries.

Task 3 – Protecting a worksheet and unlocking cells

By default all cells are locked, but locking only takes effect when the sheet is protected.

  1. Unlock input cells first: select the cells users may edit (e.g. marks B2:D20) → right-click → Format Cells (Ctrl + 1) → Protection tab → untick Locked → OK.
  2. (Optional) tick Hidden for formula cells so formulas are not shown in the formula bar.
  3. Review → Protect Sheet → enter an optional password → choose what users can do (Select unlocked cells, Format cells, Insert rows…) → OK → confirm password.
  4. Now users can type only in unlocked cells; trying to edit a locked cell shows a warning.
  5. Unprotect Sheet: Review → Unprotect Sheet (enter password).

Task 4 – Conditional formatting

Conditional formatting automatically changes the appearance of cells when they meet a condition. Home → Conditional Formatting:

Option Example
Highlight Cells Rules Greater Than 80 → green fill; Less Than 50 → light red fill with dark red text; Between; Equal To; Text that Contains "Fail"; Duplicate Values; A Date Occurring
Top/Bottom Rules Top 10 items, Top 10%, Bottom 10, Above Average, Below Average
Data Bars Bar inside the cell proportional to value
Color Scales Two/three-colour gradient (low = red, high = green)
Icon Sets Arrows, traffic lights, stars
New Rule Custom formula, e.g. highlight the whole row where Result = "Fail": =$H2="Fail"
Clear Rules / Manage Rules Remove or edit rules

Lab exercise (submit)

Using a 20-student result sheet with columns Roll No, Name, City, ICT, English, Maths, Total, Percentage, Result:

  1. Apply Filter: show only students from Lahore; then those with Percentage greater than 70; clear filters. Sort by Total (largest to smallest).
  2. Apply data validation: marks must be whole numbers 0–100 with an input message and a Stop error alert; City must be chosen from a drop-down list.
  3. Use =IF(I2>=50,"Pass","Fail") for Result.
  4. Conditional formatting: marks below 50 in light red, above 85 in green; data bars on Total; icon set on Percentage; highlight "Fail" text.
  5. Unlock the marks cells, then protect the sheet with a password so totals and formulas cannot be changed.

How to Use Conditional Formatting and Data Validation in Microsoft Excel

Short and long questions

1. What is data validation? Write the steps to allow only marks between 0 and 100.

Model answer

Data validation restricts the type or range of data entered in cells. Steps: select the marks cells → Data → Data Validation → Settings: Allow Whole number, Data between, Minimum 0, Maximum 100 → add an Input Message and Error Alert (Stop) → OK.

2. How do you protect a worksheet but allow data entry in some cells?

Model answer

Select the input cells → Format Cells (Ctrl+1) → Protection → untick Locked → OK; then Review → Protect Sheet → enter password → OK. Only the unlocked cells can now be edited.

3. Explain filtering, data validation, worksheet protection and conditional formatting in Excel.

Model answer

Filtering: Data → Filter (Ctrl+Shift+L) adds drop-down arrows to headings; choose values, Text/Number/Date filters (e.g. Greater Than 80, Top 10) to display only matching rows; others are hidden; Clear removes the filter. Sorting arranges rows A→Z or Z→A. Data validation: Data → Data Validation restricts input (whole number, decimal, list, date, text length, custom); Input Message guides users; Error Alert (Stop/Warning/Information) handles invalid entries. Protection: all cells are locked by default; unlock input cells via Format Cells → Protection; then Review → Protect Sheet with a password; Protect Workbook protects structure; Encrypt with Password protects opening. Conditional formatting: Home → Conditional Formatting → Highlight Cells Rules (greater than, less than, text contains, duplicates), Top/Bottom Rules, Data Bars, Color Scales, Icon Sets and custom formula rules; Manage/Clear Rules.

4. What is conditional formatting? Give three examples.

Model answer

Conditional formatting automatically formats cells that meet given conditions. Examples: highlight marks less than 50 in red (Highlight Cells Rules); show data bars for totals; apply icon sets/colour scales to percentages; highlight top 10 values.

This lesson ends with a quiz of 15 MCQs. Open the lesson on PrepOasis to take it and save your progress.

Previous: Week 13 Theory – Number Systems: Binary, Octal, Decimal and Hexadecimal · Next: Week 14 Theory – The Internet, Browsers and Search Engines

All lessons in Application of ICT (GEN-103) – BS 1st Semester