How To Add Leading Zeros in Excel

Say you are an online seller and your way of keeping track of your business' performance is through the use of Microsoft Excel. So here you are doing an inventory of the available products and are into the process of entering product codes that start with zeros in the assigned column on Excel. But when you hit Enter on your keyboard to go to the next cell, the leading zeros just disappear! Excel, being a powerful office tool that it is, sometimes does commands like this without prompting the author. Thankfully, this problem can be easily and quickly fixed. Follow the steps outlined below to add leading zeros back in Excel.

  1. Opt to add leading zeros in Excel manually. To add leading zeros manually back into the Excel file you are working on, go to the cell that contains the number that lost the leading zeros earlier. Now, key in the punctuation mark apostrophe first before you enter the number with the leading zeros. Placing an apostrophe prior to a leading zero will be interpreted by Excel as such that you want it to enter numbers as text characters so it would not try to take the leading zeros out. Perform this same step for the other cells that had the leading zeros taken out previously.
  2. Pre-set a column in Excel to interpret characters as text.  Now, if you have more than one Excel sheet that needs to be added with leading zeros, then manually keying in the rest of the numbers again just so the leading zeros are added back in will be very tedious. To address this problem, you will need to pre-set the column that will contain the leading zeros first. This is so the leading zeros won't be taken out next time you key them in the column. Using our example earlier about the product inventory, say, column A contains the product name, column B cell contains the product description, column C contains the product code, and column D contains the product price. Now, what you will need to do is to click on the C header (assuming that column C is empty). Once column C is highlighted, right click on your mouse for a dropdown menu to appear. Select Format Cells from the options by clicking on it once. The Format Cells box should pop up. Under the Category: section, click on the item Text and then the button OK afterwards.

Now, column C has already been configured to interpret all characters, including leading zeros, as text. You can start over and key in the product codes without having to worry that the leading zeros will be automatically eliminated by Excel.


Share this article!

Follow us!

Find more helpful articles: