By default, Microsoft Excel won't display or store leading zeros. Read this Excel tip to learn three handy ways to get around the problem.
Leading zeros are those pesky place-holding 0 digits at the beginning of a number. Unfortunately, when you enter such a value, Excel applies the General numeric format and tosses out the leading zero. Excel won't display or store the leading 0 digit (see the Formula bar).
When these values are text -- part numbers, ZIP codes, and so on -- you don't have a problem. Simply precede the value with an apostrophe character ('). You can tell the value is text because it's left-aligned and you can see the apostrophe in the Formula bar.
LEARN MORE: Office 365 Consumer pricing and features
Sometimes, these entries are true numeric values and you will need to evaluate them in mathematical processes. When this is the case, you can force Excel to display placeholder 0 digits by applying a custom format as follows:
- Select the cell or range in question.
- Right-click the selected cell or range and choose Format Cells from the context menu.
- Click the Number tab.
- Choose Custom from the Category list.
- In the Type field, enter the number of 0s necessary to accommodate the largest value. For instance, if the largest value contains four digits, enter four 0s.
6. Click OK and Excel displays leading values
Your users can enter the leading zero(s), or not -- Excel won't care. However, this particular format doesn't work with decimal values. If you enter the value .7, the format rounds the value to 1. To accommodate decimal values, enter placeholders for the appropriate number of digits. For instance, if your want Excel to display four digits to the left and two digits to the right of the decimal, enter the custom format 0000.00.
