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
- Open your spreadsheet.
- Select the cells.
- Right-click a selected cell and choose Format Cells.
- Choose Custom.
- Type a number of zeroes equal to the number of desired characters into the Type field.
- 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.
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.
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.
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.
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.
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.
Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.
After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.
His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.