Preserve Leading 0s in Excel
Author: Matt Irving
Published: 7/22/2021
Stop Excel from removing zeros
Microsoft Excel spreadsheets have a tendency of removing leading zeros. Likely due to most use cases not requiring them to be displayed. However, there are certain times where you actually need leading zeros to be displayed (like a product or order number for example).
There are a couple ways to accomplish this but we’ll focus on the easiest first which is: Changing the data type from Number or General to Text in your target cell(s).
Doing so will allow you to take inputs that used to appear like this…
And have them appear like this…
Notice the green triangle in the top left corner of the cell. This is just Excel warning you that you’ve just saved a number as a text. If you hover over the cell, a warning sign will populate. When you click the caution sign you’ll be presented with a few different option. Select ‘Ignore’ to remove this warning, or you can simply ignore the non-tech way by looking elsewhere.
And that’s it. You now have leading zeros for your numbers. To make things a bit easier the next time, you can create a temple with all the cells you need set to a data type of Text. Doing so will eliminate the need for you set each cell manually, saving you time and reducing error.
Excel is great but can be headache inducing at times. Leading zeros can cause your IF statements and/or Vlookups to produce undesirable results. As is the case in our scenario below:
Malcolm wants to assign all students with a certain ID number a letter that corresponds with their dorm unit. Nearly all the student ID numbers start with zeros.
Malcolm’s friend Harriet takes enters all the ID numbers into a spreadsheet then attempts to perform a Vlookup to find assign dorm units to students but is shocked when nothing but errors are returned.
This error was caused by the data types being different. Although the numbers have the same numeric value, Excel views them as different due to those leadings zero!
To remedy this, Harriet changes the data type to text and then adds the leading Zeros to the columns on the left. Now all is right in the world.
