Why formatting communicates data meaning in Google Sheets - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants a clear sales report that highlights important data using formatting so the team can quickly understand sales performance.

📊 Data: You have monthly sales data for different products including units sold, revenue, and profit margin.

🎯 Deliverable: Create a sales report sheet that uses formatting like colors, bold text, and number formats to communicate which products are performing well or poorly.

Sample Data

Product	Units Sold	Revenue	Profit Margin (%)
Widget A	120	2400	0.25
Widget B	80	1600	0.15
Widget C	200	5000	0.30
Widget D	50	1000	0.10
Widget E	150	3750	0.28
Widget F	90	1800	0.20

1

Step 1: Enter the sample data into your Google Sheets starting from cell A1 with headers in row 1.

No formula needed, just type or paste the data as shown.

Expected Result

Data table with columns Product, Units Sold, Revenue, Profit Margin (%) filled correctly.

2

Step 2: Format the Revenue column as currency to show dollar signs and two decimals.

Select column C, then Format > Number > Currency.

Expected Result

Revenue values show as $2,400.00, $1,600.00, etc.

3

Step 3: Format the Profit Margin (%) column as percentage with no decimals.

Select column D, then Format > Number > Percent, then decrease decimal places to zero.

Expected Result

Profit Margin values show as 25%, 15%, 30%, etc.

4

Step 4: Apply conditional formatting to highlight high profit margins (above 25%) in green.

Select D2:D7, then Format > Conditional formatting, set rule: Greater than 0.25, format fill color green.

Expected Result

Cells with profit margin 28% and 30% are highlighted green.

5

Step 5: Apply conditional formatting to highlight low profit margins (below 15%) in red.

Select D2:D7, then Format > Conditional formatting, set rule: Less than 0.15, format fill color red.

Expected Result

Cell with profit margin 10% is highlighted red.

6

Step 6: Bold the header row to make it stand out.

Select row 1, then click Bold button or press Ctrl+B.

Expected Result

Headers Product, Units Sold, Revenue, Profit Margin (%) appear bold.

7

Step 7: Apply a light gray fill color to alternate rows for easier reading.

Select A2:D7, then Format > Alternating colors, choose a style with light gray on alternate rows.

Expected Result

Rows 2,4,6 have white background; rows 3,5,7 have light gray background.

8

Step 8: Use bold and green text for Revenue values above $3,000 to highlight strong sales.

Select C2:C7, Format > Conditional formatting, Custom formula: =C2>3000, set text color green and bold font style.

Expected Result

Revenue values $5,000 and $3,750 appear bold and green.

Final Result

Product    Units Sold  Revenue    Profit Margin (%)
-------------------------------------------------
Widget A   120         $2,400.00  25%
Widget B   80          $1,600.00  15%
Widget C   200         $5,000.00  30%  <-- green fill
Widget D   50          $1,000.00  10%  <-- red fill
Widget E   150         $3,750.00  28%  <-- green fill
Widget F   90          $1,800.00  20%

Rows 3 and 5 revenue bold green
Alternate rows shaded light gray

✓Products Widget C and Widget E have high profit margins and strong revenue, highlighted in green.

✓Widget D has a low profit margin, highlighted in red, indicating poor performance.

✓Formatting helps quickly identify which products are doing well or need attention.

Bonus Challenge

Add a new column 'Performance' that shows 'Good' if profit margin is above 25%, 'Average' if between 15% and 25%, and 'Poor' if below 15%. Use conditional formatting to color these labels green, yellow, and red respectively.

Show Hint

Use the IF function with nested conditions: =IF(D2>0.25,"Good",IF(D2>=0.15,"Average","Poor")) and apply conditional formatting rules based on text.