Number formats (currency, percentage, date) in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a financial assistant at a retail company.

📋 Request: Your manager wants a clear sales report showing sales amounts in currency, discount rates as percentages, and sale dates in a readable date format.

📊 Data: You have a table with product names, sales amounts as numbers, discount rates as decimals, and sale dates as numbers.

🎯 Deliverable: Create a formatted sales report where sales amounts show as currency, discount rates as percentages, and sale dates as dates.

Sample Data

Product	Sales Amount	Discount Rate	Sale Date
Notebook	1500	0.1	44927
Pen	300	0.05	44928
Backpack	1200	0.15	44929
Calculator	800	0.2	44930
Desk Lamp	450	0	44931

1

Step 1: Select the 'Sales Amount' column cells.

Apply the currency number format with 2 decimal places and the dollar sign ($).

Expected Result

Sales amounts display as $1,500.00, $300.00, $1,200.00, $800.00, $450.00.

2

Step 2: Select the 'Discount Rate' column cells.

Apply the percentage number format with 0 decimal places.

Expected Result

Discount rates display as 10%, 5%, 15%, 20%, 0%.

3

Step 3: Select the 'Sale Date' column cells.

Apply the short date format (e.g., MM/DD/YYYY).

Expected Result

Sale dates display as 01/01/2023, 01/02/2023, 01/03/2023, 01/04/2023, 01/05/2023.

Final Result

Product    | Sales Amount | Discount Rate | Sale Date 
------------------------------------------------------
Notebook   | $1,500.00    | 10%           | 01/01/2023
Pen        | $300.00      | 5%            | 01/02/2023
Backpack   | $1,200.00    | 15%           | 01/03/2023
Calculator | $800.00      | 20%           | 01/04/2023
Desk Lamp  | $450.00      | 0%            | 01/05/2023

✓Sales amounts are easy to read with currency formatting.

✓Discount rates clearly show as percentages.

✓Sale dates are displayed in a familiar date format.

Bonus Challenge

Format the 'Sales Amount' column to show negative amounts in red with parentheses.

Show Hint

Use the custom number format: "$#,##0.00;[Red]($#,##0.00)"