Custom formula-based rules in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to highlight sales data that meet specific conditions using custom formula-based rules in Google Sheets.

📊 Data: You have a table with sales data including columns for Salesperson, Region, Sales Amount, and Sales Date.

🎯 Deliverable: Create conditional formatting rules using custom formulas to highlight sales over $5000 and sales made in the East region.

Sample Data

Salesperson	Region	Sales Amount	Sales Date
Alice	East	4500	2024-05-01
Bob	West	5200	2024-05-03
Charlie	East	7000	2024-05-05
Diana	South	3000	2024-05-07
Edward	East	4800	2024-05-09
Fiona	West	6000	2024-05-11
George	North	5500	2024-05-13
Hannah	East	3500	2024-05-15

1

Step 1: Select the range of sales data to apply conditional formatting. Select cells C2:C9 for Sales Amount.

No formula needed for selection.

Expected Result

Cells C2 to C9 are selected.

2

Step 2: Create a custom formula-based conditional formatting rule to highlight sales amounts greater than 5000.

=C2>5000

Expected Result

Cells with sales amounts 5200, 7000, 6000, and 5500 are highlighted.

3

Step 3: Select the entire data range A2:D9 to apply another conditional formatting rule for the East region.

No formula needed for selection.

Expected Result

Cells A2 to D9 are selected.

4

Step 4: Create a custom formula-based conditional formatting rule to highlight rows where the Region is East.

=$B2="East"

Expected Result

Rows 2, 3, 5, and 8 are highlighted because their Region is East.

Final Result

Sales Data Highlighting

+------------+--------+--------------+------------+
| Salesperson| Region | Sales Amount | Sales Date |
+------------+--------+--------------+------------+
| Alice      | East   | 4500         | 2024-05-01 |
| Bob        | West   | 5200 *       | 2024-05-03 |
| Charlie    | East   | 7000 *       | 2024-05-05 |
| Diana      | South  | 3000         | 2024-05-07 |
| Edward     | East   | 4800         | 2024-05-09 |
| Fiona      | West   | 6000 *       | 2024-05-11 |
| George     | North  | 5500 *       | 2024-05-13 |
| Hannah     | East   | 3500         | 2024-05-15 |
+------------+--------+--------------+------------+

* Sales Amount cells highlighted for > $5000
Rows with Region 'East' highlighted across all columns

✓Sales amounts greater than $5000 are clearly highlighted for quick identification.

✓All sales records from the East region are highlighted across the entire row for easy regional analysis.

Bonus Challenge

Create a custom formula-based rule to highlight sales made in May 2024 that are below $4000.

Show Hint

Use the AND function with conditions on the Sales Date column and Sales Amount column, e.g., =AND(MONTH(D2)=5, YEAR(D2)=2024, C2<4000)