To put a leading zero in front of a value in Excel, use a custom number format if you only need the zero to show, or store the result as text if the zero must be part of the value (as with an ID or product code). For example, a custom format can display the numeric value 123 as 00123, while a text formula can turn 123 into 0123. Choose the method that fits how you will use the data.
What You’ll Need
- Excel for Microsoft 365 on Windows, or access to Excel for the web or Mac if that is where your workbook is open.
- The cells that contain the numbers, and a decision about whether the leading zero is for display only or must be stored as part of the entry.
- If you plan to use a custom number format, know the total number of digits you want displayed. For example, five digits means a value of 123 should appear as 00123.
Step-by-Step
-
Decide whether the zero is visual or part of the data. If the value represents an amount you calculate with, it is usually best to keep it numeric and use a number format. If it is a code, phone number, ZIP or postal code, or account identifier, it is usually better to store it as text. Excel treats a number and text differently when sorting, comparing, or calculating, so make this choice before filling a large range.
-
Show a leading zero with a custom number format. Select the cells, then press Ctrl+1 on Windows to open Format Cells. On a Mac, use Command+1. In the Number tab, select Custom, click the Type box, enter the appropriate number of zero placeholders, and select OK. For a five-digit display, enter
00000. A value of 123 will display as 00123, while its underlying value remains 123.This method is useful when all values should have the same displayed length. If your IDs should be six digits, use six zero placeholders, such as
000000. Excel adds zeros at the start only to fill the specified width; it does not remove digits from longer numbers. Thus, a value of 1234567 with a six-digit format still displays as 1234567.To apply the format to a whole column, select the relevant range or click the column heading before opening Format Cells. Applying it to a defined range is often safer than formatting an entire worksheet column, especially in a large workbook with different kinds of data.
-
Enter new values as text when the zero must be retained. Select the destination cells before typing. On Windows, go to Home > Number Format and choose Text. On a Mac, use the Number Format control on the Home tab and choose Text. Now enter the full code, including its first zero, such as 00123. Excel should retain the characters instead of removing the first zero.
You can also type an apostrophe immediately before an entry, such as
'00123. Excel uses the apostrophe to mark the entry as text and does not show that apostrophe in the cell. This is convenient for a few entries, but formatting the cells as Text first is easier for a large batch. If you have already typed the number and Excel removed the zero, typing an apostrophe afterward will not restore it; you must enter the full value again or use a formula. -
Add one zero to existing values with a formula. If the original numeric value is in A2, enter this formula in B2 to create text with one zero at the front:
="0"&A2For example, if A2 contains 123, B2 returns the text 0123. Fill the formula down by dragging the fill handle or double-clicking it. This method adds one zero regardless of the original number’s length; it is different from a fixed-width number format, which pads only to a chosen total length. Keep the original column until you have checked the results.
-
Use a formula to pad values to a set width, if that is the goal. With the original numeric value in A2 and a required five-character display, use:
=TEXT(A2,"00000")If A2 contains 123, the result is the text 00123. A value that already has five or more digits is not shortened. This is useful for creating consistently sized codes, but the result is text and is not a numeric value for arithmetic. The format string must match your required width; change the number of zero placeholders for a different length.
-
Check the results before replacing anything. Compare several outputs with their source values, including the shortest and longest entries. If you want to replace the original column with formula results, copy the results and use Home > Paste > Paste Values. This replaces formulas with their current text results. Keep a backup or a copy of the source column first, because replacing it makes it harder to recover the original numbers.
In Excel for the web, you can use the Home tab’s Number Format control to set cells to Text. Ribbon options for custom formats can vary; if you do not see a Custom option, open the workbook in the desktop app to apply the fixed-width format. The Ctrl+1 and Command+1 shortcuts described above are for the Windows and Mac desktop apps, respectively.
Common Mistakes
- Expecting a number to keep a typed leading zero. Excel normally stores numeric entries as numbers and drops zeros at the beginning. Format the cells as Text first, or use a custom format for display.
- Confusing a displayed zero with a stored zero. A custom format may show 00123 while the cell’s formula bar and underlying value are still 123. That is correct for display formatting, but it may not satisfy an export or lookup that needs the actual text 00123.
- Using a fixed-width format when you mean “add exactly one zero.” The format
00000pads a number to at least five digits. It does not add a single zero to every value. For that, use text entry or the concatenation formula. - Trying to recover digits Excel already removed without checking the source. If 00123 was entered as a number, Excel stores 123. A format can display a chosen number of zeros, but it cannot tell whether the original value was 0123, 00123, or 000123. Confirm the intended width from the source data.
- Assuming formula results are still numbers. The concatenation formula and TEXT function return text. This is usually appropriate for identifiers, but formulas that expect numeric inputs may not behave as intended with text.
Tips
- Use text for identifiers. Phone numbers, postal codes, product codes, and account references generally identify something rather than represent an amount. They do not need to participate in arithmetic, so storing them as text avoids losing significant leading zeros.
- Use formatting when calculation matters. A custom number format changes how the value looks without changing the underlying number. That lets you display a fixed width and still use the cell in arithmetic or numeric analysis.
- Apply the format before pasting or importing. If you paste values into cells already set to Text, Excel is less likely to convert digit strings into numbers. For imports, check the import settings and specify that the relevant column is text when the data consists of codes.
- Keep a clear distinction between a code and a quantity. A code such as 00123 may be meaningful only as a sequence of characters. A quantity of 123 is a number. Give the columns clear headings so other people know whether they should sort, compare, or calculate them as text or numbers.
- Check downstream systems. A custom display format may not be included when data is copied into a plain-text file or another application. If the leading zero must travel with the value, create text results and verify a sample after export.
Troubleshooting
The zero disappears as soon as I press Enter. Excel is interpreting the entry as a number. Undo if possible, set the cells to Text through Home > Number Format > Text, and enter the full value again. For a one-off entry, retype it with an apostrophe before the first digit. If this is an existing numeric column, use a formula to create text results instead.
The custom format does not show enough zeros. Check how many placeholders are in the Type box. A format of 00000 makes values at least five digits wide; it does not guarantee a longer value will have just one zero added. Increase the placeholder count if the required fixed width is longer. If the requirement is one extra leading zero for every value, use the text formula instead.
I see the formula in the cell instead of its result. The cell may be formatted as Text, or Excel may be in Show Formulas mode. For a formula cell, change its format to General, then edit the cell and press Enter so Excel evaluates it. On Windows, Ctrl+` toggles Show Formulas; on Mac, the shortcut can vary with keyboard layout, so use the Formulas tab’s Show Formulas control if needed. Make sure the formula starts with an equals sign.
The result is ###### or the column looks too narrow. This usually means the column is not wide enough to display the formatted value. Widen the column by dragging the boundary between its column headings, or double-click that boundary to AutoFit. The hashes do not mean the leading-zero format failed.
Zeros appear on screen but vanish when I export or copy the data. The cells may have a display-only custom format. Use a text formula or store the entries as text if the zeros must be part of the exported characters. Then copy and paste values into a new range, and test the export with a few records before processing the full dataset.
A formula gives an unexpected result for blank cells. A concatenation formula can return a zero character for an empty source cell, because it adds the prefix regardless. Filter or inspect blank source rows before filling formulas down, and remove or handle blanks separately if they should remain empty.
Frequently Asked Questions
Can I add a leading zero without changing the number?
Yes. Apply a custom number format such as 00000 to display a five-digit width. The stored numeric value remains unchanged, so calculations continue to use the original number.
How do I add exactly one zero in front of every existing value?
Use a text formula that joins the character 0 to the value, such as ="0"&A2 when the source is in A2. Fill it down and check the output. It returns text, not a numeric value.
Can I keep leading zeros in a phone number or ZIP code?
Yes. Set the cells to Text before entering or pasting the data, or create text results from existing values. For a fixed-length numeric code, a custom number format can show the zeros, but it does not make them part of the underlying value.
Why does Excel remove zeros at the beginning?
Excel treats an entry made only of digits as a number, and leading zeros do not change a number’s numeric value. Excel therefore omits them unless you store the entry as text or apply a format that displays them.
Can I use the leading-zero value in a calculation?
A custom-formatted cell remains numeric and can be used in calculations. Text created with an apostrophe, the concatenation operator, or TEXT is an identifier-like string, so convert or handle it deliberately if a later calculation needs a number.
Related
- Find Matching Names in Excel Columns: Easy Methods
- Find Mean in Excel with AVERAGE and Examples
- Find Links to Other Workbooks in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel File With Table Filters: How to Add and Use ThemYes—an Excel file can use table filters to show only the rows that match criteria, without deleting or…
- How to Join Cells in Google Sheets: Merge or CombineTo join cells in Google Sheets by merging them, select the adjacent cells, then choose Format > Merge…
- Excel Conditional Formatting for Dates After TodayTo highlight dates after today in Excel for Microsoft 365 on Windows, select the date cells, choose Home…
- Open a JSON File in Excel and Convert It to XLSXTo open a JSON file as spreadsheet data in Excel for Microsoft 365 on Windows, go to Data…
- How to Double Freeze Panes in Excel: Rows and ColumnsTo double freeze panes in Excel—that is, keep both rows and columns visible while you scroll—select the cell…
- Add Checkbox in Google Sheets: Steps and UsesTo add a checkbox in Google Sheets, select the cell or range where you want it, then choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.