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

Overview - Number formats (currency, percentage, date)

What is it?

Number formats in Excel change how numbers appear in cells without altering their actual values. 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, all numbers would look plain and confusing, making it hard to tell if a number is money, a date, or a simple count. This would slow down work and cause mistakes, especially in finance, scheduling, and reports. Number formats make spreadsheets clearer and more professional, helping people make better decisions quickly.

Where it fits

Before learning number formats, you should know how to enter and edit data in Excel cells. After mastering formats, you can learn formulas that depend on these formats, like calculating interest on currency or working with dates in schedules.

Mental Model

Core Idea

Number formats are like special glasses that change how you see numbers without changing the numbers themselves.

Think of it like...

Imagine you have a plain white T-shirt. Number formats are like putting different stickers on it: a dollar sign sticker for money, a percent sign sticker for percentages, or a calendar sticker for dates. The T-shirt stays the same, but the stickers tell you what it means.

┌───────────────┐
│   Cell Value  │  (Actual number stored)
│   0.25       │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Display Format│
│ Percentage    │
│ Shows as 25%  │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Cell Values vs Display

Concept: Numbers in Excel have a stored value and a displayed format, which can differ.

When you type a number in Excel, the exact number is saved in the cell. However, Excel can show this number in different ways using number formats. For example, typing 0.5 and formatting the cell as Percentage will show 50%, but the stored value remains 0.5.

Result

The cell shows 50% but the value is still 0.5, which can be used in calculations.

Understanding that formatting changes appearance but not the actual number helps avoid confusion when using formulas.

2

Foundation

Applying Basic Number Formats

Concept: Excel provides built-in formats like Currency, Percentage, and Date to change number appearance easily.

You can select a cell and choose a format from the Home tab or Format Cells menu. Currency adds a dollar sign and two decimals, Percentage multiplies the number by 100 and adds %, and Date converts numbers to calendar dates based on Excel's date system.

Result

Numbers display as $12.00, 75%, or 4/27/2024 depending on the format chosen.

Knowing how to apply formats quickly improves spreadsheet readability and professionalism.

3

Intermediate

Customizing Number Formats

🤔Before reading on: do you think you can create your own number format to show numbers exactly how you want? Commit to yes or no.

Concept: Excel lets you create custom number formats to control exactly how numbers appear, including symbols, colors, and text.

In Format Cells > Number > Custom, you can write codes like "$#,##0.00" to show currency with commas and decimals, or "0%" to show percentages without decimals. You can also add text like "Profit: " before numbers or color code positive and negative values.

Result

Numbers can display as $1,234.56, Profit: 50%, or red -100 depending on your custom format.

Custom formats give you powerful control to make data clearer and highlight important values without changing the data itself.

4

Intermediate

How Dates Are Stored and Formatted

🤔Before reading on: do you think Excel stores dates as text or numbers internally? Commit to your answer.

Concept: Excel stores dates as serial numbers counting days from a starting point, and formats convert these numbers into readable dates.

Excel counts days starting from January 1, 1900 as day 1. For example, 45000 represents a date in 2023. Formatting this number as a date shows the calendar date instead of the number. Time is stored as decimal fractions of a day.

Result

A cell with 45000 formatted as Date shows 09/10/2023, but the value is still 45000.

Knowing dates are numbers helps you use math with dates, like finding differences or adding days.

5

Intermediate

Percentage Format and Its Calculation

Concept: Percentage format multiplies the stored number by 100 and adds a % sign for display.

If you type 0.2 and format as Percentage, Excel shows 20%. If you type 20 and format as Percentage, Excel shows 2000%. This is because Excel treats the cell value as a decimal fraction of 1 for percentages.

Result

0.2 displays as 20%, 20 displays as 2000%.

Understanding this prevents errors when entering percentages and interpreting results.

6

Advanced

Impact of Number Formats on Formulas

🤔Before reading on: do you think changing a cell's number format affects formulas that use that cell? Commit to yes or no.

Concept: Number formats only change appearance; formulas always use the underlying stored value, not the displayed format.

If a cell shows $100.00 but stores 100, a formula adding that cell to another uses 100, not the formatted text. This means calculations remain accurate regardless of formatting.

Result

Formulas calculate correctly even if cells show currency, percentage, or dates.

Knowing this avoids confusion when formulas give unexpected results due to formatting.

7

Expert

Locale and Regional Effects on Number Formats

🤔Before reading on: do you think number formats like currency and date look the same worldwide? Commit to yes or no.

Concept: Excel adapts number formats based on your computer's regional settings, affecting symbols, date order, and separators.

For example, currency might show $ in the US but € in Europe. Dates might appear as MM/DD/YYYY or DD/MM/YYYY depending on locale. Decimal and thousand separators also change (comma vs dot).

Result

Same number formatted as currency or date looks different on computers with different regional settings.

Understanding locale effects helps create spreadsheets that work correctly for international users.

Under the Hood

Excel stores all numbers as raw numeric values internally. Number formats are instructions that tell Excel how to display these values visually. When you apply a format, Excel overlays a display mask on the number without changing the stored data. Dates are stored as integers counting days from a base date, and times as decimal fractions of a day. Percentage formats multiply the stored decimal by 100 and add a percent sign for display only.

Why designed this way?

Separating storage from display allows Excel to keep calculations accurate and fast while giving users flexible ways to view data. Early spreadsheet programs needed a way to show money, dates, and percentages clearly without breaking formulas. This design balances usability and precision.

┌───────────────┐
│   Cell Value  │  (Stored number, e.g., 0.25)
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Number Format │  (Currency, Percentage, Date)
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Display Value │  (Shown as $0.25, 25%, or 1/1/1900)
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does changing a cell's number format change its actual value? Commit to yes or no.

Common Belief: Changing the number format changes the number itself.

Reality: Number formats only change how the number looks, not the stored value.

Why it matters: Believing this causes errors when formulas use the original value, leading to confusion and wrong conclusions.

Quick: If you type 50 and format as percentage, will Excel show 50% or 5000%? Commit to your answer.

Common Belief: Typing 50 and formatting as percentage shows 50%.

Reality: Excel shows 5000% because it treats 50 as 50.0, which is 5000% when multiplied by 100.

Why it matters: This misunderstanding leads to incorrect data entry and wrong percentage calculations.

Quick: Are dates stored as text strings in Excel? Commit to yes or no.

Common Belief: Dates are stored as text strings like '4/27/2024'.

Reality: Dates are stored as numbers counting days from a base date, not as text.

Why it matters: Thinking dates are text prevents using date math and causes formula errors.

Quick: Will a custom number format change the actual data in the cell? Commit to yes or no.

Common Belief: Custom formats can change the cell's value.

Reality: Custom formats only change appearance, not the underlying value.

Why it matters: Misunderstanding this can cause users to try to fix data by formatting instead of correcting values.

Expert Zone

1

Excel's number formats can include conditional colors and text, allowing dynamic visual cues without formulas.

2

Date and time storage as serial numbers enables complex calculations like elapsed time, but can cause errors if users enter dates as text.

3

Locale settings affect not only display but also how Excel interprets typed numbers, which can cause subtle bugs in international spreadsheets.

When NOT to use

Number formats are not suitable when you need to change the actual data, such as converting text to numbers or changing date values. In those cases, use data transformation functions or formulas instead. Also, avoid relying solely on formatting for critical data interpretation; use data validation or helper columns for clarity.

Production Patterns

Professionals use number formats to create clear financial reports with currency symbols and decimal places, dashboards with percentage progress bars, and schedules with consistent date formats. Custom formats highlight negative values in red or add units like 'kg' or 'hrs' directly in cells. International teams use locale-aware formats to ensure data is correctly understood worldwide.

Connections

Data Validation

Builds-on

Understanding number formats helps set up data validation rules that accept only properly formatted numbers, dates, or percentages, improving data quality.

User Interface Design

Same pattern

Number formatting in spreadsheets is like UI design in apps: both shape how users perceive and interact with data without changing the underlying logic.

Human Perception Psychology

Builds-on

Knowing how number formats affect readability connects to psychology principles about how people quickly interpret symbols and patterns, improving communication.

Common Pitfalls

#1 Entering percentages as whole numbers without formatting.

Wrong approach:

Typing 25 in a cell and leaving it as General format to mean 25%.

Correct approach:

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

Root cause:

Misunderstanding that Excel treats percentages as decimals internally.

#2 Typing dates in inconsistent formats causing errors.

Wrong approach:

Entering dates as text like '27-04-2024' in a system expecting MM/DD/YYYY.

Correct approach:

Entering dates in the system's recognized format or using Date functions to ensure consistency.

Root cause:

Ignoring locale and Excel's date recognition rules.

#3 Using number formats to try to fix wrong data values.

Wrong approach:

Formatting a negative number as positive with a custom format instead of correcting the data.

Correct approach:

Correcting the actual data value rather than relying on formatting to hide errors.

Root cause:

Confusing appearance with actual data correctness.

Key Takeaways

Number formats change how numbers look in Excel but never change the actual stored values.

Currency, percentage, and date formats help make data clearer and easier to understand at a glance.

Dates are stored as numbers counting days from a base date, enabling date math and formatting.

Custom number formats allow powerful control over appearance, including colors and text, without altering data.

Locale settings affect how numbers and dates display and are interpreted, which is crucial for international spreadsheets.