Excel Number Format: Keep Leading Zeros in Your Cells

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, apply a custom number format such as 00000 to display a number with leading zeros; for identifiers that must remain exactly as entered, store them as text instead. A number format changes how a numeric value looks, not the value Excel stores.

What a number format does

Excel normally treats an entry such as 00123 as the number 123 and displays it without the leading zeros. That is because zeros at the start of an ordinary number do not change its numeric value. A custom number format tells Excel to show extra zeros in front when displaying a number, while keeping the underlying value numeric.

For example, if a cell contains the number 123 and uses the format 00000, it appears as 00123. The cell still contains 123: formulas, sorting, and calculations use that numeric value. The zeros are a display effect, not additional digits stored in the value. This distinction is useful when a field looks numeric but is actually an identifier, such as an employee number, postal code, product code, or account reference.

To check what is stored, select the cell and look at the formula bar. If it shows 123 while the grid displays 00123, the cell is numeric with a custom format. If it shows 00123, the value is likely text. This difference matters when you export data, compare values, or use the column in calculations.

Apply a custom format in Excel

To show numbers with a fixed minimum number of digits, create a custom format. In Excel for Microsoft 365 on Windows:

  1. Select the cells you want to format.
  2. Press Ctrl+1 to open Format Cells. You can also use the Number Format dialog launcher on the Home tab.
  3. On the Number tab, select Custom.
  4. In the Type box, enter the required number of zeros, such as 00000 for at least five digits.
  5. Select OK and check a few cells of different lengths.

On Excel for Mac, select the cells and press Command+1 to open Format Cells, then use Number > Custom and enter the format. In Excel for the web, open the Number Format menu on the Home tab and choose More Number Formats to reach the custom format options; the precise dialog layout can differ from the desktop app.

Formatting cells before entering or pasting data can make a sheet easier to work with, but it will not stop Excel from interpreting an entry as a number. If Excel converts 00123 to 123, a custom format can make it look padded again only if you know the intended width. For values that must retain their original characters, set the cells to Text before entering or importing them.

Choose the right number of zeros

A format made entirely of zeros specifies a minimum displayed width. Each zero is a required digit position; Excel adds zeros at the left when the number has fewer digits. The format does not truncate longer values.

  • 000 displays 7 as 007 and 85 as 085; 1234 remains 1234.
  • 00000 displays 123 as 00123 and 12345 as 12345.
  • 00000000 is suitable when a numeric code should display with at least eight digits.

Choose the width from the rules of the data, not just from the longest value currently visible. If all product codes are defined as eight characters, use eight zeros or store the codes as text. If code lengths vary and the zeros are meaningful characters in each individual code, text is usually safer than a fixed-width numeric format.

A custom format can also include sections separated by semicolons for positive numbers, negative numbers, zero, and text. Most people needing padded identifiers only need a simple sequence of zeros, however. Extra sections make a format harder to maintain and do not turn a numeric cell into text.

When text is the better choice

Use text when the contents identify something rather than represent a quantity. Postal codes, telephone numbers, invoice references, and many ID fields are not values you add or average. Their punctuation, length, and leading zeros are part of the label. Storing them as text preserves those characters without depending on a display rule.

To enter a single value as text, type an apostrophe before it, for example '00123. Excel uses the apostrophe as an entry marker and does not display it in the cell. For a whole column, select the cells, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, choose Text, and then enter or paste the values. Formatting after the zeros have already been removed cannot reconstruct unknown digits.

Text values may behave differently from numbers in calculations and sorting. They are not automatically treated as quantities, and a text value of 0010 may sort differently from numeric 10. That is generally appropriate for codes, but if you need both a display code and numeric calculations, keep a separate numeric field rather than relying on the code column.

Convert numbers that already lost their zeros

If Excel has already converted a code to a number, a custom format is the quickest fix when every code has a known fixed width. For example, format the cell as 00000 to display 123 as 00123. This does not alter the stored value and is sufficient when the task is only to show a consistent number of digits in the worksheet.

If you need a text result instead, use the TEXT function with a format pattern. With the original numeric codes in A2:A20, put this formula in a neighboring cell and fill it down:

=TEXT(A2,"00000")

For an original value of 123, the formula returns the text 00123. The five-zero pattern sets the minimum width, so a longer number is not shortened. Once you have checked the results, you can copy the formula results and use Paste Special > Values if you need fixed text rather than formulas. Keep the original column until you have verified the converted values.

This method cannot restore zeros if their intended number is unknown. If the source might contain codes of different lengths, or an identifier was damaged in a way a fixed width cannot describe, recover the values from the original source rather than guessing.

Import CSV files and preserve identifiers

Opening a CSV by double-clicking it can let Excel infer data types. A column containing codes such as 00123 may be interpreted as numbers, so zeros disappear. CSV files do not preserve Excel cell formatting, so a custom number format in a workbook is not a dependable way to carry the rule into a separate CSV file.

For a controlled import in Excel for Microsoft 365 on Windows, use Data > From Text/CSV rather than opening the file directly. In the preview, choose Transform Data when available, select the identifier column in Power Query, and set its data type to Text before loading. Check the loaded column for the expected characters. If the source itself contains 00123 as text, this approach preserves it as an identifier rather than treating it as the number 123.

When exporting or sharing a CSV, inspect the resulting file with a text editor or import it into a fresh workbook to confirm the codes are intact. CSV is plain text and does not carry the workbook’s custom formats, formulas, or other spreadsheet presentation settings. If recipients need the displayed format and workbook behavior, share an Excel workbook instead, while still keeping identifier fields as text when appropriate.

Limits and common mistakes

Custom number formats work for numeric values and change only their display. They do not fix the underlying data type, convert text to numbers, or add characters to stored values. A cell that contains the text 123 will not necessarily respond to a numeric custom format in the same way as the number 123.

Excel also has a limit of 15 significant digits for numeric precision. Long account numbers or other identifiers with more than 15 digits should be imported or entered as text; otherwise, digits beyond the supported precision can be changed. Adding zeros with a format cannot recover digits that Excel has already rounded or replaced.

  • If the grid shows the wrong width, check the selected cells’ Custom format in Format Cells.
  • If the formula bar shows a shortened number, use a suitable fixed-width format only if the intended width is known.
  • If leading zeros must be part of the actual value, make the column Text before entry or import.
  • If values lose digits after the fifteenth significant digit, re-import them from the source as text.
  • If a CSV loses the appearance from the workbook, remember that cell formatting is not part of the CSV format.

Frequently Asked Questions

Can Excel keep leading zeros without changing the value?
Yes. Apply a custom format such as 00000. The cell stays numeric, while Excel displays at least five digits. Choose Text instead if the zeros must be stored as characters.

Why does Excel remove zeros when I type them?
Excel usually interprets an entry made only of digits as a number. Since leading zeros do not affect a number’s value, they are not shown under the General format. Use a custom format for numeric display or enter the value as text.

Will a custom format add zeros to a CSV file?
A custom format belongs to the Excel workbook and is not embedded in CSV formatting metadata. Do not assume the visual padding will transfer. Check the exported CSV and store identifiers as text when the zeros need to travel with the data.

Can I keep different numbers of leading zeros in the same column?
A fixed custom format gives numbers the same minimum width, not an individually specified length. If each code has a different meaningful length, store the codes as text so each value retains its own characters.

Can I restore zeros after Excel has removed them?
Only if you know the intended width or can consult the original data. A custom format can display a known fixed width, but it cannot tell whether a shortened value originally had one, two, or more leading zeros.

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.