Leading zeros are place holding 0 digits at the start of a number.The problem is that when you enter such a value, Excel will apply the General numeric format and get rid of the leading zero.Excel will not display or store that leading zero digit.
Sometimes these values are part text and part numbers, (i.e. zip codes, file numbers, etc.) – not a problem. Just precede your value with an apostrophe character. You ca see the value is text because it is left aligned and you can see the apostrophe in your Formula bar.
Other times, these entries are real numeric values and you will need to evaluate them in mathematical processes. When this occurs, you can force Excel to display placeholder zero digits by using a custom format.
Follow the steps below to learn how:
Select the cell or range .
- Right click the selected cell/range and from the context menu, select Format Cells.
- Click the Number tab.
- From the Category list, select Custom.
- In the Type field, key in the number of zeros necessary to accommodate the largest value (i.e., if your largest value is four digits, enter four zeros).
- Click OK.
Excel will now display the leading values.
You can enter the leading zeros or not. Excel does not care either way. This particular format, however, will not work when using decimal values. If you enter the value .7, the format will round the value to 1. To accommodate decimal value, you must enter placeholders for the appropriate number of digits (i.e. if you would like Excel to display four digits to the left and two to the right of the decimal point, enter the custom format 0000.00.
I receive a lot of inquires regarding this issue, so I hope this has been helpful to you!