It’s quite a common requirement to add leading zeros to numbers in Excel. Code numbers often use leading zeroes and numbers are often more presentable when shown with leading zeroes.
There are two different ways to add leading zeros to numbers in Excel.
Using a custom format
If you try entering the number 0005 into an Excel workbook, Excel will ignore the leading zeros and simply display 5. By default Excel just displays the cell’s value without any special formatting; leading zeros are considered to be formatting because they don’t affect the cell’s value.
To add leading zeros you’ll need to use the custom format: 0000
To apply it to your data, follow these steps:
- Select the data that you want to format.
- Right-click on the data and click Format Cells from the shortcut menu.
- Click Custom in the Category pane.
- Select 0000 from the Type pane (or type it into the box at the top of the pane).
- Click OK.
After applying the custom format the numbers will be displayed with leading zeros but will still be treated as numbers and usable in mathematical calculations.
You can see much more information about custom formats in our Essential Skills Books and E-books.
Formatting numbers as text
Excel allows cells to be formatted as Text. If you enter numbers into a Text cell they will be treated as text instead of numeric values. Leading zeros won’t be removed, but Excel also won’t allow them to be used in mathematical calculations.
There might be rare occasions when you would want to store numbers as text, but the custom format method used above is usually the best solution.
Excel displays a green ‘warning’ triangle when you enter numbers as text.
Sample workbook
You can download a sample workbook showing both solutions in action.
