How to add zeros in front of number in Excel

You can watch a video tutorial here.

Microsoft Excel is a very powerful tool for computing that contains rows and columns to manage the numeric data. It can carry billion transactions and numeric values. The data is being stored in cells. However, there are also certain limitations too. We can’t write digit 0 at the start of any number.

Due to this characteristic of Excel, users often face confusion. Excel has a built-in tool by which we can easily solve this case. Microsoft Excel allows us to add digit 0 before any value. We can do it by changing the type of data range. Below are the steps to follow; 

Method 1 Format Cells as Text to add leading Zeroes

The first method is to format the cells as text. To do this follow the steps mentioned below.

Step 1 – Select the cell and change data type

•  Select the cell in which you want to enter zero digit. 
• In the Home tab, go to the Numbers section,click on the General.
• A drop down menu will appear.
• Click on the text.
• Now type zero digit in your selected cell, it will add zero before numeric values.

Step 2 – Remove the error 

• So during mathematical calculations, this text data type gives errors.
• Select the entire data range.
• One corner of the cell is filled with the green.
• A yellow icon appears at the side.
• Click on it.
• A menu will appear, click on Ignore error.
• Align it right by clicking on the alignment group.
• All the data will be in symmetry as others.

Above is an animation as an example.

Method 2 Format Cells as Custom to Add leading Zeroes

The first method is to format the cells as text. To do this follow the steps mentioned below.

Step 1 – Select the cells and press CTRL+1

•  Select the cell in which you want to add leading zeros. Press CTRL+1 to open up the format cells dialog box.
• Now select the Custom option from the Category section in the Format Cells dialog box.

Step 2 – Change the Format as per requirement

•  Now go to the type: category and select the 0 digit. Then keep on adding zeros until you see your desired format in the sample window. After adding zeros press OK.
• Leading zeros will be added in all the selected cells.