Number formats (currency, percentage, date) in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to format the number in cell A1 as currency in Excel.

Excel

Select cell A1 and apply the [1] format.

Drag options to blanks, or click blank then click option'

ADate

BPercentage

CCurrency

DText

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing Percentage or Date format instead of Currency.

Using Text format which does not format numbers.

To show a number as money, use the Currency format. It adds a dollar sign and two decimals.

2fill in blank

medium

Complete the formula to calculate 20% of the value in cell B2.

Excel

=B2 * [1]

Drag options to blanks, or click blank then click option'

A20

B200

C2

D0.2

Attempts:

3 left

💡 Hint

Common Mistakes

Using 20 instead of 0.2, which multiplies by 20 times.

Using 2 or 200 which are incorrect multipliers.

To calculate 20%, multiply by 0.2 (which is 20 divided by 100).

3fill in blank

hard

Fix the error in the formula to convert the text date in cell C3 to a proper date format.

Excel

=DATEVALUE([1])

Drag options to blanks, or click blank then click option'

AC3

B"C3"

CDATE(C3)

DTEXT(C3)

Attempts:

3 left

💡 Hint

Common Mistakes

Using "C3" which is a text string, not the cell value.

Using DATE or TEXT functions incorrectly here.

DATEVALUE needs a cell reference or text string representing a date. Using C3 (without quotes) passes the cell value.

4fill in blank

hard

Fill in the blank to create a formula that formats the value in D4 as a percentage with one decimal place.

Excel

=TEXT(D4, [1])

Drag options to blanks, or click blank then click option'

A"%"

B"0.0%"

C"0.0"

D"%0.0"

Attempts:

3 left

💡 Hint

Common Mistakes

Using "0.0" formats as decimal, not percent.

Using invalid formats like "%" or "%0.0".

The TEXT function uses "0.0%" to format as percentage with one decimal. The format code multiplies by 100 and adds the % sign.

5fill in blank

hard

Complete the formula to format the date in cell E1 as 'MM/DD/YYYY'.

Excel

=TEXT(E1, [1])

Drag options to blanks, or click blank then click option'

A"mm/dd/yyyy"

B"m/d/yy"

C"yyyy-mm-dd"

D"dd/mm/yyyy"

Attempts:

3 left

💡 Hint

Common Mistakes

Using "dd/mm/yyyy" (day/month order).

Using ISO "yyyy-mm-dd".

Using short formats like "m/d/yy".

Excel's TEXT function uses "mm/dd/yyyy": 'mm' for month, 'dd' for day, 'yyyy' for year.

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