MO-201 · Question #10
On the "Popular Videos" worksheet, for cells B4:C17, create a conditional formatting rule that displays the five lowest values in bold Dark Red font.
Excel Conditional Formatting: Five Lowest Values Overall Goal The task highlights the bottom 5 values in a data range visually, so a viewer can instantly identify the least-performing entries without sorting or scanning. Conditional formatting is the correct approach because it a
Question
Exhibit
Explanation
Excel Conditional Formatting: Five Lowest Values
Overall Goal
The task highlights the bottom 5 values in a data range visually, so a viewer can instantly identify the least-performing entries without sorting or scanning. Conditional formatting is the correct approach because it applies formatting dynamically - if the data changes, the highlighting automatically updates to reflect the new bottom 5.
Step-by-Step Breakdown
1. Select B4:C17 first
You must select the target range before opening the conditional formatting dialog, because the rule is scoped to whatever is selected at the time you create it. If you forget this step and apply the rule to the wrong range (or the entire sheet), the formatting will appear in unintended cells.
2. Home tab → Conditional Formatting → Top/Bottom Rules → Bottom 10 Items
This is the correct rule type for "N lowest values." Excel calls this a Top/Bottom rule (not a "highlight cells" rule, which is for specific values or text). Choosing the wrong rule category - such as "Highlight Cell Rules" - gives you no option to target a count of lowest values.
3. Change the count from 10 to 5
The default is 10. You must explicitly change this to 5 to match the requirement. Leaving it at 10 would flag too many cells and is technically wrong.
4. Set the format to Bold + Dark Red font (Custom Format)
The dropdown in the dialog offers preset formats (e.g., "Light Red Fill"). Since "bold Dark Red font" is not a preset, you must click Custom Format…, navigate to the Font tab, set the color to Dark Red, and check Bold. Skipping the custom format and accepting a preset means the formatting won't match the requirement - even if the right cells are highlighted.
5. Click OK to confirm
This finalizes and applies the rule. Closing the dialog any other way (e.g., pressing Escape) discards all changes.
What Goes Wrong If Steps Are Skipped
| Mistake | Consequence |
|---|---|
| Wrong range selected | Rule applies to wrong cells |
| Used "Highlight Cell Rules" instead | No "bottom N" option available |
| Left count at 10 | 10 cells highlighted instead of 5 |
| Used a preset format | Color/weight won't match requirements |
Memory Tip
Think of the path as "Bottom → Count → Look":
- Bottom: Top/Bottom Rules → Bottom 10 Items
- Count: Change 10 → 5
- Look: Custom Format → Font → Bold, Dark Red
Always select the range before you open any formatting dialog - this applies universally in Excel.
Topics
Community Discussion
No community discussion yet for this question.