Encountering difficulties when inputting values that commence with zero in Excel? The default behavior of Excel involves removing any leading zeros when entering a number, creating challenges when dealing with data like phone numbers or ZIP codes that start with zero. Fear not! In this comprehensive guide, we will explore various methods to successfully input numbers starting with zero in Excel. Whether through the use of an apostrophe, leveraging Text or Special number formats, or crafting a custom number format, you'll discover the solution that best suits your needs.
You have 3 options for enter number starting with zero in Excel:
- Using an apostrophe (‘)
- Using either Text or Special number formats
- Using a custom number format
Let’s see each method in more detail.
Apostrophe (‘)
Excel evaluates any value that starts with an apostrophe as text, and text values can start with zero (0). That’s it! Excel will not show the leading apostrophe character in the beginning when displaying the cell value. You can still see and adjust it when editing the cell value.
Instead of entering an extra character every time, the downside of this method is the fact that you will see an erroneous error indicator for that cell. To get rid of this warning, click the icon on the right-side of the cell, and select Ignore Error item in the list. Please note that choosing Convert to Number here will remove any leading zeros.
Another note regarding this method is that if you want to enter a text that starts with an apostrophe, you may want to consider beginning the text with double apostrophe characters (‘’).
Text and Special Number Formats
Alternatively, you can use Excel’s built-in format options like Text and Special number formats. Number formats alter the display of the values without changing the actual value.
Text number format essentially tells Excel to accept everything inside the cell as a part of a text sting. As a result, Excel doesn’t care whether your number starts with a zero or not.
- To change the number formatting of a cell, first, select the cell or cells you want to update.
- Next, either press the Ctrl + 1 combination or use the right-click (context) menu to open the Format Cells dialog
- Activate the Number tab if it isn’t
- Select Text in the right panel
- Click OK to apply
You can now enter any value without "auto-correct".
If you want to enter values in a more specialized format, check out the options under the Special category. You can select between 4 pre-defined options:
- Zip Code
- Zip Code + 4
- Phone Number
- Social Security Number
The difference between Text and Special number formats is that the Special formats have a fixed structure and number of digits they will accept. This means that you do not need to enter a leading zero because Excel will fill it based on number format. For example, if you select ZIP Code number format and type 123, Excel displays the value as 00123.
Note that in this method, Excel is not actually adding zeros, it;s just altering how you see the value.
Custom Number Format to enter number starting with zero
The final approach is the advanced version of number format method. Excel allows you to code your custom number formatt. So, if the existing formats do not fit your needs, try to creating your own.
To code a custom formatting, open the Format Cell dialog again, and select Custom from the left pane. You need to enter the format code in the Type box and click OK.
Here is a cheat sheet for custom formatting codes:
- 0 represents digits can be filled by a leading zero if there isn’t a number. For example, if you want to a 3-digit number which should have leading zeros, you should use 000.
- If you enter 1, you will see 001.
- 12 will be 012.
- 123 will be 123.
- # represents digits can be filled by a leading zero. For example, if your number format is 0##, leading zero will be added only one time.
- 1 will be 01
- 12 will 012
- 123 will be 123
One more example: Excel’s Special – ZIP number format uses 00000. You can learn more about custom number formats in Number Formatting in Excel – All You Need to Know.
Excel's flexibility shines through as it offers multiple avenues to handle numbers starting with zero. Whether you opt for the simplicity of an apostrophe, the built-in convenience of Text or Special number formats, or the customization afforded by creating your own format, this guide empowers you to overcome Excel's default behavior. Enhance your data entry experience and eliminate the hassle of missing leading zeros with these effective techniques. Now, dive into the details of each method and transform your Excel proficiency!
