2.6 Conditional Formatting: Highlight Rules and Top/Bottom
Home › Styles › Conditional Formatting formats cells automatically based on their values.
| Menu | Rules | Example on our data |
|---|---|---|
| Highlight Cells Rules | Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring, Duplicate Values | Delivery Mins > 15 in light red |
| Top/Bottom Rules | Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, Below Average | Top 10 orders by Amount in green |
About this data: Teaching sample for a fictional shop “GreenField Dairy”. Figures and names (e.g. Meera Joshi) are made up for this lesson on conditional formatting highlight rules and top.
Steps in Excel
- Select M2:M5000 (Delivery Mins).
- Home › Styles › Conditional Formatting › Highlight Cells Rules › Greater Than… ›
15› Light Red Fill with Dark Red Text › OK. - Select the Amount column › Conditional Formatting › Top/Bottom Rules › Top 10 Items… › change 10 to 5 › choose a green fill.
- Select Order IDs › Highlight Cells Rules › Duplicate Values… to spot repeated orders.
- Conditional Formatting › Manage Rules… to edit, reorder or delete rules; Clear Rules to remove.
Worked example. Ravina highlights every Delivered order that took more than 15 minutes in Nagpur (Sitabuldi traffic!). One look at the sheet shows the late deliveries in red.
Ravindra Bagale's Tip
Many students put 5–6 rules on the same range, and then can't tell which rule's colour is showing. Check the order of rules in Manage Rules – the rule at the top applies first – and use Stop If True when needed. Fewer colours, more meaning: 2–3 rules are enough.
Ravindra Bagale's Tip – मराठी
बरेच students एकाच range वर 5-6 rules लावतात आणि मग कोणत्या rule चा रंग दिसतोय ते कळत नाही. Manage Rules मध्ये rules ची order बघा – वरचा rule आधी लागतो – आणि गरज असेल तर Stop If True वापरा. कमी रंग, जास्त अर्थ: 2-3 rules पुरेसे आहेत.
Ravindra Bagale's Tip – हिंदी
बहुत से students एक ही range पर 5-6 rules लगा देते हैं और फिर समझ नहीं आता कि किस rule का रंग दिख रहा है. Manage Rules में rules का order देखो – ऊपर वाला rule पहले लगता है – और ज़रूरत हो तो Stop If True इस्तेमाल करो. कम रंग, ज़्यादा मतलब: 2-3 rules काफ़ी हैं.
Practice task
Highlight: Amount above average (green), Delivery Mins above 15 (red), and duplicate Order IDs (yellow). Open Manage Rules and change the order of rules.
Loading practice tasks...
- Pass or fail (Easy): "Pass" if B2 is at least 40, else "Fail".
- Total (Easy): B2 plus C2.
- Passed score 1 (Easy): How many in B2:B11 are 40 or more?
- Passed both (Medium): TRUE if B3 and C3 are both at least 40.
- Passed either (Medium): TRUE if B4 or C4 is at least 75.
- Grade (Medium): Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F".
- Eligible (Medium): "Eligible" if D5 is at least 75 and B5 at least 40, else "Not eligible".
- Count passed both (Hard): How many have both scores at least 40?
- Pass percentage (Hard): Share passing score 1, rounded to 2 decimals.
- Nested IF band (Hard): "High" if C6 ≥ 75, "Mid" if ≥ 50, else "Low", using nested IF.
- Top scorer (Hard): Name with the highest score 1.
Worked example — conditional formatting highlight rules and top
Why
Aamir Khan in Pune and Oliver Bennett abroad both need a clear, repeatable way to use conditional formatting highlight rules and top on a HomeNest Supplies sheet without breaking totals.
What
Conditional Formatting Highlight Rules And Top is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for conditional formatting highlight rules and top.
- Enter a small made-up value for Aamir Khan using £ where money appears.
- Add a second value for Oliver Bennett using € so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once conditional formatting highlight rules and top is correct on one row, copy the same pattern down the column.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
