Number formats (currency, percentage, date) in Excel - Step-by-Step Guide

Introduction

Number formats help you show numbers as money, percentages, or dates. This makes your data easier to understand and looks professional. You can change how numbers appear without changing their actual values.

When you want to show prices with a dollar sign and two decimals in a sales report

When you need to display test scores as percentages in a grade sheet

When you want to show dates in a calendar or schedule clearly

When you want to format financial data to match currency style for budgeting

When you want to convert decimal numbers to percent format for easier reading

Steps

Step 1: Select

- the cells you want to format

The selected cells are highlighted and ready for formatting

Step 2: Click

- Home tab on the ribbon

The Home tab options are visible

Step 3: Click

- Number Format dropdown in the Number group

A list of number format options appears

Step 4: Select

- Currency from the dropdown

Selected cells show numbers with a currency symbol and two decimals

Step 5: Repeat steps 3 and 4

- but select Percentage

Selected cells show numbers as percentages with a % sign

Step 6: Repeat steps 3 and 4

- but select Short Date or Long Date

Selected cells show numbers as dates in the chosen date style

Before vs After

Before

Cells show raw numbers like 1234.5, 0.85, or 44205

After

Cells show $1,234.50, 85%, or 3/15/21 depending on chosen format

Settings Reference

Number Format dropdown

📍 Home tab > Number group

Choose how numbers appear in cells

Default: General

More Number Formats

📍 Home tab > Number group > Number Format dropdown > More Number Formats

Customize number appearance beyond preset options

Default: Varies by format

Decimal places

📍 More Number Formats dialog > Number tab

Set how many digits show after the decimal point

Default: 2 for Currency and Percentage

Common Mistakes

Formatting numbers as text to add currency or percent signs

This stops Excel from recognizing them as numbers, breaking calculations

Use the Number Format dropdown to apply currency or percentage formats properly

Changing the cell value instead of formatting to show a date

Typing a date as text loses date functions and sorting ability

Enter the date as a number or date, then apply a date format

Summary

Number formats change how numbers look without changing their value

Use the Home tab's Number Format dropdown to pick currency, percentage, or date styles

Avoid typing symbols manually; use formatting to keep data usable for calculations

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