Excel automatically removes zeros from the beginning of numbers when you enter them. This can cause problems with phone numbers, IDs, bank account numbers, etc. For example, write 000012 and hit Enter; Excel will automatically remove the leading zeros.
If you don’t know how to make Excel keep those leading zeroes, it can be frustrating. Don’t worry, there are a few ways through which you can accomplish your goal in Excel.
The goal of this gearupwindows article is to help you add leading 0 to a number in Excel. Here is how to do it.
How to Add 0 Before Any Number in Excel?
You can insert or add 0 before any number in an Excel worksheet in several ways. We will guide you through these procedures.
Format as Text
It is possible to change the format of a range in Excel to text. If you do this, Excel will treat any numbers you enter as text values.
Step 1. Open a new or existing Excel worksheet.
Step 2. Select a range of cells you want to enter leading zeros in.
Step 3. In the menu bar, click on the Home tab to change the ribbon.
Step 4. Click on the Number Format drop-down selection in the Number section.
Step 5. Select Text in the drop-down list.
Because text values are entered instead of numbers, numbers with leading zeros will not disappear when you enter them.
Format Cells
Using the Format Cells dialog, you can also add leading 0 before a number in Excel. This is useful if you have similar types of data, such as several 10-digit phone numbers, or if you want to display several numbers in a similar manner. In such cases, you don’t need to add 0 before a number, Excel will do it for you. The Excel spreadsheet will add the leading 0 to a 9-digit number when you press Enter. Here is how to do it.
Step 1. Open a new or existing Excel worksheet.
Step 2. Select several blank or filled cells where you want to display numbers in a similar manner.
Step 3. Right-click on the cells and select the Format Cells option to open the Format Cells dialog box.
Step 4. Switch to the Number tab.
Step 5. Select Custom in the left sidebar.
Step 6. On the “Type” box, enter several 0 you want to see in chosen cells.
Step 7. Finally, click the OK button.
As an example, if you have entered five 0 in the above step, when you type any number, such as 1, in an Excel cell and press Enter, Excel will automatically display 1 as 00001. That means you don’t need to type 00001 to match with other numbers in your cells.
Leading Apostrophe
If you use a leading apostrophe (‘) to force Excel to enter a number as text, you will keep the zeroes in front as you enter your data. This method is quick and easy if you have to enter a few numbers leading to 0. You can do this by typing ‘ before any numbers. This will make Excel see the data as text and not a number.
Conclusion
In conclusion, adding leading zeros to numbers in Excel can be essential for accurately displaying data, such as phone numbers, IDs, and bank account numbers. However, Excel automatically removes leading zeros, which can be frustrating for users. Fortunately, there are several ways to add leading zeros in Excel, including formatting as text, using the format cells dialog, and using a leading apostrophe. By following the steps outlined above, users can easily add leading zeros to their numbers in Excel, ensuring accurate data representation.
