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 |
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.