How to Keep Leading Zeros in Google Sheets

When working in Google Sheets, there may be times when you want to keep leading zeros in a number.

It can be frustrating to find that Google Sheets will automatically remove leading zeros when you enter them in a number.

Luckily, it’s pretty easy to keep Google Sheets from deleting your zeros. There are several ways to keep them.

In this tutorial, I will show you how to keep leading zeros in Google Sheets

How to Keep Leading Zeros

Placing an Apostrophe Before the Number

One of the easiest ways to keep leading zeros in a number is to simply add an apostrophe before the number.

Adding an apostrophe will convert the number in your cell to text, which will keep the zero.

You will notice that after the apostrophe is added your number will align to the left because that is the default formatting of text in Google Sheets.

Text Format

If you have a bunch of cells where you need to enter numbers with leading zeros, the previous method might take too long to be practical.

A better way to do this would be to change an entire column or range to text format. Then you can continue to enter leading zeros into every cell.

Here is how this is done:

1. Highlight the range of cells that you want to add leading zeros to

2. In the top menu select Format>Number

3. Then from the list of options select Plain text

4. You can now enter zeros at the beginning of your number in the range that you highlighted in step 1 and they will stay in the number

Custom Format

Another option is to create a custom number format with a certain number of leading zeros.

With this method, you can set a range of cells to be a custom number format.

So maybe you want to have a five-digit number with leading zeros. After you set a range of cells to this number format, every number you enter will automatically will be five digits with leading zeros whether you enter the zeros or not.

So if you enter a number like “989” in a cell it would automatically change to”00989″

Here is how this is done:

1. First highlight the cells that you want to change the number format of

2. Then in the top menu select Format>Number

3. At the bottom of the drop-down list select Custom number format

4. Enter the number of zeros corresponding to the number of digits you want your number to be. Press Apply when you are finished

5. Now when you enter any number into a cell that you have changed the format of, the number will automatically include leading zeros until your final number is the number of digits you entered in the previous step

Closing Thoughts

There are many ways to keep your leading zeros in place when working in Google Sheets.

If you’re just doing a small number of cells, the apostrophe method will be fine, however, for more cells, the text format or custom number format method will be a better option.

Try all of the methods to see which one you prefer best.

More Google Sheets Tutorials:
How to Stop Rounding Numbers
How to Round Numbers