Number formats (currency, percentage, date) in Google Sheets - Dashboard Guide

Dashboard Mode - Number formats (currency, percentage, date)

Dashboard Goal

Show sales data with clear number formats: currency for sales, percentage for growth, and date for order dates. This helps understand money, growth rates, and timing easily.

Sample Data

Order ID	Order Date	Sales Amount	Growth Rate
1001	2024-01-15	1500	0.05
1002	2024-02-20	2300	0.10
1003	2024-03-10	1800	-0.02
1004	2024-04-05	2100	0.07
1005	2024-05-18	1950	0.03

Dashboard Components

• KPI Card: Total Sales
  Formula: =SUM(C2:C6)
  Format: Currency (e.g., $)
• KPI Card: Average Growth Rate
  Formula: =AVERAGE(D2:D6)
  Format: Percentage with 1 decimal place (e.g., 4.6%)
• Table: Sales Details
  Columns: Order ID (plain), Order Date (formatted as Date, e.g., Jan 15, 2024), Sales Amount (Currency), Growth Rate (Percentage)
• Line Chart: Sales Over Time
  X-axis: Order Date (Date format)
  Y-axis: Sales Amount (Currency format)

Dashboard Layout

+----------------------+-----------------------+
| Total Sales (Currency) | Average Growth (Percent) |
+----------------------+-----------------------+
|                                      Sales Details Table                                      |
+---------------------------------------------------------------------------------------------+
|                                      Sales Over Time Chart                                   |
+---------------------------------------------------------------------------------------------+

Interactivity

Add a date filter to select orders within a date range. When the date filter changes:

• Total Sales updates to sum only filtered orders.
• Average Growth Rate recalculates for filtered orders.
• Sales Details Table shows only filtered rows.
• Sales Over Time Chart redraws with filtered dates and sales.

Self Check

If you add a filter to show only orders after 2024-03-01, which components update and how?

• Total Sales will sum sales from orders 1003, 1004, and 1005.
• Average Growth Rate will average growth rates of these orders.
• Sales Details Table will show only orders 1003, 1004, and 1005.
• Sales Over Time Chart will plot sales for these filtered dates only.

Key Result

Dashboard showing sales data with currency, percentage, and date formats for clear financial and time insights.

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