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).
- Click any cell in the data → Data → Filter (or Ctrl + Shift + L). Drop-down arrows appear in the heading row.
- 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.
- Filtered row numbers turn blue; the arrow shows a funnel icon.
- 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.
- Select cells (e.g.
B2:B50marks) → Data → Data Validation. - 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,FailorLahore,Karachi,Islamabador a range. - Custom – formula rule.
- Input Message tab – a tip shown when the cell is selected ("Enter marks 0–100").
- Error Alert tab – message when invalid data is entered; styles: Stop (blocks entry), Warning (asks), Information (allows).
- 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.
- 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. - (Optional) tick Hidden for formula cells so formulas are not shown in the formula bar.
- Review → Protect Sheet → enter an optional password → choose what users can do (Select unlocked cells, Format cells, Insert rows…) → OK → confirm password.
- Now users can type only in unlocked cells; trying to edit a locked cell shows a warning.
- Unprotect Sheet: Review → Unprotect Sheet (enter password).
- Protect Workbook prevents adding, deleting, renaming or moving sheets (structure).
- File → Info → Protect Workbook → Encrypt with Password requires a password to open the file.
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:
- Apply Filter: show only students from Lahore; then those with Percentage greater than 70; clear filters. Sort by Total (largest to smallest).
- 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.
- Use
=IF(I2>=50,"Pass","Fail")for Result. - Conditional formatting: marks below 50 in light red, above 85 in green; data bars on Total; icon set on Percentage; highlight "Fail" text.
- 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