Why advanced rules highlight patterns in Google Sheets - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants you to find and highlight sales patterns in the monthly sales data to spot trends and outliers quickly.

📊 Data: You have monthly sales data for different products over six months. The data includes product names and sales numbers for each month.

🎯 Deliverable: Create conditional formatting rules in Google Sheets that highlight sales patterns such as increasing trends, decreasing trends, and unusually high or low sales.

Sample Data

Product	Jan	Feb	Mar	Apr	May	Jun
Apples	120	130	150	160	170	180
Bananas	200	190	180	170	160	150
Cherries	50	55	60	65	70	75
Dates	300	310	290	280	270	260
Elderberries	80	85	90	95	100	105
Figs	150	160	170	180	190	200
Grapes	220	210	230	240	250	260
Honeydew	90	85	80	75	70	65

1

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

No formula needed for selection.

Expected Result

Cells B2:G9 are selected.

2

Step 2: Create a conditional formatting rule to highlight increasing sales trends for each product across months.

Use custom formula: =AND(B2<C2, C2<D2, D2<E2, E2<F2)

Expected Result

Rows where sales increase every month are highlighted (e.g., Apples, Cherries, Elderberries, Figs).

3

Step 3: Create a conditional formatting rule to highlight decreasing sales trends for each product across months.

Use custom formula: =AND(B2>C2, C2>D2, D2>E2, E2>F2)

Expected Result

Rows where sales decrease every month are highlighted (e.g., Bananas, Dates, Honeydew).

4

Step 4: Create a conditional formatting rule to highlight unusually high sales in any month compared to the average sales of that product.

Use custom formula: =B2>AVERAGE($B2:$G2)*1.2

Expected Result

Cells with sales 20% higher than the product's average sales are highlighted (e.g., Grapes in Mar, Apr, May, Jun).

5

Step 5: Create a conditional formatting rule to highlight unusually low sales in any month compared to the average sales of that product.

Use custom formula: =B2<AVERAGE($B2:$G2)*0.8

Expected Result

Cells with sales 20% lower than the product's average sales are highlighted (e.g., Honeydew in Jun).

6

Step 6: Apply distinct colors for each rule to easily see patterns: green for increasing trend, red for decreasing trend, blue for high sales, orange for low sales.

Set colors in conditional formatting rules.

Expected Result

Sales patterns are visually clear with different colors.

Final Result

Product   Jan  Feb  Mar  Apr  May  Jun
-------------------------------------
Apples    120  130  150  160  170  180  (green highlight for increasing trend)
Bananas   200  190  180  170  160  150  (red highlight for decreasing trend)
Cherries   50   55   60   65   70   75  (green highlight for increasing trend)
Dates     300  310  290  280  270  260  (red highlight for decreasing trend)
Elderberries 80   85   90   95  100  105  (green highlight for increasing trend)
Figs      150  160  170  180  190  200  (green highlight for increasing trend)
Grapes    220  210  230  240  250  260  (blue highlight on Mar-Jun for high sales)
Honeydew   90   85   80   75   70   65  (red highlight for decreasing trend, orange on Jun for low sales)

✓Apples, Cherries, Elderberries, and Figs show steady increasing sales over six months.

✓Bananas, Dates, and Honeydew show steady decreasing sales over six months.

✓Grapes have unusually high sales from March to June compared to their average.

✓Honeydew has an unusually low sale in June compared to its average.

Bonus Challenge

Create a new conditional formatting rule to highlight months where sales are equal to the highest sales value for that product.

Show Hint

Use the formula =B2=MAX($B2:$G2) to highlight the highest sales month per product.