How to Add Leading Zeroes to Numbers in Excel 2013

If you are working with numbers that might include zeroes at the start of those numbers, then you might be running into a problem.

Excel will not display a zero if it’s the first number in a string. When you have barcodes or tracking numbers, or something similar where that zero matters, then you might be wondering how to put zeroes at the start of a cell in Excel.

Our tutorial below will show you how to accomplish this with a formatting adjustment.

How to Put Zeroes at the Beginning of Numbers in Excel

  1. Open your spreadsheet.
  2. Select the cells.
  3. Right-click a selected cell and choose Format Cells.
  4. Choose Custom.
  5. Type a number of zeroes equal to the number of desired characters into the Type field.
  6. Click OK.

Our guide continues below with additional information on how to start numbers with zeroes in Excel, including pictures of these steps.

Is there too much or too little space between your lines in Powerpoint? Visit our Powerpoint line spacing guide for more information on how to change that setting.

Excel has a habit of removing zeroes if they are the first digits in numbers.

In some cases that isn’t an issue, but in the case of certain types of data, like zip codes, you might need to find a way to add leading zeroes in Excel so that the numbers are correct.

Fortunately, you can do this with the help of a formula, similar to this subtraction formula, so there is only a small amount of manual entry that you will need to do in order to add these leading zeroes to your cells.

If you would like to find out more about changing cell formats in Microsoft Excel, then this article can show you how to do it.

How to Use the TEXT Function to Add Zeroes in Front of Numbers in Excel

The steps below will assume that you have a column of numbers in Excel 2013, and that you would like all of them to have the same number of characters. This is common with United States zip codes, as there are several zip codes that start with the number zero. If you only want to display your numbers with the same number of digits, and don’t wish to actually change the value in the cell, then we will show you a quick formatting change that you can make at the end of this article.

You may also want to look into using concatenate in Excel if you have data in multiple columns that needs to be combined.

Step 1: Open your spreadsheet in Excel 2013.

Step 2: Click inside an empty cell in the spreadsheet.

select the cell for the fixed data

Step 3: Type the formula =TEXT(XX, “00000”) but replace XX with the location of the cell that you wish to modify, and replace 00000 with the number of characters that you want in the cell. Press Enter on your keyboard after you have finished entering the formula.

how to add leading zeroes in excel 2013

Step 4: Click the handle at the bottom-right corner of the cell containing the formula, then drag the handle downward to copy the formula for the rest of the cells in that column. The formula will automatically adjust to use the cell location relative to your original formula.

how to put zeroes in front of numbers in excel 2013

If you would rather change the format of your cells to display your values with leading zeroes, then you can do so by selecting the cell(s) that you wish to modify, right-clicking one of the cells, then choosing Format Cells.

how to format cells in excel 2013

Select the Custom option from the list at the left of the window, then click inside the field under Type: and enter a number of zeroes equal to the number of digits you want to display in the cell. Click the OK button at the bottom of the window when you are done.

how to apply custom formatting to cells in excel 2013

As mentioned earlier, this formatting option will display your cells with the leading zeroes, but it will not change the values to include those leading zeroes.

Do you have columns containing separate bits of information, and would you like to combine them into one cell? Learn how to easily combine multiple cells in to one in Excel with the help of the concatenate formula.

Join Our Free Newsletter

Featured guides and deals

You may opt out at any time. Read our Privacy Policy