To show two leading zeros before a number in Excel, select the cells, press Ctrl+1, choose Custom, and enter "00"0. For a value of 123, Excel will display 00123 while keeping the underlying value numeric; if you need the zeros to become part of a text value instead, use a formula such as ="00"&A2.
First decide what adding 00 means
There are a few different things people mean by “add 00” in Excel. You might want every number to display with two zeros in front, you might want to pad numbers to a set length, or you might need the zeros permanently included in an identifier such as a product code. Those goals call for different methods, so decide what the result must do before changing the sheet.
- Show two extra zeros before a number: A value of 123 should appear as 00123. Use a custom number format if it should remain a number.
- Make every entry a fixed length: A value of 7 might need to appear as 00007, while 123 appears as 00123. Use a format or formula with the required total number of digits.
- Store the zeros as characters: A code like 00123 must remain exactly that when copied, exported, or used as an identifier. Store it as text or create text with a formula.
Excel treats a numeric value and the way it is displayed as separate things. The number 123 can be displayed as 00123 without changing its value. That distinction matters for calculations, sorting, imports, and exports. A phone number, postal code, or inventory code is usually an identifier rather than a quantity, so it often makes sense to preserve it as text.
Display two zeros with a custom number format
A custom number format is usually the simplest method when the cells contain numbers and you want a visual prefix. It changes how Excel presents the values, not the values themselves. On Excel for Microsoft 365 on Windows, select the cells and press Ctrl+1 to open Format Cells. Choose the Number tab, select Custom, enter "00"0 in the Type box, and select OK.
- Select the number cells or the entire column where future entries will go.
- Press Ctrl+1. In Format Cells, choose Number > Custom.
- In the Type field, replace the current code with
"00"0, then select OK.
For example, if A2 contains the numeric value 123, this format displays 00123. If A2 contains 7, it displays 007. The quoted "00" is a literal prefix, and the final zero is a digit placeholder that displays the number. Excel can still add, compare, and calculate with the underlying numeric value normally.
For a format that simply pads values to at least two digits, enter 00 instead. Then 7 displays as 07, 45 displays as 45, and 123 remains 123. This does not add two zeros to every number; it supplies zeros only when the number has fewer than two digits. Choose this when the requirement is a minimum width of two digits, not a fixed two-zero prefix.
To apply a format to a whole column, select the column heading before opening Format Cells. This can make later numeric entries display consistently too. However, formatting does not change text already stored in the cells, and it cannot recover zeros that were removed from a value before it reached the workbook.
Add 00 as text with a formula
If the zeros must be part of the resulting text, use concatenation. With the original numeric value in A2, enter ="00"&A2 in B2 and fill the formula down. If A2 contains 123, B2 returns the text 00123. The formula joins the literal text 00 to the value in A2; the ampersand is Excel’s text-concatenation operator.
Because this result is text, it will not behave exactly like a number in arithmetic. For a series of codes, that is often desirable: Excel should not add two product codes together or treat them as quantities. But if you need the output for calculations, keep the original numeric column or use a display format instead. Also note that concatenation uses the cell’s underlying value, not necessarily its displayed formatting. If A2 contains 1.5, the result is 001.5, not a padded or rounded whole-number code.
To make a static text result from the formula, copy the formula cells and use Home > Paste > Paste Values. This replaces the formulas with their current results. Keep the source column until you have checked the output; deleting the source too early can leave formulas pointing to empty cells.
Pad numbers to a fixed total length
When every code must have exactly five digits, use a five-digit custom number format, 00000. A value of 7 displays as 00007, 123 displays as 00123, and 45678 displays as 45678. This keeps numeric values numeric while displaying at least five digits. Values longer than five digits are not cut off; for example, 123456 still displays as 123456.
If the padded output must be text, use the TEXT function. With the numeric value in A2, enter =TEXT(A2,"00000") in B2. When A2 contains 123, the result is the text 00123. The five zero characters in the format argument specify a minimum displayed width of five digits. To make a seven-character code, use seven zeros in the format argument and adjust the format to suit the data.
Do not confuse fixed-width padding with adding a fixed prefix. For a numeric value of 123, the prefix method produces 00123, and five-digit padding also happens to produce 00123. But for 12345, adding the prefix gives 0012345, while five-digit padding gives 12345. Test the expected output with both a short and a long example before applying the method to a large list.
Keep leading zeros when entering or importing data
Excel commonly interprets entries such as 00123 as numbers and removes the leading zeros because they do not change a number’s value. If the cells are already formatted as General, typing 00123 may therefore leave 123 in the cell. To enter a single text code manually, type an apostrophe before it, as in '00123. Excel uses the apostrophe to indicate text; it does not appear as part of the displayed cell value.
For a larger set of codes, format the destination cells as Text before entering or pasting the data. Select the cells, press Ctrl+1, choose Text, and confirm. This is most useful when you control the entry process. It does not automatically restore zeros that were stripped earlier, and pasted or imported data can sometimes bring its own type interpretation.
When importing a CSV file, check the import options and preview the relevant column. In Excel for Microsoft 365 on Windows, opening a CSV by double-clicking it may cause Excel to infer data types, which can remove leading zeros from values it treats as numbers. If the import workflow lets you specify column types, set code columns to Text before loading. If you already have a numeric value but know every code should be five digits, a five-digit format or =TEXT(A2,"00000") can rebuild the displayed padding. It cannot infer a different original length if the codes had varying lengths and no rule was recorded.
Choose the method that fits your data
Use a custom format when you want the cell to remain numeric and only its appearance needs to change. This is convenient for reports and numeric sequences. The underlying value remains available for sums and numeric comparisons, but a plain-text export may omit the formatting and reveal the unpadded number.
Use text storage or a text-producing formula when the zeros are part of the identity of the entry. This is common with postal codes, account references, employee IDs, and product numbers. The text value preserves the characters when copied to systems that would otherwise interpret the entry as a number. The trade-off is that numeric calculations and numeric sorting may require extra care.
Before settling on a method, consider these questions:
- Will the values be used in arithmetic, or are they labels and codes?
- Must the result have a fixed length, or should every entry receive exactly two prefix zeros?
- Will someone export the data to a CSV file or another application?
- Do existing entries have a known length, or were some zeros already lost?
For code columns that must retain exact characters across applications, text is generally the clearest choice. For numeric data that only needs a consistent on-screen appearance, a custom number format avoids changing the data type.
Common problems and how to fix them
The zeros do not appear. Check whether the cells contain numbers or text. Number formats affect numeric values, while text entries generally display their characters as entered. Also confirm that the custom format is exactly "00"0 for a fixed prefix, or 00000 for five-digit padding. A format with only 00 means minimum two-digit padding, not a literal prefix.
The formula shows 00 followed by decimals or unexpected digits. Concatenation joins the text to the value; it does not automatically round or set a width. If you need five digits, use =TEXT(A2,"00000"). If the source includes decimals and the code should be a whole number, establish the intended rounding or truncation rule rather than silently assuming one.
The zeros disappear after copying or exporting. A custom format may not travel with the value into another program or a plain-text file. If the receiving system needs the literal characters, create or store a text result and check the exported file. An apostrophe used for manual text entry is an Excel entry marker; do not assume it will be included in an export.
The column sorts strangely. Text is sorted as text, while numbers are sorted numerically. Codes of equal length usually sort more predictably as text, but mixed-length codes can sort in a different order from numeric values. Decide whether sorting should follow the characters or the numeric magnitude, and use a consistent data type in the column.
Excel on Mac and Excel for the web
In Excel for Microsoft 365 on Mac, select the cells and use Command+1 to open Format Cells, then choose the custom number format option and enter the same format code. The Windows shortcut is Ctrl+1. The formulas and the distinction between a numeric display format and text apply on both platforms.
In Excel for the web, select the cells and use the Home tab’s Number Format controls to choose additional number formats or a custom format if that option is available in the current interface. If you cannot create the specific custom format in your web session, open the workbook in the desktop app, apply it there, and save the workbook. A formula such as ="00"&A2 or =TEXT(A2,"00000") can also create text results in supported Excel versions, without relying on a custom display format.
Frequently Asked Questions
How do I add 00 in front of a number in Excel?
To show two zeros as a prefix while keeping a numeric value, apply the custom format "00"0. To create a text value with the zeros included, use ="00"&A2, assuming the original number is in A2.
How do I make 1 show as 001 in Excel?
Use the custom format 000 to display numeric values with at least three digits. If you need a text result instead, use =TEXT(A2,"000") with the value in A2. The result for 1 is 001.
Why does Excel remove the zeros at the beginning?
Excel treats a string of digits entered into a General-format cell as a number, and leading zeros do not change that number’s value. Format the cells as Text before entry, use an apostrophe for an individual entry, or apply a number format when only display is important.
Can I add leading zeros without changing the number?
Yes. A custom number format changes the display, not the stored numeric value. For example, a value of 123 formatted with 00000 displays as 00123 but remains 123 for arithmetic.
How can I add zeros to an entire column?
Select the column or the range, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, and apply the appropriate custom format. For text output, enter a formula in the first row and fill it down, then paste values if you need fixed results rather than formulas.
Related
- Replace Words in Excel: Find and Replace Guide
- Randomize Numbers in Excel: Methods That Work
- Print Excel Sheets With Gridlines: Steps and Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Template for HOA Budget: How to Build OneThe fastest way to get an Excel template for an HOA budget is to build one yourself using…
- How to Import an Excel Spreadsheet into WordTo import Excel data into Word, select and copy the cells in Excel, then paste them into Word;…
- How to Set Out of Office Replies in Outlook 2011 for MacIn Outlook 2011 for Mac, open Tools > Out of Office, turn on out-of-office replies, write your message,…
- How to Enable Copy and Paste in Excel When It StopsCopy and paste is normally enabled in Excel for Microsoft 365 on Windows; there is no general switch…
- Add a Watermark to Just the First Page in WordTo add a watermark to just the first page in Word, separate page one from the rest of…
- Databricks STRING Data Type: Uses and ExamplesIn Databricks, STRING is the data type for text: it holds sequences of characters such as names, descriptions,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.