Bird
Raised Fist0
Excelspreadsheet~10 mins

Number formats (currency, percentage, date) in Excel - Cell-by-Cell Formula Trace

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Sample Data

This table shows product prices in column A, tax rates as decimals in column B, and dates in column C.

CellValue
A1Price
A225
A340
B1Tax Rate
B20.08
B30.15
C1Date
C22024-06-01
C32024-12-25
Formula Trace
=A2*B2
Step 1: A2 * B2
Step 2: 25 * 0.08
Cell Reference Map
    A      B      C
1 |Price |Tax Rate| Date    
2 |  25  | 0.08   |2024-06-01
3 |  40  | 0.15   |2024-12-25

Formula uses A2 and B2 cells for calculation.
The formula multiplies the value in cell A2 (Price) by the value in B2 (Tax Rate).
Result
    A      B      C      D
1 |Price |Tax Rate| Date    |Tax Amount
2 |  25  | 0.08   |2024-06-01|  2.00   
3 |  40  | 0.15   |2024-12-25|         

Cell D2 shows the tax amount formatted as currency ($2.00).
The result of the formula is 2, shown in cell D2. It is formatted as currency to display $2.00.
Sheet Trace Quiz - 3 Questions
Test your understanding
What does the formula =A2*B2 calculate?
AThe tax amount for the price in A2
BThe total price including tax
CThe date in cell C2
DThe percentage of the price
Key Result
Multiplying a price by a decimal tax rate calculates the tax amount.

Practice

(1/5)
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