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.

Practice

1. What is the main purpose of rules-based formatting in Excel?

easy

A. To change cell colors based on specific conditions

B. To add formulas to cells automatically

C. To sort data alphabetically

D. To protect cells from editing

Solution

1. Step 1: Understand rules-based formatting

   Rules-based formatting changes how cells look based on rules you set.

2. Step 2: Identify the main effect

   The main effect is changing cell colors or styles to highlight data patterns.

3. Final Answer:

   To change cell colors based on specific conditions -> Option A

4. Quick Check:

   Rules-based formatting = change cell appearance [OK]

Hint: Think: formatting changes appearance by rules [OK]

Common Mistakes:

• Confusing formatting with sorting
• Thinking it adds formulas
• Assuming it protects cells

2. Which of these is the correct way to start creating a new rule for conditional formatting in Excel?

easy

A. Select cells, then go to Home > Conditional Formatting > New Rule

B. Right-click cell and choose Insert Formula

C. Use Data > Sort to create a rule

D. Go to Review > Protect Sheet

Solution

1. Step 1: Recall how to create conditional formatting

   You start by selecting cells, then use the Home tab's Conditional Formatting menu.

2. Step 2: Identify the correct menu path

   The correct path is Home > Conditional Formatting > New Rule.

3. Final Answer:

   Select cells, then go to Home > Conditional Formatting > New Rule -> Option A

4. Quick Check:

   New rule path = Home > Conditional Formatting > New Rule [OK]

Hint: Look under Home tab for Conditional Formatting [OK]

Common Mistakes:

• Trying to insert formulas from right-click menu
• Using Data > Sort instead of formatting
• Confusing with sheet protection options

3. Given cells A1 to A5 contain values: 10, 20, 30, 40, 50. You apply a rule: Format cells greater than 25 with red fill. Which cells will be highlighted?

medium

A. A1, A2

B. A1, A3, A5

C. All cells

D. A3, A4, A5

Solution

1. Step 1: Identify values greater than 25

   Values are 10, 20, 30, 40, 50. Values greater than 25 are 30, 40, 50.

2. Step 2: Match cells with those values

   Cells A3, A4, and A5 contain 30, 40, and 50 respectively.

3. Final Answer:

   A3, A4, A5 -> Option D

4. Quick Check:

   Values > 25 = A3, A4, A5 [OK]

Hint: Check which values meet the condition > 25 [OK]

Common Mistakes:

• Including values equal to 25
• Highlighting wrong cells by position
• Assuming all cells highlight

4. You set a conditional formatting rule with formula =A1>100 but no cells get highlighted even though some values are above 100. What is the likely problem?

medium

A. The formula should use relative reference like =A$1>100

B. The formula should be =A1>=100 to include 100

C. The rule was applied to the wrong cell range

D. Conditional formatting does not work with formulas

Solution

1. Step 1: Check formula correctness

   The formula =A1>100 is valid and should highlight cells with values above 100.

2. Step 2: Consider rule application range

   If no cells highlight, likely the rule was applied to a range that does not include those cells.

3. Final Answer:

   The rule was applied to the wrong cell range -> Option C

4. Quick Check:

   Wrong range = no highlight [OK]

Hint: Check if rule covers the correct cells [OK]

Common Mistakes:

• Thinking formula syntax is wrong
• Assuming conditional formatting ignores formulas
• Confusing relative and absolute references here

5. You want to highlight cells in column B that are above the average of the entire column B. Which conditional formatting formula should you use for the range B1:B10?

hard

A. =B1>AVERAGE(B1:B10)

B. =B1>AVERAGE($B$1:$B$10)

C. =B1>AVERAGE(B$1:B$10)

D. =B1>AVERAGE(B1:B$10)

Solution

1. Step 1: Understand the need for fixed range in average

   The average should always calculate over the fixed range B1:B10, so use absolute references $B$1:$B$10.

2. Step 2: Use relative reference for cell comparison

   The cell reference B1 should be relative so it adjusts for each cell in the range.

3. Final Answer:

   =B1>AVERAGE($B$1:$B$10) -> Option B

4. Quick Check:

   Fixed average range + relative cell = correct formula [OK]

Hint: Use $ to fix average range, keep cell relative [OK]

Common Mistakes:

• Not fixing average range with $ signs
• Fixing cell reference instead of range
• Using inconsistent mixed references