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

Introduction

Number formats help you show numbers in ways that make sense, like money, percentages, or dates. This makes your data easier to read and understand at a glance.

When you want to show prices with a currency symbol like $ or €.

When you need to display a number as a percentage, such as 75%.

When you want to show dates in a clear format like MM/DD/YYYY or DD-MM-YYYY.

When you have a list of sales amounts and want them to look like money.

When you track progress and want to show it as a percent instead of a decimal.

Steps

Step 1: Select the cells with numbers you want to format

- Google Sheets grid

The selected cells are highlighted

💡 Click and drag to select multiple cells or use Ctrl/Cmd + click for non-adjacent cells

Step 2: Click the Format menu

- Top menu bar

The Format dropdown menu opens

Step 3: Hover over Number

- Format dropdown menu

A side menu with number format options appears

Step 4: Click Currency to format numbers as money

- Number submenu

Selected cells show numbers with a currency symbol and two decimals, like $12.00

Step 5: Click Percent to format numbers as percentages

- Number submenu

Selected cells show numbers multiplied by 100 with a % sign, like 75%

Step 6: Click Date to format numbers as dates

- Number submenu

Selected cells show numbers as dates in the default date format, like 6/15/2024

Step 7: To customize, click More Formats > More date and time formats

- Number submenu

A dialog opens where you can pick or create a custom date format

Before vs After

Before

Cells show plain numbers like 12, 0.75, 44500

After

Cells show formatted values like $12.00, 75%, 6/15/2024

Settings Reference

Currency

📍 Format > Number > Currency

Show numbers as money with currency symbols and two decimal places

Default: Based on your spreadsheet locale

Percent

📍 Format > Number > Percent

Show numbers as percentages with a % sign

Default: 0%

Date

📍 Format > Number > Date

Show numbers as dates in various common formats

Default: MM/DD/YYYY

Common Mistakes

Formatting a cell with text instead of numbers

Number formats only change how numbers look, not text values

Make sure the cell contains a number before applying number formats

Entering a date as text (e.g., 'June 15, 2024') and expecting date format to work

Text dates are not recognized as dates by Google Sheets

Enter dates using recognized date formats or use the DATE function

Expecting percentage format to convert 0.75 to 0.75%

Percent format multiplies the number by 100 and adds %, so 0.75 becomes 75%

Enter decimals like 0.75 to get 75% or enter 75 to get 7500%

Summary

Number formats change how numbers appear without changing the actual value.

Use currency format to show money, percent format to show percentages, and date format to show dates.

Make sure your data is the correct type (number or date) before applying formats.

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