There are many reasons that you might want to continue displaying zeroes at the beginning of a numerical string, such as a zip code, password, or social security number, but Excel’s default reaction is to strip these numbers from your cells. Fortunately, you can adjust the format of either the entire sheet or just some select cells to continue displaying these zeroes.
How to Keep Leading Zeroes in Excel 2010
Our guide continues below with additional information on adding leading zeros in Excel 2010, including pictures of these steps.
How to Show Beginning Zeroes in Excel 2010 (Guide with Pictures)
There are a lot of different formats that you can use for your cells. Each format is designed to handle a particular type of information and will adjust the information continued within the cells accordingly. You can easily select the correct format for cells that will contain data with leading zeroes, but if there is not an available format for the type of data that you will be using, then you may have to create your own custom format.
Step 1: Open the Excel 2010 spreadsheet that contains the information that you want to modify to display leading zeroes.
Step 2: Click the column heading that contains the cells that you want to reformat (if you want to reformat all of the cells in that column).
If you only want to reformat some of your cells, then you can use your mouse to highlight all of them. If the cells are not next to each other, then you can hold down the Ctrl key as you click each cell.
Step 3: Right-click one of the selected cells, then click Format Cells.
Step 4: Click the Special option at the left side of the window, click the type of data that is contained within your selected cells, then click the OK button at the bottom of the window.
If your data does not fit one of these formats, then you will need to click Custom and enter your number format into the Type field at the center of the window. Any new values that you enter into your newly formatted cells will adhere to the formatting options that you selected. If you choose to change the formatting of your cells in the future then you will lose the leading zeroes.
How Does the Custom Format Option Work in Microsoft Excel?
When you need to force Excel to create leading zeros when you type a number, then you need to either enter your information as text, include a character like an apostrophe at the start of the input, or you need to use custom formatting. After you select the Excel data that you need to format and select Custom from the left column, you are able to click inside the Type field and enter a series of zeros that corresponds to the number of digits that will be in your cells. For example, if you are entering employee ids that will contain five digits, but some will be zeros, then you could put 5 zeros into the Type field. In the above example when you type data into a cell that is formatted this way, then there will be leading zeros at the start of the cell equal to the number of zeros you entered minus the number of characters in the value you typed into the cell. With a custom format of five zeroes, for example, an entry of 123 would display as “00123.”
More Information on How to Add Leading Zeros in Excel 2010
The steps above are going to change the formatting of some of the cells in your Microsoft Excel spreadsheet. Note that you might need to use the Custom option when you select the cell formatting, and manually add a number of zeros that correspond to the number of digits that your data will contain. Using the Custom option from the Category column is likely going to be the ideal option if you aren’t specifically working with zip codes, phone numbers, or social security numbers. If you are using UPC codes, for example, then a custom format where you enter the desired number of digits in the Type field will be most effective. You can use the keyboard shortcut of Ctrl + 1 to open the Format Cells dialog box more quickly. Note that you need to press the “1” that is above your letter keys, not the “1” on the number pad at the right side of the keyboard. One other option you could consider is using Text formatting. You may encounter some problems with the text format since Excel treats anything with text formatting as a text value, but for simple data entry where the underlying value is less important, then being able to add zeros to the beginning of a text string is more important. When you select a cell value with numeric data that is being manipulated by formatting, the value without the added leading zeroes will be shown in the formula bar at the top of the window. One final way that you can add leading zeros is with the concatenate function. An example of the concatenate formula is shown below: =CONCATENATE(“00”, 123) This will display the number “00123” in the cell with the formula. You can adjust the number of zeros inside the quotation marks to display the desired number of leading zeros. You can also replace with “123” art of the formula with another value, or you could even enter a cell location to place zeroes in front of data that is already located in a cell.
Additional Sources
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. Read his full bio here.
You may opt out at any time. Read our Privacy Policy
