Conditional formatting basics in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to highlight sales figures that are below the target to quickly identify underperforming products.

📊 Data: You have a table with product names and their sales amounts for the month.

🎯 Deliverable: Create a spreadsheet where sales below the target value are highlighted in red using conditional formatting.

Sample Data

Product	Sales
Apples	1200
Bananas	800
Cherries	1500
Dates	700
Elderberries	1300
Figs	600
Grapes	1100
Honeydew	900

1

Step 1: Select the range of sales data to apply conditional formatting.

Select cells B2:B9.

Expected Result

Cells B2 to B9 are selected.

2

Step 2: Open the Conditional Formatting menu.

Go to Home tab > Conditional Formatting > New Rule.

Expected Result

New Formatting Rule dialog box opens.

3

Step 3: Create a rule to highlight sales less than the target value of 1000.

Choose 'Format only cells that contain'. Set rule to 'Cell Value' less than 1000.

Expected Result

Rule is set to highlight cells with values below 1000.

4

Step 4: Set the formatting style for the rule.

Click Format > Fill tab > select red color > OK.

Expected Result

Cells meeting the condition will be filled with red color.

5

Step 5: Apply and close the conditional formatting rule.

Click OK to apply the rule and close the dialog.

Expected Result

Sales values below 1000 are highlighted in red in the selected range.

Final Result

Product     Sales
-------------------
Apples     1200
Bananas    800  <-- highlighted red
Cherries   1500
Dates      700  <-- highlighted red
Elderberries 1300
Figs       600  <-- highlighted red
Grapes     1100
Honeydew   900  <-- highlighted red

✓Sales below 1000 are clearly visible with red highlight.

✓Bananas, Dates, Figs, and Honeydew are underperforming products.

Bonus Challenge

Add a second conditional formatting rule to highlight sales above 1300 in green.

Show Hint

Create a new rule with 'Cell Value' greater than 1300 and set fill color to green.