Excel Number Format: Meaning, Examples, and How It Works

How-to basics · Updated November 20, 2026

In Excel, a number format is the set of display instructions that controls how a cell’s value appears, without usually changing the value stored in that cell. For example, formatting 0.25 as a percentage displays 25%, while the underlying value remains 0.25; this distinction is central to understanding number formats in Microsoft Excel for Microsoft 365 on Windows.

What an Excel number format means

A number format determines how Excel presents numbers, dates, times, and text in a worksheet. It can add a currency symbol, show a fixed number of decimal places, insert digit grouping, display a date in a chosen style, or hide decimal digits from view. It is a visual presentation rule attached to a cell, not ordinarily a conversion of the cell’s contents.

That means the value in the formula bar may not look exactly like the value shown in the grid. A cell might contain 12.345 but display 12.35 if its format shows two decimal places. Calculations generally use the stored value, not the rounded-looking display. Similarly, a date displayed as Jan 1, 2024 is represented internally as a serial number with a date format applied.

Excel applies a format to a cell or range, so the same value can look different in different places. The number 1500 might appear as 1,500, $1,500.00, 1.5K, or Jan 1, 1904 depending on the format. Those appearances do not all mean the underlying value has been rewritten; they are different ways of showing it.

How number formats work

Excel stores a value in a cell and separately applies instructions for displaying it. When you choose a format, Excel uses those instructions to render the value in the worksheet. Changing the format normally leaves the stored value available for arithmetic, sorting, and other operations.

For example, applying Percentage to 0.25 displays 25% because the percentage format shows the value multiplied by 100 and adds a percent sign. The stored value is still 0.25. Applying a format that shows zero decimal places to 4.6 may make the cell appear to contain 5, but the underlying value remains 4.6 unless you use a separate rounding operation to change it.

This distinction can explain apparent disagreements between a displayed total and the sum of the visible rounded figures. If several cells contain fractional cents but display only two decimal places, Excel may calculate with the hidden fractions. To make the stored values themselves whole cents, a formula or data-cleaning step is needed; changing the display format alone does not do that.

Built-in format categories and examples

Excel includes common categories in the Number group on the Home tab and in the Format Cells dialog. These built-in formats cover many everyday needs, and they are usually a better starting point than typing a custom code. Common choices include:

  • General: Excel’s default display, which adapts to the value and available column width.
  • Number: Displays numbers with configurable decimal places and digit grouping.
  • Currency and Accounting: Show a currency symbol. Accounting formatting aligns symbols and decimal points in a column, while Currency places the symbol closer to the amount.
  • Percentage: Displays a decimal value as a percentage. For instance, 0.08 appears as 8%.
  • Date and Time: Display date or time values in selected styles.
  • Text: Treats entries as text for display and input purposes; it is not a way to turn an existing long numeric value back into digits Excel has already discarded.
  • Scientific: Displays very large or very small numbers using exponential notation.

For a quick change, select the cells and use the Number Format drop-down in Home > Number. The nearby buttons can also apply accounting, percentage, comma-style grouping, or increase and decrease displayed decimal places. To see more options, open Format Cells with Ctrl+1 on Windows. In Excel for Mac, Command+1 opens the Format Cells dialog; the ribbon layout may differ slightly from Windows.

Custom formats and their codes

When the built-in choices do not show information in the exact style you need, a custom number format can define the display. On Windows, select the cells, open Format Cells, choose the Number tab, and select Custom. Enter or select a code in the Type field. A custom format changes the appearance, not the normal stored numeric value.

Several characters control the display. The zero placeholder forces a digit to appear, including a zero where a position has no digit. The hash placeholder (#) shows a digit only when needed. The question-mark placeholder reserves space for an insignificant digit, which can help align decimal places. A decimal point sets the displayed decimal position, and a comma can group thousands. For example, a format using #,##0.00 can show 12345.6 as 12,345.60 in a locale that uses a comma for grouping and a period for decimals.

Some other useful examples are:

  • 00000 displays the number 42 as 00042. This is useful for a visual code or fixed-width label, but the cell still stores the number 42.
  • 0.0% displays 0.256 as 25.6%.
  • ddd, mmm d, yyyy can display a date with the weekday, abbreviated month, day, and four-digit year.
  • [h]:mm displays elapsed hours and minutes, including totals beyond 24 hours.

A custom format can contain up to four semicolon-separated sections, in this order: positive numbers, negative numbers, zero values, and text. For example, 0.00;[Red]-0.00;0.00;@ specifies two decimal places for positive values, red negative values with a minus sign, two decimal places for zero, and the text placeholder for text. If a section is omitted, Excel uses its formatting rules for the remaining cases. Custom format syntax has additional options for colors, conditions, and literal characters, so test a code on sample cells before applying it to important data.

Choosing the right format for the data

Choose a format based on what the value represents and what a reader needs to see. A count of items usually needs a Number format with zero decimal places. A rate or share may be clearest as a percentage. A transaction amount should show a currency that matches the context, while a date column should use a consistent, unambiguous date style. Formatting a quantity as currency or a date can make a correct value misleading, so the choice is about meaning as well as appearance.

For amounts, decide whether decimals matter and whether the currency symbol is necessary in every cell. In a table with a clear currency heading, repeated symbols can make the column harder to scan; in a standalone figure, the symbol may prevent confusion. For percentages, check the scale before applying the format: 0.15 becomes 15%, but 15 becomes 1,500%. A percentage format does not infer that a whole-number input was intended to mean a percentage.

For identifiers such as product codes, postal codes, or account references, consider whether the value is really a number. If arithmetic is not meaningful and leading zeros must be preserved, storing the identifier as text is often safer. A custom format such as 00000 can make numeric entries appear padded, but it does not turn them into text; other systems may receive the numeric value without the visual padding.

Dates, times, and elapsed time

Excel date and time displays can be confusing because ordinary date values are stored as serial numbers, with time represented as a fraction of a day. A date format tells Excel to show a date serial in a calendar style. A time such as noon is represented as half of a day, so the numeric value 0.5 can display as 12:00 PM when a suitable time format is applied.

Different date formats can show the same date as 1/2/2025, 2-Jan-2025, or Thursday, January 2, 2025. The first style can be ambiguous across regions because some readers interpret the first number as a month and others as a day. For shared workbooks, a month name or an ISO-style year-month-day display can reduce that ambiguity. Excel’s available date presentation and separators can reflect regional settings.

Elapsed time needs a different choice from time of day. A total of 27 hours may wrap around and display as 3:00 if formatted as a clock time. The custom format [h]:mm displays the total as 27:00 instead. The square brackets tell Excel to show accumulated hours rather than resetting the hour count after each 24-hour day.

Number formats versus changing values

A frequent misconception is that formatting rounds or converts the value. Usually, it changes only what is displayed. If a cell contains 2.71828 and is formatted to show two decimal places, the grid displays 2.72, while a calculation can still use 2.71828. If a report requires the rounded number to be the actual value used in later calculations, use an appropriate rounding operation in the data or formula rather than relying on display formatting alone.

There are cases where values really are converted or changed. Entering an apostrophe before a number, importing a column as text, or using a conversion formula affects how content is stored. Excel also has an option called Set precision as displayed, which can permanently discard precision based on displayed values; it is a consequential workbook setting and should not be enabled casually. A number format by itself should not be confused with those actions.

The TEXT function is another related feature but does something different: it returns a text string formatted according to a pattern. Text that looks like a number is not the same as a numeric value for calculations, and converting results to text can affect later arithmetic, sorting, or charting. Use cell formatting when the value should remain numeric and only its appearance needs to change.

Common problems and how to diagnose them

If a cell shows hash marks instead of a value, the column may be too narrow for the formatted display, especially for dates and times. Widen the column or choose a shorter display format. Hash marks can also appear in some date and time situations when the value cannot be displayed as a valid date or time, so check both the width and the cell’s actual contents.

If changing a cell to Date or Number does not make it behave as expected, the content may be stored as text. A number format does not reliably turn text into a numeric value. Check whether the entry is left-aligned by default, has a leading apostrophe, or came from an import. Use Excel’s conversion tools or re-enter/import the data with the correct type, then apply the desired format.

Long digit strings are a separate concern. Excel preserves only 15 significant digits for numeric values, so formatting cannot restore digits lost when a long identifier was entered as a number. Import or enter such identifiers as text from the outset. Likewise, a custom format can show leading zeros for a numeric code but cannot protect a lengthy code from numeric precision limits.

Formats can also differ between the worksheet and other applications. A workbook format preserves Excel formatting, whereas plain-text formats such as CSV do not preserve the workbook’s full formatting rules. Before sharing a CSV, verify how the receiving system will interpret dates, decimal separators, leading zeros, and identifiers. Do not assume that a custom visual format will travel with the data.

Frequently Asked Questions

Does an Excel number format change the value?

Usually not. It changes how the value is displayed, while formulas continue to use the stored value. Some separate actions, such as converting content to text, rounding it, or enabling Set precision as displayed, can change what is stored.

How do I add leading zeros in Excel?

For a numeric value used in calculations, apply a custom format such as 00000 to display five digit positions. If the entry is an identifier that must retain its zeros when copied or exported, store it as text instead; custom display formatting does not change the underlying number.

Why does Excel show a different number of decimal places than I entered?

The selected number format may be set to show fewer or more decimal places. The hidden digits may still be stored. Use the decimal-place buttons on the Home tab or open Format Cells to change the display, and check the formula bar or a higher-precision format when you need to inspect the value.

Can I use a number format to round a value?

You can make a value look rounded by displaying fewer decimal places, but that does not normally round the stored value. If later calculations must use a rounded result, apply a rounding formula or otherwise change the data, rather than relying only on the cell’s display format.

Why does a date appear as a number in Excel?

Excel stores dates as serial values and uses a date number format to display them as calendar dates. If you see a serial number, apply a date format; if the cell remains unchanged, the entry may be text rather than a numeric date value.

Practical takeaway

An Excel number format is best understood as a display layer: it helps readers interpret a value without generally changing the value itself. Choose a format that matches the data’s purpose, verify the stored value when precision matters, and use text storage for identifiers that must retain exact characters. For unusual layouts, custom formats provide fine control, but checking a few representative cells helps prevent misleading displays.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.