QuitExams

Introduction

Conditional Formatting in LibreOffice Calc allows users to automatically change the appearance of cells—such as their font color, background color, or borders—based on specific conditions or formulas.

It helps highlight key trends, identify outliers, and visualize data patterns dynamically.

📊 Example Data Table

Below is a sample dataset you can use to practice conditional formatting in LibreOffice Calc:

Use the above data to apply different types of conditional formatting examples below.

1️⃣ Applying Conditional Formatting

1. Select the range of cells you want to format (e.g., C2:C11 for marks).
2. Go to Format → Conditional → Condition.
3. Set your condition (e.g., Cell value > 75).
4. Choose a cell style (like Green background).
5. Click OK to apply.

2️⃣ Color-based Conditional Formatting Examples

A. Highlight High Scores

Example: Highlight marks above 75 in green.

• Range: C2:C11
• Condition: Cell value > 75
• Style: Green background

B. Highlight Low Scores

Example: Highlight marks below 40 in red.

• Condition: Cell value < 40
• Style: Red background, white font

C. Highlight Duplicate Names

• Range: A2:A11
• Formula: COUNTIF($A$2:$A$11;A2)>1
• Style: Yellow background

D. Highlight Old Submission Dates

• Range: E2:E11
• Formula: E2< TODAY()
• Style: Red background

3️⃣ Formula-based Conditional Formatting Examples

A. Highlight Alternate Rows

• Range: A2:E11
• Formula: MOD(ROW();2)=0
• Style: Light gray background

B. Highlight Top 3 Marks

• Range: C2:C11
• Formula: C2>=LARGE($C$2:$C$11;3)
• Style: Green background, bold font

C. Highlight Students Matching a Given Name

• Type the name in G1.
• Range: A2:A11
• Formula: A2=$G$1
• Style: Light blue background

4️⃣ Summary Table

Conclusion

Conditional formatting makes it easier to analyze and visualize data at a glance. Using the above examples, users can color-code marks, detect duplicates, and visually enhance spreadsheets for better decision-making.