Keep Leading 0’s in your spreadsheet

If you have leading 0’s in your cell, Excel and Google Sheets will remove them. However, those may be important. Here is how to keep them.

Why does this happen?

Leading 0’s don’t change the value of a number and therefore they are redundant. The numbers 500 and 0005000 are identical as far as Excel is concerned. However, I have received files where the leading 0’s are part of the ID for the row. Usually, this is done so that every ID number has the same number of digits. This can also happen with Zip Codes. Connecticut, for example, has zip codes that lead with a 0 as in 06101.

How do I fix this?

There are a ton of ways to address this but this is what use most commonly

Add a single quote '

If I am just fixing one cell, I will just add a single quote before the zeros. So in my formula bar it will show '000001 while it will display 000001 in the cell (without the ')

Change the Cell Formatting

In Excel, launch the cell formatting window by holding and pressing CTRL + 1. Then create a custom format with 0’s. If you want all your numbers to be five digits long, type five 0’s into the Type: box.

In Google Sheets, Click on Format > Number > Custom number format.

If you want to have all your numbers be five digits long, then type in five 0’s in the box.

After you hit ‘Apply’, your numbers will have the leading 0’s


Posted

in

by

Tags: