Entering the leading zero in excel
By default, Microsoft Excel will not display or store leading zones. Read this Excel tip to learn three easy ways to solve the problem.
Leading zeros are those 0 points holding those pesky placements at the beginning of a number. Unfortunately, when you enter such a value, Excel implements the normal numeric form and removes leading zero. Excel will not display or store leading 0 points (see Formula Bar).
When these values are text - part number, zip code, and so on - then you have no problem. Just before price with an apostrophe character (') You can tell that the value is text because it is left-aligned and you can see epostrop in formula bar.
Occasionally, these entries are correct numerical values and you will need to evaluate them in mathematical processes. When this happens, you can force Excel to display placeholder 0 points:
Select the cell or range in question. Right-click on the selected cell or range and select Format Cells from the context menu. Click on the number tab. Choose Custom from category list. In the Type field, enter the number of 0s required to adjust the largest value. For example, if the largest value is four digits, then enter four 0.
6. Click Ok and Excel displays the key values
Your users can enter leading zero (s), or not - Excel will not care about it. However, this particular format does not work with decimal values. If you enter a value, .7, rounds the value of format 1. To adjust the decimal values, enter the placeholder for the appropriate number of digits. For example, if you want Excel to display four digits on the left and two digits on the right side of the decimal, then enter the custom format 003.00.
A bonus Microsoft Excel tip
10 things that you should never do by Susan Hurkins in Excel:
Delete data: Users sometimes use the same spreadsheet for new data by overwriting existing data. For example, you can record sales statistics for this week while destroying data from last week of last week. In a short time, this process seems to be flawless enough, but if the data is important enough to record, then it is important that it be kept for at least some time. Instead of overwriting existing data, use the template to enter new data and give the sheet or file a proper name. Keep your data intact so that you can later mention it and evaluate it, which is beyond its short-lived shelf life.
This bonus excel tip is also available in free PDF 30 things you should never do at Microsoft Office.
Editor's note on February 14, 2019: This Excel article was first published in December 2009. Since then, we have updated a video tutorial, a bonus tip, and related resources.