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

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the displayed value of a currency formatted cell?

Cell A1 contains the number 1234.567. The cell is formatted as currency with 2 decimal places and the dollar sign ($). What will the cell display?

A $1,234.57

B $1234.567

C 1234.57

D 1234.567

💡 Hint

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

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

📊 Formula Result

intermediate

How does percentage formatting affect a cell value?

Cell B2 contains the number 0.085. The cell is formatted as percentage with 1 decimal place. What is the displayed value?

A 0.085%

B 0.1%

C 8.5%

D 85%

💡 Hint

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

0.085 as a percentage is 8.5%. The format shows one decimal place.

❓ Function Choice

advanced

Which formula converts a date string to a date value in Google Sheets?

You have the text "2024-06-15" in cell C3. Which formula correctly converts it to a date value that Google Sheets recognizes?

A =VALUE("2024-06-15")

B =DATEVALUE(C3)

C =TO_PURE_NUMBER(C3)

D =TEXT(C3, "yyyy-mm-dd")

💡 Hint

DATEVALUE converts a date string to a date number.

DATEVALUE converts a date string to a serial number date value. TEXT returns text, TO_PURE_NUMBER expects a number, VALUE works but only if input is a number or date serial.

🎯 Scenario

advanced

You want to display 0.756 as a percentage with no decimals. Which number format should you apply?

Given the number 0.756 in a cell, you want it to show as "76%" (rounded, no decimals). What number format setting should you use?

A Percentage with 0 decimal places

B Currency with 0 decimal places

C Number with 2 decimal places

D Date format MM/DD/YYYY

💡 Hint

Percentage format multiplies by 100 and shows % sign.

Percentage format with 0 decimals rounds 0.756 to 76%.

❓ data_analysis

expert

Analyzing date format display differences

You enter the date 6/7/2024 in cell D4. The cell is formatted as "MM/DD/YYYY". What will the cell display if you change the format to "DD-MMM-YY"?

A 07-Jul-24

B 06-Jul-24

C 06-Jun-24

D 07-Jun-24

💡 Hint

MM is month, DD is day, MMM is short month name, YY is two-digit year.

The date 6/7/2024 with MM/DD/YYYY format is June 7, 2024 (month/day/year). Changing to DD-MMM-YY shows 07-Jun-24.

Practice

1. What does applying the Percentage number format to a cell do in Google Sheets?

easy

A. It changes the number into a date format.

B. It converts the number into currency with a $ sign.

C. It shows the number as a part of 100 with a % sign.

D. It rounds the number to the nearest whole number.

Solution

1. Step 1: Understand Percentage Format

   The percentage format multiplies the number by 100 and adds a % sign to show parts of 100.

2. Step 2: Compare with Other Formats

   Currency adds a $ sign, date changes to calendar format, rounding changes number value, so these are incorrect.

3. Final Answer:

   It shows the number as a part of 100 with a % sign. -> Option C

4. Quick Check:

   Percentage format = parts of 100 [OK]

Hint: Percentage format shows numbers as % of 100 [OK]

Common Mistakes:

• Thinking percentage changes the number value
• Confusing percentage with currency format
• Assuming percentage rounds numbers

2. Which of the following is the correct way to format a cell as currency in Google Sheets?

easy

A. Select the cell, then click Format > Number > Currency.

B. Type a $ sign before the number in the cell.

C. Use the formula =CURRENCY(A1).

D. Change the cell font color to green.

Solution

1. Step 1: Identify Currency Formatting Method

   In Google Sheets, currency format is applied via the menu: Format > Number > Currency.

2. Step 2: Evaluate Other Options

   Typing $ manually does not format the cell; no =CURRENCY formula exists; font color change does not affect number format.

3. Final Answer:

   Select the cell, then click Format > Number > Currency. -> Option A

4. Quick Check:

   Currency format applied via Format menu [OK]

Hint: Use Format > Number > Currency to format money [OK]

Common Mistakes:

• Typing $ manually instead of formatting
• Trying to use a non-existent formula
• Changing font color instead of number format

3. If cell A1 contains the number 0.25 and you apply the Percentage format, what will the cell display?

medium

A. 25%

B. 1/4/1900

C. $0.25

D. 0.25

Solution

1. Step 1: Understand Percentage Formatting

   Applying percentage format multiplies the number by 100 and adds a % sign. So 0.25 becomes 25%.

2. Step 2: Check Other Formats

   0.25 is the raw number, $0.25 is currency, and 1/4/1900 is a date format interpretation, so these are incorrect.

3. Final Answer:

   25% -> Option A

4. Quick Check:

   0.25 as percentage = 25% [OK]

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

Common Mistakes:

• Expecting the number to stay the same
• Confusing currency and percentage formats
• Thinking date format applies automatically

4. You entered the date 12/31/2023 in a cell, but it shows as 44926. What should you do to fix this?

medium

A. Increase the font size to make the date visible.

B. Re-enter the date with dashes instead of slashes.

C. Use the formula =DATE(2023,12,31) instead.

D. Change the cell format to Date via Format > Number > Date.

Solution

1. Step 1: Understand Date Storage

   Google Sheets stores dates as numbers (days since 12/30/1899). 44926 is the number for 12/31/2023.

2. Step 2: Apply Date Format

   To see the date instead of the number, change the cell format to Date via Format > Number > Date.

3. Final Answer:

   Change the cell format to Date via Format > Number > Date. -> Option D

4. Quick Check:

   Date format shows calendar date, not serial number [OK]

Hint: Change format to Date to see calendar dates, not numbers [OK]

Common Mistakes:

• Re-entering date instead of formatting
• Using formulas unnecessarily
• Changing font size instead of format

5. You have a list of prices in column A as numbers (e.g., 12.5, 7.99). You want to show them as US dollars with two decimals and a $ sign. Which steps will achieve this correctly?

hard

A. Use the formula =TEXT(A1, "$0.00") in a new column.

B. Select column A, then Format > Number > Currency, and set decimal places to 2.

C. Type $ before each number manually and round to 2 decimals.

D. Change the cell format to Percentage and multiply by 100.

Solution

1. Step 1: Apply Currency Format

   Select the cells and use Format > Number > Currency to add $ sign and currency style.

2. Step 2: Set Decimal Places

   Adjust decimal places to 2 to show cents properly.

3. Final Answer:

   Select column A, then Format > Number > Currency, and set decimal places to 2. -> Option B

4. Quick Check:

   Currency format + 2 decimals = correct money display [OK]

Hint: Use Format > Number > Currency and set decimals to 2 [OK]

Common Mistakes:

• Typing $ manually instead of formatting
• Using percentage format by mistake
• Using TEXT formula unnecessarily