# Number Formatting

Course: [Excel](https://www.devtutorials.ai/learn/excel) · Section: Worksheet Formatting
Source: https://www.devtutorials.ai/learn/excel/worksheet-formatting/number-formatting

> Show values as numbers, currency, percentages, dates, times, and custom formats. Then format an invoice in the Chapter 3 project.

A value such as `0.15` can represent different things depending on how it is displayed. It could mean fifteen cents, fifteen percent, or part of a date or time. Number formatting helps readers understand what a value represents without changing the actual value stored in the cell. In this lesson, you'll learn how to use number, currency, percentage, date, time, and custom formats. You'll also apply these skills to create a professional invoice in the Chapter 3 project.

Open the **Excel sandbox** and follow along. Number formatting tools are available in the **Number** group on the **Home** tab. These include the format dropdown list, which usually displays **General**, along with buttons for currency, percentage, and decimal places.

Number formatting changes only the way a value is displayed. The underlying value remains unchanged, and formulas continue to use the original value. For example, a cell displaying `$12.50` may actually contain `12.4999`.

## Number Format

**General** is the default format used by Excel. It usually displays values as they are entered. The **Number** format provides greater control over decimal places and thousand separators.

To apply the Number format:

1. Select the cells.
2. Choose **Number** from the format dropdown.
3. Use **Increase Decimal** or **Decrease Decimal** to adjust the number of displayed decimal places.

For example:

- `1234.5` can be displayed as `1,234.50`

## Currency Format

**Currency** formatting displays values as monetary amounts by adding a currency symbol, thousand separators, and decimal places.

For example:

- `1500` becomes `$1,500.00`

To apply Currency formatting:

1. Select the cells.
2. Choose **Currency** from the format dropdown, or click the **$** button.

Enter only the number and allow the format to add the currency symbol automatically.

## Percentage Format

**Percentage** formatting displays a value as a percentage by multiplying the stored value by 100 and adding a percent sign.

For example:

- `0.15` displays as `15%`

To apply Percentage formatting:

1. Select the cells.
2. Choose **Percentage** from the format dropdown, or click the **%** button.

Excel stores `15%` as `0.15`. If you format a cell containing `15` as a percentage, Excel displays `1500%`. To display fifteen percent, enter either `0.15` or `15%`.

## Date Format

Dates are stored as numbers, allowing Excel to perform date calculations and comparisons. You can choose how dates appear without changing the stored value.

| Format | Example |
|----------|----------|
| **Short Date** | `3/15/2026` |
| **Long Date** | `Sunday, March 15, 2026` |
| **Custom dd-mmm-yyyy** | `15-Mar-2026` |

To format dates:

1. Select the date cells.
2. Choose **Short Date** or **Long Date** from the format dropdown.

## Time Format

Times are stored as fractions of a day.

Examples:

- `0.5` represents noon.
- `0.75` represents 6:00 PM.

Enter a time such as:

- `9:30`
- `2:15 PM`

Then select a **Time** format from the format dropdown if you want to change the way it is displayed.

## Custom Format

When built-in formats do not meet your needs, you can create a **Custom Format**.

Select **More Number Formats** or **Custom**, then enter a format code.

| Code | Displays `1234.5` As | Purpose |
|----------|----------|----------|
| `0` | `1235` | Whole numbers |
| `0.00` | `1234.50` | Two decimal places |
| `#,##0` | `1,235` | Thousand separators |
| `0.0 "kg"` | `1234.5 kg` | Display a unit while keeping the value numeric |
| `dd-mmm-yyyy` | `15-Mar-2026` | Custom date format |

The format `0.0 "kg"` is especially useful because the unit appears in the cell while the value remains a number that can be used in calculations.

If the sandbox does not provide a **Custom** option directly, open **More Number Formats**. You can also press **Ctrl + 1** to open the **Format Cells** dialog and create custom formats from the **Number** tab.

## Common mistakes

| Mistake | Result | Solution |
|----------|----------|----------|
| Typing `$1,500` directly into a cell | The value may be stored as text | Enter `1500` and apply Currency formatting |
| Formatting `15` as a percentage | It displays as `1500%` | Enter `0.15` or `15%` |
| Hiding decimal places and expecting totals to round | Totals may appear slightly different | Use rounding functions if needed |
| A date displays as `46096` | The date format was removed | Apply a date format again |
| `#####` appears after formatting | The column is too narrow | Increase the column width or use AutoFit |

## Exercise

Click **Reset** in the sandbox header and enter the following data:

- **A1:** `Price`, **B1:** `1500`
- **A2:** `Discount`, **B2:** `0.1`
- **A3:** `Date`, **B3:** `3/15/2026`
- **A4:** `Weight`, **B4:** `12.5`

Then complete each task and click **Check**.

## Chapter Project

Apply everything you've learned in Chapter 3 to create a professional invoice using formatting, alignment, borders, and number formats.

## What to Remember

- Number formats change how a value **appears**, not the value itself.
- **Number** format controls decimal places and thousand separators.
- **Currency** format adds a currency symbol and decimal places.
- **Percentage** format displays values such as `0.15` as `15%`.
- Dates and times are stored as numbers and can be displayed using different formats.
- **Custom formats** such as `0.00`, `#,##0`, and `0.0 "kg"` provide additional display options.
- Enter plain numbers and use formatting to add symbols, units, or display styles.

That's the end of **Chapter 3**. In **Chapter 4**, you'll begin creating formulas and using functions to perform calculations in Excel.