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.
SEE: Software usage policy (Tech Pro Research)
A bonus Microsoft Excel tip
From the article 10 things you should never do in Excel by Susan Harkins:
Destroy data: Users sometimes use the same spreadsheet for new data by overwriting the existing data. For example, you might enter this week’s sales figures over last week’s–destroying last week’s data. In the short run, this process seems innocent enough, but if the data’s important enough to record, it’s probably important enough to keep, at least for a while. Instead of overwriting existing data, use a template to enter new data and name the sheet or file appropriately. Keep your data intact so you can refer to and evaluate it later, beyond its seemingly short shelf life.
This bonus Excel tip is also available in the free PDF 30 things you should never do in Microsoft Office.
Editor’s note on Feb. 14, 2019: This Excel article was first published in December 2009. Since then, we have included a video tutorial, added a bonus tip, and updated the related resources.
Affiliate disclosure: TechRepublic may earn a commission from the products and services featured on this page.