Why rules-based formatting highlights patterns in Excel - Business Case Study

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to quickly spot sales trends and patterns in the monthly sales data to help plan inventory.

📊 Data: You have a table with sales data for different products over 6 months. Each row shows a product and its sales for each month.

🎯 Deliverable: Create a spreadsheet that uses rules-based formatting to highlight high and low sales values so patterns stand out visually.

Sample Data

Product	Jan	Feb	Mar	Apr	May	Jun
Apples	120	135	150	160	145	170
Bananas	80	90	85	95	100	110
Cherries	200	210	190	220	230	240
Dates	50	55	60	65	70	75
Elderberries	30	25	35	40	45	50
Figs	90	95	100	105	110	115
Grapes	160	170	165	175	180	185
Honeydew	70	75	80	85	90	95

1

Step 1: Select the sales data range from B2 to G9 (all months for all products).

Select cells B2:G9.

Expected Result

The sales numbers for all products and months are selected.

2

Step 2: Apply conditional formatting to highlight high sales values with a green fill.

Use Home > Conditional Formatting > Color Scales > Green-Yellow-Red color scale.

Expected Result

Cells with higher sales values show green shades, lower values show red shades, and middle values show yellow.

3

Step 3: Add a new rule to highlight sales greater than 200 with bold text and dark green fill.

Conditional Formatting > New Rule > Use a formula: =B2>200, format with bold font and dark green fill.

Expected Result

Sales numbers above 200 stand out with bold text and dark green background.

4

Step 4: Add another rule to highlight sales below 60 with red fill and white font.

Conditional Formatting > New Rule > Use a formula: =B2<60, format with red fill and white font.

Expected Result

Low sales numbers below 60 are clearly visible with red background and white text.

5

Step 5: Check that the formatting applies correctly across all months and products.

Verify conditional formatting rules apply to range B2:G9.

Expected Result

High sales are green and bold, low sales are red and white, and other values have color scale shading.

Final Result

Product   Jan  Feb  Mar  Apr  May  Jun
-------------------------------------
Apples   120  135  150  160  145  170
Bananas   80   90   85   95  100  110
Cherries 200  210  190  220  230  240
Dates     50   55   60   65   70   75
Elderberries 30  25   35   40   45   50
Figs      90   95  100  105  110  115
Grapes   160  170  165  175  180  185
Honeydew  70   75   80   85   90   95

(High sales >200 are bold and dark green)
(Low sales <60 are red fill with white font)
(Other sales shaded green to red by value)

✓Cherries consistently have the highest sales, often above 200.

✓Elderberries and Dates have the lowest sales, often below 60.

✓Apples and Grapes show steady mid-to-high sales.

✓Rules-based formatting makes it easy to spot these patterns quickly.

Bonus Challenge

Create a rule to highlight any month where sales increased compared to the previous month for each product.

Show Hint

Use a formula like =C2>B2 applied to the range starting from Feb column to compare each month with the previous one.