Excel Save Custom Number Formats: A Practical Guide

How-to basics · Updated November 19, 2026

Excel saves a custom number format inside the workbook: select the cells, press Ctrl+1, choose Number > Custom, enter a format code in Type, and select OK. Save the workbook as an Excel file, such as .xlsx, to keep the format; the displayed appearance changes, but the underlying cell value does not.

Where Excel Keeps a Custom Format

A custom number format belongs to the workbook in which it was created or used. Once you apply it, Excel adds the code to that workbook’s available custom formats, so you can select it again for other cells in the same file. Saving and reopening the workbook normally preserves both the format and its application to cells.

The format is not a global setting that automatically becomes available in every workbook you open. If you create a useful code in one file, a separate workbook may not show it in its Custom list until you transfer or recreate it there. This distinction matters when you build reports repeatedly: saving the first workbook preserves the format in that file, but does not by itself make a reusable format library for all future files.

For reliable retention, save in a format that supports Excel formatting, such as .xlsx for a standard workbook or .xlsm for a macro-enabled workbook. Older .xls files also support custom number formats, but have older file-format limits. Avoid choosing a text-based export when you need the formatting preserved.

Create and Save a Custom Number Format

In Excel for Microsoft 365 on Windows, use these steps:

  1. Select the cells whose display you want to change.
  2. Press Ctrl+1 to open Format Cells. You can also use the dialog launcher in the Number group on the Home tab.
  3. On the Number tab, select Custom in the Category list.
  4. Choose a similar code in the Type box and edit it, or type a new code.
  5. Check the Sample preview, then select OK.
  6. Save the workbook with Ctrl+S, or use File > Save.

The format is saved with the workbook once it is saved. If you want to use the code on another range in that same workbook, select the range and choose the code from the Custom list. On a Mac, Command+1 opens Format Cells; the dialog’s Number > Custom options are similar. Excel for the web can display many existing number formats, but creating or changing custom codes is best done in the desktop app.

Typing a code into the Type box does not alter the value in the selected cells. It changes how Excel presents that value. If a format seems to do nothing, first check that the selected cells contain the kind of data the code is designed to display.

How Custom Number Format Codes Work

A number format is a set of display instructions, not a formula or data conversion. For example, the code 00000 displays the number 42 as 00042. The stored value remains 42, so calculations still use 42. This is useful for fixed-width IDs or codes that are numeric but need leading zeroes. If the identifier is not meant for arithmetic, storing it as text may be more appropriate.

Common symbols include:

  • 0 displays a digit and adds a zero when a position is otherwise empty.
  • # displays a digit when one exists, but does not force an extra zero.
  • ? reserves space for insignificant zeroes, which can help align decimal places.
  • A comma can group thousands, while a period marks the decimal position in typical English-language settings.
  • A percent sign displays a value as a percentage; a number such as 0.25 displays as 25% with 0%.

Excel format codes can have up to four sections separated by semicolons: positive numbers; negative numbers; zero values; and text. For instance, #,##0;[Red]-#,##0;"—" displays whole numbers with grouping, shows negatives in red with a minus sign, and displays zero as a dash. The quoted dash is literal display text; it does not replace the underlying zero. A fourth section can specify how text is shown, often using the @ placeholder.

Choose a Format That Matches the Data

Start with what a reader needs to see, then choose a code that communicates it without implying a different value. A percentage, for example, is stored as a decimal fraction: 0.075 formatted as 0.0% appears as 7.5%. If someone types 7.5 into that cell, Excel will display 750.0%, because it formats the stored value as a percentage rather than interpreting the entry as “7.5 percent.”

For currencies, select a built-in currency or accounting format when it meets the need. A custom format is useful when you need a particular mix of symbols, decimal places, negative-number display, or zero display. Be careful with codes copied from another locale: Excel’s decimal and thousands separators, currency symbols, and date conventions can vary with regional settings.

Dates and times also use number formats. Excel stores dates as serial values and displays them in a date style; a custom date code changes the presentation, not the date itself. For example, a date can be displayed with a month name or as a compact numeric date. Because letters can have special meanings in date and time codes, do not assume that text typed into a format code will always appear literally; quote literal text where needed and check the Sample preview.

Reuse a Custom Format in Other Workbooks

There are several practical ways to reuse a format when the destination workbook does not already contain it:

  • Recreate the code: Open Format Cells > Number > Custom in the destination workbook and enter the same code. This is usually the simplest option for one or two formats.
  • Copy formatted cells: Copy cells that use the format, then paste them into the other workbook. If you only want the formatting, use Paste Special > Formats where available, and check that the destination cells retain the intended values.
  • Use Format Painter: Copy the appearance from a cell with the custom format and apply it to another range. Verify the result, especially if the source cell also has other formatting you do not want.
  • Use a template: If you create the same kind of workbook repeatedly, save a prepared workbook as an Excel template (.xltx), or as .xltm if it contains macros. Start new workbooks from that template so its formats are included.

A template is a dependable approach for a recurring report because it can preserve more than number formats, including sheet layouts and other formatting. Still, it is worth checking a newly created file before sharing it: formats may be present but not applied to new rows or newly added sheets automatically.

What Happens When You Export or Share the File

Saving as .xlsx or .xlsm preserves workbook formatting. Exporting or saving as CSV is different: CSV stores plain text values rather than Excel’s cell formatting, formulas, multiple sheets, or workbook structure. If a cell displays 00042 because of the custom format 00000, a CSV export may contain the underlying value 42 instead of the padded display. If the exact visible text must travel in a CSV, prepare a text representation deliberately and inspect the exported file.

Recipients using Excel can generally see the workbook’s saved formats, even if they do not have the custom code in a separate workbook of their own. Other spreadsheet programs may interpret some Excel-specific formatting differently, so check the file in the receiving application when appearance is important. A PDF preserves the visible page output rather than making the number format reusable as an Excel setting.

Custom number formats are not a security feature. Hiding digits with a display format does not remove or protect the underlying value; a recipient can inspect the cell or change its format. Do not use a display code to conceal sensitive information.

Common Problems and How to Fix Them

  • The custom format disappears in another file: The format is workbook-specific. Recreate it, copy formatted cells, or use a template.
  • Leading zeroes do not appear: Confirm the selected cells are numeric and that the code has enough zero placeholders, such as 00000. If the value was imported as text, a numeric format may not affect it as expected.
  • The code is visible in the cell: The cell may be set to Text, or Excel may be treating the entry as text rather than as a format code. In Format Cells, choose Custom and enter the code in Type. Also confirm that the cell contains a value appropriate to that code.
  • A date displays as a number: The value may be a date serial shown with a General or numeric format. Apply a date format. Conversely, if the entry is text that merely looks like a date, number formatting will not convert it into a real date.
  • The output changes after reopening or sharing: Check that the file was saved as .xlsx or another Excel workbook type rather than CSV, and verify that the format was applied before saving.

Excel may also adjust or reject codes that use unsupported syntax, and a code that works in one regional setup may display separators differently in another. Use the Sample preview and test a positive value, a negative value, zero, and any text values the range may contain before applying the format broadly.

Frequently Asked Questions

Does Excel save a custom number format automatically?
Excel keeps the format in the current workbook after you create and apply it, but you must save the workbook to preserve the latest changes. It will not automatically add the code to unrelated workbooks.

How do I save a custom number format for future workbooks?
Save a prepared workbook as an .xltx template and create future files from it, or recreate or copy the format into each new workbook. A regular .xlsx file preserves the code only in that workbook.

Can I save a custom number format as a default for all Excel files?
Excel’s normal custom-format list is tied to workbooks, not a universal personal format library. A template is the straightforward way to start recurring workbooks with your chosen formats already available.

Will a custom number format change the actual number?
No. It changes how a value is displayed. For example, 00000 can make 42 appear as 00042 while the value used in calculations remains 42.

Why did my custom format disappear when I saved as CSV?
CSV does not preserve Excel cell formatting. It exports text values rather than the workbook’s custom number-format instructions, so save an Excel workbook copy if you need the formatting to remain editable.

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.