Skip to content

How to add leading zeros in Excel?

  • by
  • 4 min read

Microsoft Excel is a popular tool for sorting and studying data. When dealing with numbers that start with zeros, like codes or postal IDs, it’s important to keep those zeros to show the information correctly. Fortunately, Excel offers various ways to include these leading zeros in your numbers.

In this article, we have discussed various methods to add leading zeros in Excel in a few simple and easy steps.

Also read: How to remove leading zeros in Excel?


Convert format to text to add leading zeros

Excel turns zeros before a number in a cell. To stop that, you can convert the cell format to text so it won’t turn those numbers and keep the leading zeros. For example, we have used an employee datasheet in which we want to add zeros before the Employee IDs. Follow the steps to add leading zeros:

Step 1: Select the cells for which you want to change the format, click on the Home tab in the Menu bar and then click on the dropdown menu in the Number section.

Step 2: In the dropdown menu, click on the Text option.

Step 3: Now, you can add leading zeros in the selected cells.

Also read: How to enable and disable Excel autosave?


Use custom number formatting to add leading zeros

To add zeros using custom number formatting, follow the steps below:

Step 1: Select the cells where you want to add leading zeros and click on the Home tab in the menu bar. Click on the Dialogue launcher (Arrow in a box) icon in the Number section.

Step 2: In the box, click on the Custom in the category, type the number of zeros you want and then click on the OK button.

For example, we needed three leading zeros, so we entered four in the box.

Step 3: Here is the result.

Also read: How to exclude words from Google Search?


Use the Text function to add leading zeros

The TEXT function lets you format a value the way you want. For instance, if you want to show the number 1 as 001, you can use the TEXT function to make that happen. To do that, follow the steps below:

Step 1: For example, if we want to add three leading zeros in cell A2, we will select any empty cell and enter the formula =TEXT(A2, “0000”).

Step 2: Here is the result.

Also read: How to find external links in Excel?

Akash Singh

Akash is a law graduate who likes to go for bike rides on the weekends soul-searching for answers to his many existential questions. You can contact him here: singhakash95@pm.me

>
Exit mobile version