Lab Manual: Practical 2 – Conditional Formatting

 1. Objective

The primary objective of this practical is to enable students to effectively use Conditional Formatting in Microsoft Excel. By the end of this practical, students will be able to:

• Apply rules to automatically format cells based on their content.
• Highlight important data and visualize trends.
• Improve the readability and interpretability of large datasets.

2. Introduction to Conditional Formatting

Conditional Formatting in Excel is a dynamic feature that allows specific formatting (e.g., colors, fonts, icons) to be applied to cells based on predefined rules. Unlike static formatting, conditional formatting updates automatically as data changes.

Example:
If you have a list of student scores, you can automatically highlight scores above 50 in green and those 50 or below in red without manual intervention.

Applications include:

• Highlighting Key Information: Identify critical data (e.g., overdue payments, low stock levels).
• Identifying Trends: Visualize performance using color scales or data bars.
• Faster Decision-Making: Provide instant visual cues for data analysis.
• Improving Readability: Enhance the visual clarity of spreadsheets.

3. Pre-requisites

• Basic understanding of Microsoft Excel interface and navigation.
• Ability to open, save, and enter data into Excel worksheets.
• Familiarity with selecting cells, ranges, rows, and columns.

4. Software Required

• Microsoft Excel (latest or compatible version).

5. Procedure: Step-by-Step Instructions

Step 5.1: Opening a New Workbook and Entering Data

1.     Launch Microsoft Excel and create a Blank Workbook.

2.     Rename the sheet to Sales Data.

3.     Enter the following data starting from cell A1:

Product	Region	Sales	Revenue ($)	Profit Margin (%)	Status
Laptop	North	150	150000	15%	In Stock
Monitor	South	80	24000	10%	Low Stock
Keyboard	East	250	7500	20%	In Stock
Mouse	West	300	6000	25%	In Stock
Camera	North	40	20000	8%	Out of Stock
Speaker	South	120	12000	12%	In Stock
Printer	East	30	9000	5%	Low Stock
Router	West	70	7000	18%	In Stock

Step 5.2: Highlight Cells Rules

• Highlight Sales > 100:
  Select C2:C9 → Home → Conditional Formatting → Highlight Cells Rules → Greater Than… → Enter 100 → Select Green Fill with Dark Green Text → OK.
• Highlight Revenue < 10000:
  Select D2:D9 → Highlight Cells Rules → Less Than… → Enter 10000 → Select Light Red Fill with Dark Red Text → OK.
• Highlight “Low Stock” in Status Column:
  Select F2:F9 → Text that Contains… → Enter Low Stock → Select Yellow Fill with Dark Yellow Text → OK.

Step 5.3: Top/Bottom Rules

• Highlight Top 3 Sales:
  Select C2:C9 → Top/Bottom Rules → Top 10 Items… → Change 10 to 3 → Select Green Fill with Dark Green Text → OK.
• Highlight Bottom 2 Profit Margins:
  Select E2:E9 → Top/Bottom Rules → Bottom 10 Items… → Change 10 to 2 → Select Light Red Fill with Dark Red Text → OK.

Step 5.4: Data Visualization (Data Bars, Color Scales, Icon Sets)

• Data Bars for Revenue:
  Select D2:D9 → Data Bars → Choose a gradient or solid fill.
• Color Scale for Profit Margin:
  Select E2:E9 → Color Scales → Choose Green–Yellow–Red.
• Icon Sets for Sales:
  Select C2:C9 → Icon Sets → Choose 3 Traffic Lights.

Step 5.5: Advanced Conditional Formatting (New Rule)

• Highlight Rows where Status = “Out of Stock”:
  Select A1:F9 → Conditional Formatting → New Rule… → Use a formula to determine which cells to format → Enter:

·         =$F2="Out of Stock"

Click Format… → Fill → Light Blue → OK → OK.

Step 5.6: Managing Conditional Formatting Rules

• Open Rule Manager:
  Conditional Formatting → Manage Rules… → View/Edit/Delete/Order rules.
• Clear Rules:
  Clear Rules from Selected Cells or Entire Sheet as needed.

6. Exercises / Tasks

Sheet 1: Student Grades

Create a new worksheet Student Grades with the following columns:
Student Name, Subject 1, Subject 2, Subject 3, Total, Percentage.

Data:

• Alice: 78, 85, 92
• Bob: 65, 70, 68
• Charlie: 90, 88, 75
• David: 45, 52, 48
• Eve: 82, 79, 85

Tasks:

1.     Calculate Total and Percentage.

2.     Apply a Color Scale to Percentage (Green = High, Red = Low).

3.     Highlight scores < 60 in Light Red Fill with Dark Red Text.

4.     Apply Icon Set to Total column.

5.     Highlight rows where Percentage < 60 using formula-based rule.

Sheet 2: Inventory Tracking

Create a worksheet Inventory Tracking with the following columns:
Item ID, Item Name, Quantity, Reorder Level, Last Sale Date.

Data:

• P001 – Widgets – 150 – 100 – 2025-08-25
• P002 – Gadgets – 75 – 80 – 2025-08-20
• P003 – Doodads – 250 – 50 – 2025-08-28
• P004 – Fuzzies – 40 – 50 – 2025-07-15

Tasks:

1.     Highlight Quantity below Reorder Level in Orange Fill with Dark Orange Text.

2.     Apply Data Bars to Quantity.

3.     Highlight Last Sale Date if older than 30 days using:

4.      =TODAY()-E2>30

5.     Highlight entire row if Quantity ≤ Reorder Level.

7. Assessment / Deliverables

• Submit the completed Excel workbook (.xlsx) including:
• Sales Data (with all rules applied)
• Student Grades (all tasks completed)
• Inventory Tracking (all tasks completed)
• Ensure all rules work dynamically when data is changed.

 

 

Solutions: Practical 2 – Conditional Formatting

Course: Advanced Excel with Visualization
Course Code: CAD41VSP201
Practical Number: 2
Topic: Conditional Formatting

Sheet 1: Student Grades

Step 1: Data Entry

1.     Create a new worksheet and rename it to Student Grades.

2.     Enter the following data starting from A1:

Student Name	Subject 1	Subject 2	Subject 3	Total	Percentage
Alice	78	85	92
Bob	65	70	68
Charlie	90	88	75
David	45	52	48
Eve	82	79	85

Task A: Calculate Total and Percentage

1.     Total:

o    In cell E2, enter:

o    =SUM(B2:D2)

o    Drag the formula down to E6.

2.     Percentage:

o    If each subject is out of 100, the total marks are 300.

o    In cell F2, enter:

o    =E2/300

o    Format as Percentage → Adjust decimal places to one digit.

o    Drag the formula down to F6.

Task B: Apply Color Scale to Percentage

• Select F2:F6 → Home → Conditional Formatting → Color Scales → Select Green–Yellow–Red.

Task C: Highlight Scores Below 60

• Select B2:D6 → Conditional Formatting → Highlight Cells Rules → Less Than… → Enter 60 → Choose Light Red Fill with Dark Red Text → OK.

Task D: Apply Icon Set to Total

• Select E2:E6 → Conditional Formatting → Icon Sets → Choose 3 Traffic Lights (or 3 Arrows).

Task E: Highlight Rows with Percentage < 60

• Select A2:F6 → Conditional Formatting → New Rule… → Use a formula to determine which cells to format.
• Enter:

·         =$F2<0.6

• Format → Fill: Light Blue or Light Orange → OK.

Sheet 2: Inventory Tracking

Step 1: Data Entry

1.     Create a new worksheet and rename it to Inventory Tracking.

2.     Enter the following data starting from A1:

Item ID	Item Name	Quantity	Reorder Level	Last Sale Date
P001	Widgets	150	100	2025-08-25
P002	Gadgets	75	80	2025-08-20
P003	Doodads	250	50	2025-08-28
P004	Fuzzies	40	50	2025-07-15

Task F: Highlight Quantity Below Reorder Level

• Select C2:C5 → Conditional Formatting → New Rule… → Use a formula.
• Enter:

·         =$C2<$D2

• Format → Fill: Orange, Font: Dark Orange → OK.

Task G: Apply Data Bars to Quantity

• Select C2:C5 → Conditional Formatting → Data Bars → Choose Gradient or Solid Fill.

Task H: Highlight Last Sale Date Older than 30 Days

• Select E2:E5 → Conditional Formatting → New Rule… → Use a formula.
• Enter:

·         =TODAY()-$E2>30

• Format → Fill: Light Red → OK.

Task I: Highlight Rows where Quantity ≤ Reorder Level

• Select A2:E5 → Conditional Formatting → New Rule… → Use a formula.
• Enter:

·         =$C2<=$D2

• Format → Fill: Light Yellow → OK.

Outcome

By completing these exercises, students will:

• Gain hands-on experience with Conditional Formatting.
• Learn to apply both simple and formula-based rules.
• Enhance spreadsheet readability and decision-making efficiency.