Number formats (currency, percentage, date) in Excel - 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-05	1800	-0.02
1004	2024-04-10	2100	0.07
1005	2024-05-18	1950	0.03

Dashboard Components

• KPI Card: Total Sales
  Formula: =SUM(C2:C6)
  Format: Currency ($)
• KPI Card: Average Growth Rate
  Formula: =AVERAGE(D2:D6)
  Format: Percentage (2 decimal places)
• Table: Sales Details
  Shows Order ID, Order Date, Sales Amount, Growth Rate
  Formats:
  • Order Date: Date format (e.g., MMM dd, yyyy)
  • Sales Amount: Currency format
  • Growth Rate: Percentage format (1 decimal place)

Dashboard Layout

+----------------------+-----------------------+
| Total Sales (Currency) | Average Growth (Pct)  |
+----------------------+-----------------------+
|                                              |
|             Sales Details Table               |
|  (Order ID, Date, Sales $, Growth %)         |
|                                              |
+----------------------------------------------+

Interactivity

Add a date filter to select orders by month or range. When the date filter changes, the Total Sales, Average Growth, and Sales Details table update to show only data for the selected dates.

Self Check

If you add a filter to show only orders from March 2024 onward, which components update?

• Total Sales (currency) updates to sum only filtered sales.
• Average Growth Rate updates to average only filtered growth rates.
• Sales Details table shows only orders from March 2024 and later with correct formats.

Key Result

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

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