How to retain leading zeroes in Excel
Leading zeroes are the zeros at the beginning of a number that serve as placeholders. However, when you enter such values in Excel, it automatically applies the General numeric format and removes the leading zero. Excel neither displays nor stores the leading zero digit, as can be observed in the Formula bar.
If the values you’re working with are text-based, such as part numbers or ZIP codes, there is no issue. Simply precede the value with an apostrophe character (‘), indicating that it should be treated as text. You can identify text values as they are left-aligned, and the apostrophe is visible in the Formula bar.
How to retain leading zeroes in Excel
However, there are instances where these entries are true numeric values that need to be used in mathematical operations. In such cases, you can force Excel to display the leading zero by applying a custom number format as follows:
- Select the cell or range where you want to retain the leading zeroes.
- Right-click the selected cell or range and choose “Format Cells” from the context menu.
- In the “Format Cells” dialog box, click on the “Number” tab.
- Select “Custom” from the “Category” list.
- In the “Type” field, enter the number of zeros needed to accommodate the largest value. For example, if the largest value contains four digits, enter four zeros.
How to retain leading zeroes in Excel
- Click “OK,” and Excel will display the leading values with the retained zeroes.
How to retain leading zeroes in Excel
By applying this format, users can input values with or without the leading zero, and Excel will handle it correctly. However, note that this particular format doesn’t work with decimal values. If you need to accommodate decimal values, you should include placeholders for the appropriate number of digits. For example, if you want Excel to display four digits to the left and two digits to the right of the decimal, use the custom format “0000.00.”
How to retain leading zeroes in Excel
In addition, here’s a bonus tip for using Microsoft Excel:
Preserve data: Users often overwrite existing data when using the same spreadsheet for new data, which can lead to the loss of previous data. Instead of overwriting the existing data, it is recommended to use a template for entering new data and give the sheet or file an appropriate name. This way, you can keep your data intact and refer to it or evaluate it later, even beyond its initial use.
Editor’s note on Feb. 14, 2019: This Excel article was originally published in December 2009. Since then, we have included a video tutorial, added a bonus tip, and updated the related resources.
