To display leading zeros in Excel without changing a number’s value, select the cells, press Ctrl+1, choose Number > Custom, and enter a format such as 00000. The number 73 will then appear as 00073, while Excel still stores it as the number 73.
What a leading-zero number format does
A custom number format changes how a value looks in a worksheet; it does not add zeros to the stored value. That distinction is useful when a column contains numbers that need a consistent width, such as product codes, postal codes, or internal reference numbers, but should remain usable as numbers. A five-position format displays short values with enough zeros to fill five positions. Values that already have more than five digits are not shortened.
For example, applying 00000 to the numeric values 73, 412, and 123456 displays them as 00073, 00412, and 123456. In a formula or calculation, those cells still behave as 73, 412, and 123456. The zeros are a display instruction, not extra digits stored in the cell.
This is different from typing an apostrophe before a value, as in '00073, or setting a cell to Text before entering the digits. Those approaches store the entry as text. The right choice depends on whether the zeros are merely for presentation or are part of an identifier that must be preserved exactly.
Apply a custom format in Excel for Windows
In Excel for Microsoft 365 on Windows, use these steps to apply a five-digit display:
- Select the cells you want to format. You can select an entire column if new values in that column should use the same display.
- Press Ctrl+1 to open Format Cells. Alternatively, on the Home tab, use the Number group’s dialog box launcher.
- On the Number tab, choose Custom from the Category list.
- Click the Type box, enter
00000, and select OK.
Format codes are not case-sensitive in this example, but the number of zero placeholders matters. Use 000 for a minimum of three displayed digits, or 000000 for a minimum of six. The format does not force every value to exactly that length: a value longer than the specified width remains fully visible.
On a Mac, select the cells and press Command+1 to open Format Cells, then choose Custom and enter the code. Excel for the web can display many custom formats, but the controls available to create or edit them may differ. If the Custom category or Type box is unavailable in your browser, open the workbook in the desktop app to set the format.
Choose the right format code
In a custom number format, the digit placeholder 0 requires a digit to appear in that position. Use it when you want zero-padding. The # placeholder displays a digit only when needed, so it does not pad a short number with leading zeros. The ? placeholder also leaves an unused position blank, which can help align decimal points, but it is not the usual choice for padded codes.
00000displays 8 as 00008 and 246 as 00246.000.00displays 8.4 as 008.40, showing three positions before the decimal and two after it.000-000displays 12345 as 012-345. The hyphen is a literal separator in the display; it is not a calculation or a change to the stored number.
Choose a pattern based on the actual structure of the values, not just the longest example in the current list. A format such as 00000 is a minimum width of five digits. It does not validate that a value is a valid five-digit code, prevent someone from entering a decimal, or reject a number with too many digits. Use data validation or a text-based workflow if you need to enforce rules as well as control appearance.
When to use text instead of number formatting
Number formatting is appropriate when the cell contains a quantity that should remain numeric, or when a fixed-width display is all you need in the worksheet. It is often suitable for a numbered sequence or an internal code that will be used in numeric sorting and calculations. But many identifiers are not truly numbers. Phone numbers, account references, postal codes, and serial numbers are labels: adding or averaging them has no meaningful result.
For identifiers, text is generally safer when every character must be preserved exactly, especially if the data may be imported, exported, or shared with another system. Text can retain leading zeros regardless of how another application interprets numeric formatting. It can also preserve characters such as a plus sign in a phone number. The trade-off is that text does not behave like a number in arithmetic, and numeric sorting may not work as expected. Decide based on how the data will be used, not just how it should look.
To enter a single text value in Excel, type an apostrophe before the digits; Excel uses it to indicate text and does not show the apostrophe in the cell. For a full imported column, set the column’s data type to Text during import rather than relying on a later number format to recover zeros that Excel has already discarded.
Display zeros in a formula result
A cell’s custom format controls its display, but a formula can also produce a text string with a chosen number of digits. This is useful when a formatted version needs to be assembled with other text or passed to a process that expects text. The TEXT function returns text, so the result is no longer a numeric value for ordinary arithmetic.
With the number 73 in A2, enter this in B2 to return a five-digit text string:
=TEXT(A2,"00000")
The result is the text value 00073. If you only need zeros to show in a worksheet cell, formatting A2 is usually simpler and keeps the underlying value numeric. If you concatenate the value into a label, apply TEXT to the number before combining it with other text, because a cell’s visual format is not necessarily carried into a concatenated result.
Be aware that a formula result based on TEXT may be treated as text in lookups, sorting, or comparisons. If a later calculation needs the numeric value, keep the original numeric cell and use the text version only where a text representation is required.
Importing, sorting, and exporting data
A custom format can only display zeros that are implied by the value and format. If Excel imports a code such as 00127 as the number 127, applying 00000 may make it look right again, but it cannot tell whether the original code had five digits or a different length. If the source has variable-length identifiers, the original zeros may be impossible to infer once they have been removed.
For CSV or text files, inspect the import settings before loading a column of identifiers. In desktop Excel for Windows, Data > From Text/CSV opens the import workflow; where the Power Query editor is available, set the relevant column’s data type to Text before loading it. The exact import controls can differ by source and platform. For a quick paste, format the destination column as Text before pasting, then check a few entries against the source.
Sorting numeric values sorts by their underlying numbers, not by the visible padded strings. Thus numeric 2 appears before numeric 10 even if their displays are 00002 and 00010, which is usually desirable for numeric sequences. If the codes are text, sorting is lexicographic: the order follows characters from left to right. Fixed-width text codes often sort intuitively, but mixed lengths or letters can produce a different order from numeric sorting.
CSV files do not preserve Excel’s cell formatting. When you save a workbook as CSV, the file contains text values rather than the workbook’s custom display rules. If a recipient must receive 00073 as literal characters, create or export a text value, and inspect the resulting file. Do not assume that the custom format alone will travel with the data.
Common problems and how to fix them
- The zeros do not appear: Check that the cells are numeric and that the format was entered under Custom. Confirm the code has the intended number of zero placeholders, then reopen Format Cells to verify it was applied to the selected range.
- The displayed value is still too short: A code of
00000pads to at least five places, not to a fixed maximum. Check the source value and whether the intended width is different. - The zeros vanish after reopening a CSV: CSV does not retain workbook number formats. Import the identifier column as Text, or store the required digits as text before exporting.
- The value appears as ####: This generally means the column is too narrow to show the formatted value. Widen the column or use Home > Format > AutoFit Column Width.
- The format changes a code into something unexpected: Check for decimal places, separators, or other characters in the custom code. A number format changes presentation, so use a simple pattern such as
00000when only padding is needed.
Also remember that a format does not check whether an entry is valid. A five-digit display can make a shorter number look like a code, but it cannot confirm that the code exists or belongs to the correct record. For important identifiers, compare against the source system and use a consistent text or import process.
Frequently Asked Questions
How do I add leading zeros in Excel without changing the value?
Select the cells, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, choose Custom, and enter a pattern such as 00000. The value stays numeric; only its display changes.
How do I make every number five digits long?
Apply the custom format 00000. It pads values shorter than five digits, but it does not truncate longer values or validate that the cell contains exactly five digits.
Why does Excel remove leading zeros when I type or import a code?
Excel commonly interprets digit-only entries as numbers, and numbers do not retain unnecessary leading zeros. Format cells as Text before entry or import the identifier column as Text when the zeros are part of the data itself.
Can I keep the zeros when I save as CSV?
Do not rely on the custom number format: CSV does not store Excel’s formatting rules. Export the required digits as text and check the saved file, particularly if another program will open it.
Should postal codes and phone numbers be formatted as numbers?
Usually, they are better treated as text because they identify places or people rather than represent quantities. Text preserves leading zeros and symbols, while numeric formats are useful when only the worksheet display needs padding and the underlying value should remain numeric.
Related
- Excel Redo Shortcut: How to Reapply an Action
- Excel Group/Ungroup Shortcut: Keys, Steps, and Fixes
- Excel Keyboard Shortcut for Inserting Copied Cells
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Track Changes in Word: How to Review and Accept EditsTo use Track Changes in Microsoft Word, open the Review tab and select Track Changes, then edit the…
- CSV vs ODS in Excel: Which Should You Use?In Excel, choose CSV when you need a simple, widely compatible file of flat data; choose ODS when…
- Shortcut Key to Insert a Checkbox in Microsoft WordIn Word for Microsoft 365 on Windows, type 2610 and press Alt+X to insert an empty checkbox symbol…
- Biweekly Timesheet Template for Google Sheets: SetupYes—make a biweekly timesheet in Google Sheets with one row for each of 14 days, columns for date,…
- Outlook Share Calendar Greyed Out? Causes and FixesIf Outlook’s Share Calendar button is greyed out, the calendar or account may not support sharing, or your…
- Can Excel Goal Seek Multiple Cells at Once?No. In Microsoft Excel for Microsoft 365 on Windows, Goal Seek can adjust only one changing cell to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.