Skip to Content

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.

zero apostrophe

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.

zero apostrophe-1

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

text-leading-zero-1

2. In the top menu select Format>Number

text-leading-zero-2

3. Then from the list of options select Plain text

text-leading-zero-3

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

text-leading-zero-4

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

custom-number-format-1

2. Then in the top menu select Format>Number

custom-number-format-2

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

custom-number-format-3

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

custom-number-format-4

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

custom-number-format-5

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