Update Number Format in Excel: How to Change Formats

How-to basics · Updated November 20, 2026

To update a number format in Excel for Microsoft 365 on Windows, select the cell or range, then choose a format from Home > Number, or press Ctrl+1 to open Format Cells and select a format on the Number tab. Formatting changes how a value looks, not usually the value stored in the cell, so you can show a number as a date, percentage, currency, or a chosen number of decimal places without recalculating it.

Quick steps to change a number format

For a common format, select the cells and use the Number Format list in the Number group on the Home tab. Choose options such as General, Number, Currency, Accounting, Short Date, Long Date, Time, Percentage, or Text. The selected format applies to every cell in the selection; it does not change the contents of neighboring cells.

For more control, open the Format Cells dialog:

  1. Select the cells, rows, columns, or range you want to update.
  2. Press Ctrl+1, or right-click the selection and choose Format Cells.
  3. On the Number tab, select a category, such as Number, Date, Currency, or Custom.
  4. Adjust available options, such as decimal places, a date type, or a currency symbol.
  5. Select OK and check that the display is what you intended.

To apply a format to a whole column, click its column letter before choosing the format. New values entered in that column will generally use the column’s existing format, although pasted data or imported content can bring different formatting with it. To format only part of a column, select the relevant cells instead.

What a number format changes—and what it does not

A number format controls a cell’s display. A value such as 0.25 can appear as 0.25, 25%, or $0.25 depending on the format. In the percentage example, Excel displays the stored value multiplied by 100 and adds a percent sign; applying Percentage to 0.25 does not change the stored value to 25. Entering 25% in a cell, by contrast, stores the numeric value 0.25.

This distinction matters when calculations, charts, or later exports are involved. Two cells can look different but contain the same numeric value, or look similar while containing different values. The formula bar can help you inspect the selected cell’s underlying content. Changing a format normally leaves the value intact, so it is a reversible way to improve readability without changing the calculation.

There are exceptions to the practical result: formatting a blank cell as Text affects how future entries are interpreted, but applying Text to a cell that already contains a number does not automatically convert that existing number into text. For existing values, conversion is a separate operation. Also, a formula may return text rather than a numeric value, in which case number formats cannot make that result behave like a number.

How to choose a built-in format

Choose a format that communicates what the value represents and preserves useful precision. Excel’s built-in categories offer a quick starting point:

  • Number: Shows numeric values with configurable decimal places and optional thousands separators. For example, a value of 1234.5 can display as 1,234.50.
  • Currency or Accounting: Adds a currency symbol. Accounting aligns symbols and decimal points down a column, which can make financial tables easier to scan. Choose the correct currency rather than assuming the workbook’s default symbol is appropriate.
  • Percentage: Displays a numeric value as a percentage. For instance, 0.08 appears as 8%. Check the underlying value before applying it, since formatting 8 as a percentage displays 800%.
  • Date and Time: Displays date or time values in a selected pattern. Short Date and Long Date are presentation choices, not different kinds of date arithmetic.
  • Text: Treats new entries as text, useful for identifiers that should not be calculated, such as product codes. It is not a general repair tool for values already stored as numbers.
  • General: Uses Excel’s default display behavior. It may show large or very small values in scientific notation, depending on the cell width and value.

For a table of measurements, decide how many decimal places readers need. Showing 2.347 as 2.35 makes the sheet neater but hides displayed precision; it does not round the underlying value used in calculations. If calculations must use rounded values, that is a calculation decision rather than a number-format change.

Use a custom format for a specific display

If the built-in choices do not fit, open Format Cells, select the Custom category, and choose or edit a format code in the Type box. Custom formats are useful when you want a consistent pattern without changing the stored values. For example, the code 0.00 displays at least one digit before the decimal and two digits after it, so 4.5 appears as 4.50. The code #,##0 adds thousands separators and shows no decimal places.

Some other common patterns are 0% for a percentage with no decimal places, 0.0% for one decimal place, and yyyy-mm-dd for a year-month-day date display. A date code changes the presentation, not the date value itself. Custom codes can use different separators or date conventions depending on regional settings, so check the preview and confirm that the result is unambiguous for the people who will use the workbook.

Excel custom number formats can have up to four sections separated by semicolons: positive values, negative values, zero values, and text. For example, 0.00;[Red]-0.00;–;@ requests two decimal places for positive values, red negative values with a minus sign, a dash for zero, and the original text for text entries. The exact appearance can depend on Excel’s interpretation of punctuation and regional settings. A custom format changes display only; showing a dash for zero does not remove the zero from the cell.

Custom formats are not the same as a cell’s literal contents. If you need a label such as “Item 0042” to be part of a value, rather than just a visual presentation, consider whether the data should be stored as text. A display-only pattern can be convenient for reports, but it may not carry over as expected when data is exported to another system.

Apply a format consistently across a workbook

When updating an existing workbook, first decide whether the change belongs on one range, a whole column, or several related tables. Apply a format to comparable fields consistently: dates should look like dates throughout a report, and amounts in the same currency should use matching symbols and precision. Consistency makes it easier to compare rows and spot data-entry errors.

You can copy formatting from a correctly formatted cell with the Format Painter on the Home tab, then apply it to other cells. This copies formatting rather than the cell’s value, but it can also copy more than the number format, such as font, fill, borders, and alignment. If you want to copy only a number format, use Paste Special: copy the source cell, select the destination, open Home > Paste > Paste Special, and choose Formats. Check the result if the destination already uses important styles.

For multiple nonadjacent ranges, hold Ctrl while selecting additional ranges on Windows, then apply the format once. This is useful for related cells, but avoid selecting unrelated cells simply because they are easy to reach: a format that makes sense for a date column may be confusing when applied to an ID or a rate.

Common problems: dates, zeros, and values that look wrong

A date displays as a number. Excel stores dates as serial numbers so it can perform date arithmetic. Apply a date format to a genuine date value to show it as a calendar date. However, if the cell contains text that resembles a date, changing its format may have no effect; the text must first be converted to a real date value. Imported dates can also be interpreted differently according to regional conventions, especially when both the day and month are 12 or less.

A long number appears as scientific notation or hashes. General format can use scientific notation for long values, and a narrow column may show hash marks for values that cannot fit in the selected date or number format. Widen the column by dragging the boundary between its column letters or double-clicking that boundary to AutoFit. If the value is an identifier, such as a long account or tracking number, store it as text when preserving every digit is more important than performing arithmetic.

Leading zeros disappear. Excel normally treats an entry such as 0042 as the number 42 and drops the zeros. For an identifier, use Text format before entering or importing the data, or use a custom format such as 0000 when a fixed-width visual display is enough. The custom pattern makes 42 display as 0042, but the stored number remains 42. That distinction can matter when exporting or comparing the value elsewhere.

Changing to Text does not fix old entries. A number that was already entered remains a number after you change its cell format to Text. For data you need to convert, use an appropriate conversion method, such as Text to Columns for a selected column, and inspect the results. Make a copy of important source data first, particularly when imported values include leading zeros or mixed formats.

The format appears not to change. Check whether the cell contains text, whether a conditional formatting rule is overriding the appearance, and whether the column is wide enough. A formula returning a text string will not respond like a numeric result. To inspect conditional formatting, select the cell and use Home > Conditional Formatting > Manage Rules. Also check that you selected the intended cells and clicked OK in Format Cells.

Windows, Mac, and Excel for the web

The instructions above are for Excel for Microsoft 365 on Windows. On Windows, Ctrl+1 opens Format Cells. Excel for Mac has a corresponding Format Cells dialog; if a keyboard shortcut is inconvenient or differs with your keyboard settings, select the cells and use the Format Cells command from the menus or the Home tab’s number-format controls. The core categories and the idea of custom number formats are similar, though some shortcuts and interface details differ.

Excel for the web also provides number-format choices on the Home tab, including the number-format dropdown. Its available controls can differ from the desktop dialog, especially for detailed custom formats. If the specific custom format you need is not available in the web interface, open the workbook in the desktop app and use Format Cells. A workbook’s existing formats may still display in the browser even when the web interface does not offer every desktop editing option.

Frequently Asked Questions

How do I change the number format in Excel?
Select the cells, then choose a format from Home > Number, or press Ctrl+1 on Windows and choose a category on the Number tab. Select OK to apply it.

How do I change Excel from date format to number?
Select the date cells and choose General or Number from Home > Number. This shows the underlying serial number for real Excel dates; it does not turn text that looks like a date into a numeric date value.

How can I keep leading zeros in Excel?
For identifiers, format cells as Text before entering or importing values. If a fixed-width display is sufficient, use a custom format such as 00000; the underlying value remains numeric, so consider how it will be exported or used.

Does changing number format change the actual value?
Usually not. It changes how an existing value is displayed, while calculations continue to use the stored value. Text conversion and rounding are separate issues and require more than a display-format change.

Why does Excel show #### instead of a number or date?
The column is often too narrow for the selected display. Widen it or AutoFit the column. In some cases, hash marks can also appear when a date or time value is outside the range Excel can display as a date.

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.