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)"

Practice

1. Which number format should you use in Excel to display a value as money with a dollar sign and two decimal places?

easy

A. Date format

B. Percentage format

C. Currency format

D. General format

Solution

1. Step 1: Understand the purpose of currency format

   Currency format shows numbers as money, adding a currency symbol like $ and two decimals.

2. Step 2: Identify the correct format for money

   Since money needs a dollar sign and decimals, currency format is the right choice.

3. Final Answer:

   Currency format -> Option C

4. Quick Check:

   Money = Currency format [OK]

Hint: Money values use currency format with $ and decimals [OK]

Common Mistakes:

• Choosing percentage format for money
• Using general format which shows plain numbers
• Selecting date format for currency

2. Which of these is the correct way to format a cell as a percentage in Excel?

easy

A. Right-click cell -> Format Cells -> Date -> Percentage

B. Right-click cell -> Format Cells -> General -> Percentage

C. Right-click cell -> Format Cells -> Currency -> Percentage

D. Right-click cell -> Format Cells -> Number -> Percentage

Solution

1. Step 1: Locate the Format Cells dialog

   Right-clicking a cell and choosing Format Cells opens options to change number formats.

2. Step 2: Select the Percentage category under Number tab

   Percentage is found under Number, not Date or Currency or General.

3. Final Answer:

   Right-click cell -> Format Cells -> Number -> Percentage -> Option D

4. Quick Check:

   Percentage format is under Number tab [OK]

Hint: Percentage format is under Number tab in Format Cells [OK]

Common Mistakes:

• Choosing Date or Currency tab for percentage
• Selecting General which does not format as percentage
• Confusing tabs in Format Cells dialog

3. If cell A1 contains the number 0.25 and is formatted as Percentage, what will the cell display?

medium

A. 25%

B. 2500%

C. 0.25

D. 0.025

Solution

1. Step 1: Understand percentage formatting

   Percentage format multiplies the number by 100 and adds % sign for display.

2. Step 2: Calculate display for 0.25

   0.25 x 100 = 25, so it shows as 25%.

3. Final Answer:

   25% -> Option A

4. Quick Check:

   0.25 as percentage = 25% [OK]

Hint: Percentage format multiplies value by 100 and adds % [OK]

Common Mistakes:

• Showing raw decimal instead of percentage
• Multiplying by 10000 instead of 100
• Confusing decimal and percentage display

4. You entered the date 12/31/2023 in a cell, but it shows as 44926. What is the likely cause and how to fix it?

medium

A. Cell is formatted as General; change format to Date

B. Date entered incorrectly; retype as text

C. Cell is formatted as Currency; change format to Percentage

D. Excel does not support dates; use text format

Solution

1. Step 1: Recognize date serial number display

   Excel stores dates as numbers; 44926 is the serial for 12/31/2023.

2. Step 2: Fix by changing cell format to Date

   Changing format from General to Date shows the date properly.

3. Final Answer:

   Cell is formatted as General; change format to Date -> Option A

4. Quick Check:

   Date serial number shows if format is General [OK]

Hint: Change cell format to Date to show calendar dates [OK]

Common Mistakes:

• Retyping date as text instead of formatting
• Changing to percentage or currency format
• Thinking Excel can't show dates

5. You have a sales report with amounts in column A and a commission rate of 5% in cell B1. Which formula and format combination correctly calculates and displays the commission as currency?

hard

A. =A2*B1 with Percentage format on the formula cell

B. =A2*$B$1 with Currency format on the formula cell

C. =A2+B1 with Currency format on the formula cell

D. =A2*$B$1 with General format on the formula cell

Solution

1. Step 1: Use absolute reference for commission rate

   Using $B$1 locks the commission rate when copying formula down.

2. Step 2: Multiply sales amount by commission rate and format as currency

   Formula =A2*$B$1 calculates commission; Currency format shows money properly.

3. Final Answer:

   =A2*$B$1 with Currency format on the formula cell -> Option B

4. Quick Check:

   Correct formula + Currency format = commission in dollars [OK]

Hint: Use $ for fixed cell and Currency format for money display [OK]

Common Mistakes:

• Not using $ to fix commission cell
• Using addition instead of multiplication
• Formatting result as Percentage or General