Number formats (currency, percentage, date) in Google Sheets - Deep Dive

Overview - Number formats (currency, percentage, date)

What is it?

Number formats in Google Sheets change how numbers look in cells without changing their actual value. Common formats include currency, which shows money with symbols; percentage, which shows numbers as parts of 100; and date, which displays numbers as calendar dates. These formats help make data easier to read and understand at a glance.

Why it matters

Without number formats, numbers would appear as plain digits, making it hard to tell if a number is money, a percentage, or a date. This can cause confusion and mistakes when analyzing data. Number formats make spreadsheets clearer and more professional, helping people quickly understand the meaning behind the numbers.

Where it fits

Before learning number formats, you should know how to enter data and basic cell selection in Google Sheets. After mastering formats, you can learn formulas that depend on data types, like date calculations or financial functions.

Mental Model

Core Idea

Number formats are like clothes for numbers—they change how numbers look without changing what they really are inside the cell.

Think of it like...

Imagine your phone number written on a piece of paper. You can write it with dashes, spaces, or parentheses to make it easier to read, but the actual number stays the same. Number formats do the same for numbers in spreadsheets.

┌───────────────┐
│   Cell Value  │
│     0.25      │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Display Format│
│ Percentage %  │
│   Shows 25%   │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding raw numbers in cells

Concept: Numbers in cells are stored as raw values before any formatting is applied.

When you type a number like 123 or 0.5 into a cell, Google Sheets stores it as a plain number. This raw number is what formulas use for calculations, regardless of how it looks on the screen.

Result

The cell shows the number exactly as typed, for example, 123 or 0.5.

Understanding that the cell stores the actual number separately from how it looks helps you realize formatting only changes appearance, not the data itself.

2

Foundation

Applying basic number formats

Concept: You can change how numbers look by applying formats like currency, percentage, or date from the menu.

Select a cell with a number, then go to Format > Number and pick a format like Currency, Percentage, or Date. The number's appearance changes but the stored value stays the same.

Result

A number like 0.25 formatted as Percentage shows as 25%, or 100 formatted as Currency shows as $100.00 (depending on locale).

Knowing how to apply formats visually clarifies data meaning without affecting calculations.

3

Intermediate

How percentage format works internally

🤔Before reading on: Do you think typing 25% stores 25 or 0.25 as the cell value? Commit to your answer.

Concept: Percentage format multiplies the stored number by 100 for display but keeps the original decimal value internally.

When you type 25% in a cell, Google Sheets stores it as 0.25 internally. The percentage format then shows it as 25%. If you type 0.25 and format as percentage, it also shows 25%.

Result

The cell value is 0.25, but the display shows 25%. Formulas use 0.25 in calculations.

Understanding this prevents errors when entering percentages and using them in formulas.

4

Intermediate

Customizing currency and date formats

🤔Before reading on: Can you guess if you can change the currency symbol or date style in Google Sheets? Commit to your answer.

Concept: Google Sheets lets you customize currency symbols and date display styles to match your needs or locale.

You can choose different currency symbols like $, €, or ¥ by selecting Format > Number > More Formats > More Currencies. For dates, you can pick formats like MM/DD/YYYY or DD-MMM-YYYY under Format > Number > Date or create custom date formats.

Result

Numbers show with your chosen currency symbol or date style, e.g., €100.00 or 31-Dec-2023.

Knowing customization options helps you present data clearly for different audiences or countries.

5

Intermediate

Date formats and underlying serial numbers

Concept: Dates are stored as serial numbers counting days from a start date, with formatting showing them as calendar dates.

Google Sheets stores dates as numbers starting from December 30, 1899 (day 1). For example, January 1, 2024, is stored as 45179. Formatting the cell as a date converts this number to a readable date.

Result

A cell with 45179 formatted as Date shows 1/1/2024, but the value is still a number usable in calculations.

Understanding dates as numbers explains why you can add days to dates by adding numbers.

6

Advanced

How formatting affects formulas and calculations

🤔Before reading on: Does changing a cell’s number format change the result of formulas using that cell? Commit to your answer.

Concept: Number formats only change appearance; formulas always use the underlying raw value regardless of format.

If a cell shows 25% but stores 0.25, a formula like =A1*100 uses 0.25, returning 25. Changing the format to Currency or Date does not affect formula results.

Result

Formulas produce consistent results based on raw values, not displayed formats.

Knowing this prevents confusion when formula results don’t match what you see on screen.

7

Expert

Limitations and quirks of number formatting

🤔Before reading on: Can number formats cause rounding or display errors that affect data interpretation? Commit to your answer.

Concept: Number formats can round displayed values or mislead if formats don’t match data type, causing interpretation errors.

For example, currency format may round to two decimals, hiding small differences. Dates formatted incorrectly can show wrong calendar dates. Also, text-looking numbers can cause formula errors if formats are mismatched.

Result

Displayed values may differ from stored values, leading to mistakes if not careful.

Understanding these quirks helps avoid subtle bugs and ensures data integrity in reports.

Under the Hood

Google Sheets stores all numbers as raw numeric values internally. Number formats are instructions for the display engine on how to show these values visually. For example, percentage format multiplies the raw number by 100 and adds a % sign for display only. Dates are stored as serial numbers counting days from a fixed start date, and formatting converts these numbers into human-readable calendar dates. The formatting layer is separate from the data layer, so calculations always use raw values.

Why designed this way?

Separating data storage from display formatting allows flexibility in showing data in many ways without changing the underlying values. This design supports accurate calculations and easy presentation changes. Early spreadsheet programs adopted this model to balance usability and computational correctness.

┌───────────────┐
│   Raw Value   │
│   (e.g. 0.25) │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Format Layer  │
│ (Percentage)  │
│ Multiplies x100│
│ Adds % sign   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Display Value │
│    25%        │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: If a cell shows 25%, is its value 25 or 0.25? Commit to your answer.

Common Belief: If a cell shows 25%, its value is 25.

Reality: The actual stored value is 0.25; the percentage format just changes how it looks.

Why it matters: Mistaking displayed percentage for the stored value can cause formula errors and wrong calculations.

Quick: Does changing a cell’s number format change the number used in formulas? Commit to your answer.

Common Belief: Changing number format changes the actual number in the cell.

Reality: Number formats only change appearance; formulas always use the original stored number.

Why it matters: Believing otherwise can lead to confusion when formula results don’t match what you see.

Quick: Can you enter a date as text and have it behave like a date in calculations? Commit to your answer.

Common Belief: Typing a date as text works the same as a date format for calculations.

Reality: Dates entered as text are not recognized as dates and cause errors in date formulas.

Why it matters: Misunderstanding this leads to broken formulas and incorrect data analysis.

Quick: Does currency format always show the exact stored number? Commit to your answer.

Common Belief: Currency format shows the exact stored number without rounding.

Reality: Currency format rounds numbers to two decimal places for display, hiding small differences.

Why it matters: Ignoring rounding can cause unnoticed errors in financial reports.

Expert Zone

1

Number formats can be combined with conditional formatting to highlight data trends visually without changing values.

2

Custom number formats allow hiding parts of numbers or adding text, enabling advanced display tricks like showing positive/negative differently.

3

Locale settings affect default currency symbols and date formats, so sharing sheets across countries requires careful format management.

When NOT to use

Avoid relying solely on number formats for data validation or logic checks; use data validation rules or formulas instead. For complex date/time calculations, use dedicated date/time functions rather than depending on display formats.

Production Patterns

Professionals use number formats to create clear financial reports, dashboards with percentages for KPIs, and date timelines. They combine formats with formulas to automate currency conversions or date calculations, ensuring data is both accurate and easy to read.

Connections

Data Validation

Builds-on

Understanding number formats helps when setting data validation rules that depend on data types like dates or percentages.

User Interface Design

Same pattern

Number formatting in spreadsheets is similar to UI design where data presentation is separated from data logic to improve user experience.

Human Perception Psychology

Builds-on

Knowing how number formats affect readability connects to how humans perceive and process visual information, improving data communication.

Common Pitfalls

#1 Entering percentages as whole numbers without formatting.

Wrong approach:

Typing 25 in a cell and expecting it to mean 25%.

Correct approach:

Typing 0.25 and formatting the cell as Percentage to show 25%.

Root cause:

Not understanding that percentage format expects decimal values representing parts of 1.

#2 Typing dates as text strings instead of using date format.

Wrong approach:

Entering '2024-01-01' as plain text without formatting.

Correct approach:

Entering the date normally and formatting the cell as Date so Sheets recognizes it as a date value.

Root cause:

Confusing text input with date data type, causing formulas to fail.

#3 Relying on currency format to show exact values without rounding.

Wrong approach:

Using currency format and assuming displayed value matches stored value exactly.

Correct approach:

Being aware currency format rounds display and checking raw values for precise calculations.

Root cause:

Assuming display equals stored value without considering rounding rules.

Key Takeaways

Number formats change how numbers look but never change the actual stored value in a cell.

Percentage format displays decimals as parts of 100 but stores the decimal value internally.

Dates are stored as serial numbers and formatted to look like calendar dates for easy calculations.

Changing number formats does not affect formulas; formulas always use the raw stored numbers.

Understanding number formats prevents common errors and helps present data clearly and professionally.

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