Number Formatting

Here is something that surprises a lot of beginners — the number you see in a cell and the number actually stored in that cell are two different things. Number formatting controls how a value is displayed without changing the underlying value at all. The same number, 0.1872, can look like a plain decimal, a percentage, a currency amount, or a date — depending entirely on how you format it. This lesson teaches you to control that display with precision.

The Number Group on the Home Tab

The quickest number formatting options live in the Number group on the Home tab. You will see a dropdown showing the current format (usually "General"), plus a row of shortcut buttons for the most common formats. These buttons are what most people use day-to-day:

The Built-In Format Types

The format dropdown gives you a list of common built-in number formats. Here is what each one does and when you would use it:

How Dates Actually Work in Excel

Dates deserve a special mention because they behave differently from other values. Excel stores every date as a plain integer — a serial number counting the days since 1 January 1900. So 15 January 2025 is stored as 45658. When you format a cell as a date, Excel takes that integer and displays it as a readable date.

Because dates are numbers, you can do arithmetic with them. Subtracting two dates gives you the number of days between them. Adding 30 to a date gives you the date 30 days later. This is exactly how Excel's date formulas work, and we will build on this in the Date Functions lesson.

The Format Cells Dialog — Full Control

The quick buttons in the Home tab handle most situations, but when you need full control over how a number looks, you open the Format Cells dialog. Get there by pressing Ctrl + 1 — the fastest way — or by right-clicking a cell and choosing Format Cells.

The Number tab inside this dialog shows you every built-in format category, and also gives you a live preview of what your cell will look like as you make changes. This is where you can also write your own custom formats.

Custom Number Formats

Custom formats are where number formatting becomes really powerful. You write a format code using a simple set of symbols, and Excel displays your number exactly the way you specify. Open Format Cells (Ctrl+1) → Number → Custom and type your format code in the Type box.

Here are the key symbols you need to know:

A format code can have up to four sections separated by semicolons — the first controls positive numbers, the second negative numbers, the third zeros, and the fourth text. This lets you show negative numbers in red and zeros as a dash:

#,##0.00 ; [Red]-#,##0.00 ; "-" ; @

Positive:   42,000.00
Negative:   -8,500.00    (in red)
Zero:       -
Text:       displayed as-is

Practical Custom Format Examples

The Percentage Trap

This is one of the most common mistakes beginners make, so it is worth calling out clearly. When you format a cell as percentage, Excel multiplies the stored value by 100 for display. So if the cell contains 0.18 and you apply percentage format, it shows 18% — correct.

But if you type 18 into a cell and then apply percentage format, Excel shows 1800% — because it multiplied 18 by 100. The correct approach is to either type 0.18 first and then format as percentage, or type 18% directly, which tells Excel to store 0.18 automatically.

Next up — Sort & Filter, where you will learn how to organise and slice through your data to find exactly what you need, fast.