Leading zeros in Excel

How to Add Leading Zeros in Excel?

This tutorial will show different ways to add leading zeros in excel before the number. Excel does not allow you to add zero before the number. This is one of the annoying features of Excel. Excel treats postal cards, phone numbers and another similar number as a general number only and does not allow preceding zeros.

👉 Change the cell format to Text

There are three ways you can change your cell format to text which will help to Add leading zeros in Excel. 

1st Way: Go to the home tab > In the Number group, change the format to text. This is one of the ways to add leading zeros to the number. 

Result: A small green triangle in the left upper corner of the cell. Select the cell and click on ignore the error.

2nd Way: Type the apostrophe (‘) at the start and then enter the number. It is advisable not to use this method to change the cell format to text because you need to do it one by one for each and every cell. It will be time-consuming.

Result: Adding the apostrophe to the cell will convert that particular cell into text form.

3rd Way: Change the cell format to text is by typing text formula. The formula will be =TEXT (cell, number of digits)

👉 An Example to use the Text function

For example, it will be =TEXT (A1, 000000) where A1 is the cell selected and 000000 is the number of digits you want to convert your data into. Simply drag your mouse through the column and all your data will be converted into 6 digit number but with green triangle seen at the upper left corner of the cell.

👉 Using Format Cell Dialog Box to change the format of the cell to number

Steps to change the format of the cell to a number using the format cell dialog box:

Step 1: Select the cell and right click the mouse.

Step 2: Go to format cells. A format cell dialog box will appear.

Step 3: Click on the number and select custom.

Step 4: As you select custom you will see a sample and type option.

Step 5: In the type of option type the number of zero you want to add counting the number as well.

Step 6: Click on OK.

👉 Example

For example, if you want to add 2 zeros to the existing 4 digit number type 6 zeros you will see that 2 zeros are added before the number in the cell. You can select the entire column and carry out this process in no time. It is a quick process to add zero before the number.

You can also use the Excel keyboard shortcut to open the format cell dialog box CTRL + SHIFT +F.

👉 Conclusion

This function will help to present your data in a presentable form. While printing the sheet into a paper this looks quite proper. This is an inbuilt function in Excel. This type of situation arises less as we use calculation more than formatting. But this is important while we present our data.

Hope you like our tutorial for more cool and amazing trick of excel Like our Excel Superstar Facebook Page and subscribe to our Excel Superstar YouTube channel. Excel Superstar is the leading online training company, which provides Online Excel Course in Hindi. Connect with us and become an Excel Superstar.