Number formats (currency, percentage, date) in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a clear report showing sales amounts as currency, commission rates as percentages, and sale dates in a friendly date format.

📊 Data: You have a table with sales data including the sale date, sales amount, and commission rate as a decimal.

🎯 Deliverable: Create a formatted sales report where sales amounts show as currency, commission rates as percentages, and dates as 'Month Day, Year'.

Sample Data

Sale Date	Sales Amount	Commission Rate
2024-01-15	1500	0.05
2024-02-10	2300	0.07
2024-03-05	1800	0.06
2024-04-20	2100	0.05
2024-05-18	1950	0.04
2024-06-12	2500	0.08
2024-07-01	1700	0.05
2024-08-15	2200	0.06

1

Step 1: Select the 'Sales Amount' column cells (B2:B9). Format these cells as currency with the dollar sign and two decimal places.

Format > Number > Currency

Expected Result

Sales amounts display as $1,500.00, $2,300.00, etc.

2

Step 2: Select the 'Commission Rate' column cells (C2:C9). Format these cells as percentages with one decimal place.

Format > Number > Percent, then increase decimal places to 1

Expected Result

Commission rates display as 5.0%, 7.0%, 6.0%, etc.

3

Step 3: Select the 'Sale Date' column cells (A2:A9). Format these cells to show dates as 'Month Day, Year' (e.g., January 15, 2024).

Format > Number > Custom date and time > Type: 'MMMM d, yyyy'

Expected Result

Dates display as January 15, 2024; February 10, 2024; etc.

Final Result

Sale Date         | Sales Amount | Commission Rate
-------------------------------------------------
January 15, 2024  | $1,500.00    | 5.0%
February 10, 2024 | $2,300.00    | 7.0%
March 5, 2024     | $1,800.00    | 6.0%
April 20, 2024    | $2,100.00    | 5.0%
May 18, 2024      | $1,950.00    | 4.0%
June 12, 2024     | $2,500.00    | 8.0%
July 1, 2024      | $1,700.00    | 5.0%
August 15, 2024   | $2,200.00    | 6.0%

✓Sales amounts are easy to read with currency formatting.

✓Commission rates clearly show as percentages.

✓Dates are friendly and clear with full month names.

Bonus Challenge

Add a new column 'Commission Earned' that calculates the commission amount for each sale and format it as currency.

Show Hint

Use formula =B2*C2 in the first row of the new column and format the result as currency.

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