Numbers such as 1, 10, and 100 can be displayed as 001, 010, and 100 in Excel either by applying custom formatting (visual only) or by converting them to text values that contain leading zeros.

Method 1: Custom number format (visual only)

This method changes how numbers are displayed while preserving their numeric nature for calculations.

• Select the relevant cells.

• Open the Format Cells dialog (for example, via Ctrl+1).

• On the Number tab, choose Custom.

• In the Type box, enter 000 for three-digit codes (or use more zeros, such as 00000, for longer codes).

• Confirm with OK.

The cell values remain numeric (1, 10, 100), but they appear as 001, 010, 100. Formulas such as SUM, AVERAGE, and others continue to work normally.
Custom formats can also mix digits and text, for example 000-00-0000 for ID patterns, or "EN"000 for codes like EN001.

Method 2: Store the code as text

When leading zeros are an essential part of an identifier (serial numbers, ZIP codes, product codes), the value is often better stored as text.

Two common approaches:

• Pre‑format the cells as Text in the Format Cells dialog, Number tab, then enter values such as 001 directly.

• Type an apostrophe before the value, such as '001; Excel displays 001, while the underlying value is stored as text.

In this mode, the content is treated as text rather than a number and is preserved exactly as entered.

Method 3: Formulas that create padded text

If a column already contains numbers (for example, 1, 10, 100) and a new column with fixed‑width text codes is needed, formulas are appropriate.

Assuming the original number is in cell A1:

• =TEXT(A1,"000") produces a three‑character text value with leading zeros where necessary.

• =RIGHT("000"&A1,3) produces a similar result by concatenating zeros and trimming from the right.

• =TEXT(A1,REPT("0",3)) allows the total length (here, 3) to be controlled with a single parameter.

After generating the formatted codes, the results can be converted to static text via copy and Paste Special → Values, if required.

Method 4: Preserving leading zeros during import

During import from CSV or text files, Excel often removes leading zeros by default.

Common strategies to avoid this include:

• Defining the target column as Text before pasting or importing.

• In import wizards or tools (such as Text Import Wizard or Power Query), specifying the column’s data type as Text.

• If zeros have already been removed, applying one of the formulas from Method 3 to reconstruct fixed‑length codes from the numeric values.