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
Khup students ekach range var 5-6 rules lavtat aani mag kontya rule cha rang disato he kalat nahi. Manage Rules madhe rules chi order bagha – varcha rule aadhi lagto – aani garaj asel tar Stop If True vapra. Kami rang, jast arth: 2-3 rules purese aahet.
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.