Mada za sehemu hiiManaging spreadsheet and data presentationMada 4
Implementing Conditions and Data
In spreadsheet work, conditions allow you to automatically apply formatting or calculate values based on specific rules. For example, you can highlight all sales below a target in red, or automatically calculate bonuses for employees who meet performance targets. This makes your spreadsheets smarter and helps you analyze data quickly without manual checking.

Conditional formatting automatically changes how a cell looks when its contents meet a condition you specify. The formatting is applied only when the condition is TRUE.
How to Apply Conditional Formatting
- Select the cell or range of cells you want to format.
- Go to Home tab → Conditional Formatting.
- Choose a rule type or create a custom rule.
- Set your condition and choose the formatting style (color, font, border).
- Click OK.
1.1 Highlight Cells Rules
This rule highlights cells based on their values. It works with numbers, text, and dates.
Example: Highlight all sales amounts below 500,000 TZS in red.
- Select the sales column.
- Go to Home → Conditional Formatting → Highlight Cells Rules → Less Than.
- Enter
500000and choose Red Fill. - Click OK.
All cells with values less than 500,000 TZS will have a red background.
Other options include:
- Greater Than – highlight values above a threshold
- Between – highlight values within a range
- Text that Contains – highlight cells with specific text
- Duplicate Values – highlight repeated entries
1.2 Top/Bottom Rules
Quickly find the highest or lowest values in your data.
- Top 10 Items – highlights the top values (you can change 10 to any number)
- Bottom 10 Items – highlights the lowest values
- Above Average – highlights values above the mean
- Below Average – highlights values below the mean
Example: Highlight the top 5 performers in a sales team.
- Select the sales column.
- Go to Home → Conditional Formatting → Top/Bottom Rules → Top 10 Items.
- Change 10 to 5 and choose a format (e.g., green fill).
- Click OK.
1.3 Data Bars
Data bars are mini bar charts inside cells. Longer bars represent larger values.
Example: Add data bars to visualize monthly expenses.
- Select the expense amounts.
- Go to Home → Conditional Formatting → Data Bars.
- Choose a color (blue or green).
- The cells will show colored bars proportional to their values.
1.4 Color Scales
Color scales apply a gradient of two or three colors based on values. Higher values get one color, middle values another, and lowest values a third.
- Red-Yellow-Green – common for showing good (green) to bad (red) performance
- Blue-White-Red – often used for temperature-like data
1.5 Icon Sets
Icon sets add symbols (arrows, traffic lights, stars) to cells based on values.
Example: Use traffic lights to show sales performance.
- Select the sales data.
- Go to Home → Conditional Formatting → Icon Sets.
- Choose Traffic Lights or Directional Arrows.
- Excel automatically assigns icons based on value thresholds.
When predefined rules don't meet your needs, create a custom rule.
- Go to Home → Conditional Formatting → New Rule.
- Choose a rule type:
- Format all cells based on their values (for data bars, color scales, icon sets)
- Format only cells that contain specific content
- Use a formula to determine which cells to format
- Set your condition and click Format to choose formatting.
- Click OK to apply.
Example: Highlight students who scored exactly 75 marks.
- Select the scores column.
- Go to Conditional Formatting → New Rule.
- Choose Format only cells that contain.
- Set: "Cell Value" → "equal to" →
75. - Choose Yellow Fill and click OK.
You can highlight empty cells to identify missing data.
Example: Highlight empty cells in an attendance sheet.
- Select the range.
- Go to Home → Conditional Formatting → Highlight Cells Rules → More Rules.
- Select Blanks from the dropdown.
- Click Format, choose a color (e.g., light red), and click OK.
- Click OK again.
All blank cells will be highlighted.
You can compare values in two columns and highlight matches or differences.
4.1 Compare Row by Row
Highlight cells in two columns that have the same value.
Example: Compare prices in Shop A and Shop B columns.
- Select both columns (A and B).
- Go to Home → Conditional Formatting → New Rule.
- Select Use a formula to determine which cells to format.
- Enter the formula:
=$A2<>$B2(highlights differences). - Click Format, choose red fill, then OK.
- Click OK.
Cells where values differ will be highlighted.
4.2 Highlight Duplicates
To highlight duplicate values across both columns:
- Select both columns.
- Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
- Choose a format and click OK.
You can format one column based on values in another column.
Example: Highlight names of students who scored above 75 in the score column.
- Select the Names column (column A).
- Go to Home → Conditional Formatting → New Rule.
- Select Use a formula to determine which cells to format.
- Enter:
=$B2>=75(assuming scores are in column B, starting from row 2). - Click Format, choose green fill, then OK.
- Click OK.
Names of students with scores 75 and above will be highlighted.
6.1 IF Function
The IF function returns one value if a condition is TRUE, and another if FALSE.
Syntax:
=IF(condition, value_if_true, value_if_false)
Example: Calculate bonus for salespeople.
In cell C2 (Bonus):
=IF(B2>=1000000, B2*0.1, 0)
If sales (B2) are 1,000,000 TZS or more, calculate 10% bonus; otherwise, give 0.
6.2 COUNTIF Function
COUNTIF counts cells that meet a single condition.
Syntax:
=COUNTIF(range, criteria)
Example: Count how many students passed (score >= 50).
=COUNTIF(B2:B20, ">=50")
6.3 SUMIF Function
SUMIF adds cells that meet a condition.
Syntax:
=SUMIF(range, criteria, sum_range)
Example: Total sales for Region A.
=SUMIF(A2:A10, "Region A", B2:B10)
This sums values in B2:B10 where the corresponding A2:A10 cell equals "Region A".
Scenario: You have a shop inventory list with product names in column A, quantities in stock in column B, and reorder points in column C. Products with stock below the reorder point need restocking.
Step 1: Apply conditional formatting to highlight items needing reorder.
- Select column B (quantities in stock).
- Go to Home → Conditional Formatting → Highlight Cells Rules → Less Than.
- Enter the cell reference of the reorder point (e.g., C2).
- Choose Red Fill and click OK.
Step 2: Use a formula to show "Reorder" or "OK" in column D.
In cell D2:
=IF(B2<C2, "Reorder", "OK")
Copy this formula down. Items needing reorder will show "Reorder" in red.
In Tanzania, shop owners and market vendors can use conditional formatting to track inventory. For example, a duka owner in Dar es Salaam can apply red highlighting to items like rice, cooking oil, or soap when stock falls below the reorder level, and use the IF function to automatically calculate restocking costs based on quantities needed. This helps prevent running out of fast-moving goods during peak business hours like weekend markets or holidays.
Jinsi utakavyotathminiwaTathmini ya mchakato · Tathmini ya matokeo · Tathmini ya maarifa
Tathmini ya mchakato
The student should be able to:
- Write and apply conditional formulas (e.g., IF, COUNTIF, AVERAGEI F)
- Use conditional formatting to highlight specific data points.
- Validate data using custom rules and input restrictions.
- Organize and analyze data sets with logical conditions.
Tathmini ya matokeo
Conditional formulas are correctly applied to data.
Tathmini ya maarifa
Methods used: The trainee should explain how to:
- Use conditional formulas to automate calculations and data analysis.
- Apply rules for conditional formatting to emphasize trends or outliers.
Principles: The student should explain the principle of:
- Logical operations and their application in spreadsheet conditions.
- Data validation and error prevention techniques.
- Effective use of formatting to convey data insights.
Theories: The trainee should explain: The trainee should explain:
- Conditional logic and its applications in decision-making.
- The role of data visualisation in enhancing data interpretation
Detailed knowledge about:
- Conditional formulas and their real-world applications.
- Common errors in conditional logic and how to troubleshoot them
Swali
What is conditional formatting in Microsoft Excel?
Ingia ili kuwasilisha jibu lako na lihesabiwe katika umahiri wako.
Ingia ili kufanya mazoeziMwalimu
Umekwama? Niulize chochote kuhusu mada hii.
Ingia ili kumuuliza Mwalimu wa AI wa Sonza kuhusu swali hili.
Ingia ili kuulizaMajadiliano
Hakuna maswali bado