Number formats (currency, percentage, date) in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the displayed value with currency format?

Cell A1 contains the number 1234.567. The cell is formatted as Currency with 2 decimal places and the dollar sign ($). What is the displayed value in A1?

A $1,234.57

B 1234.57

C $1234.567

D 1234.567

💡 Hint

Currency format rounds to 2 decimals and adds the currency symbol.

Currency format rounds the number to 2 decimal places and adds the dollar sign with comma separators.

📊 Formula Result

intermediate

What is the displayed value with percentage format?

Cell B2 contains the number 0.256. The cell is formatted as Percentage with 1 decimal place. What is the displayed value in B2?

A 0.3%

B 25.6%

C 0.256%

D 256%

💡 Hint

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

0.256 as a percentage with 1 decimal place is 25.6%.

📊 Formula Result

advanced

What date is shown with custom date format?

Cell C3 contains the date value 44561 (Excel serial date number). The cell is formatted with the custom date format "dddd, mmmm dd, yyyy". What is the displayed value?

A 06/12/2021

B 12/06/2021

C Monday, December 06, 2021

D Monday, 12 06, 2021

💡 Hint

Excel date 44561 corresponds to December 6, 2021.

The custom format shows full weekday name, full month name, day with leading zero, and full year.

❓ Function Choice

advanced

Which formula converts a decimal to a percentage string with 2 decimals?

You want to convert the decimal number in cell D4 to a text string showing percentage with exactly 2 decimal places (e.g., 0.1234 → "12.34%"). Which formula will do this correctly?

A =TEXT(D4, "0.00%")

B =D4*100 & "%"

C =CONCATENATE(D4*100, "%")

D =ROUND(D4, 2) & "%"

💡 Hint

Use TEXT function with percentage format code.

TEXT with "0.00%" formats the number as percentage with 2 decimals and adds % sign.

🎯 Scenario

expert

You have a list of prices in column A and want to show them as currency in column B with a formula. Which formula in B2 correctly converts the number in A2 to a currency string with $ and 2 decimals?

Given prices in A2:A10 as numbers, you want column B to show prices as text with dollar sign, commas, and 2 decimals (e.g., 1234.5 → "$1,234.50"). Which formula should you enter in B2 and copy down?

A =FIXED(A2, 2)

B ="$" & ROUND(A2, 2)

C =CONCATENATE("$", A2)

D =TEXT(A2, "$#,##0.00")

💡 Hint

Use TEXT function with currency format code for proper commas and decimals.

TEXT with "$#,##0.00" formats number with dollar sign, commas, and 2 decimals as text.

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