How To Add Zeros In Google Sheets

How to add zeros in google sheets

When keying in data in google sheets, one may come across some values with leading zeros. Datasets such as codes of places like postal codes, street codes, as well as other binary numbers have leading zeros. One may find it difficult to enter those values in google sheets as the leading zeros disappear once you click enter after typing the value or after moving to the next cell hence making the data lose its meaning.

In a short tutorial, I will show you some different methods of adding leading zeros in google sheets depending on the requirements such as adding zeros before the number or alphanumeric characters


1. The simplest method for adding zeros in some text values, numerals, or alphanumeric values is by starting with an apostrophe(‘) before keying in the other values as shown below.

072220 0924 Howtoaddzer2

2. When you click ENTER, you’ll notice that the zeroes before the real numbers don’t disappear like in normal occurrences when no apostrophe is not put before or other formats are activated as shown in the screenshot below.

072220 0924 Howtoaddzer3

3. The other method is to change the format of the cell. In this method, Highlight/ select the range of cells in which you desire the leading numbers to apply. For the whole column, double click the column header and for a whole row, double click the row header.

072220 0924 Howtoaddzer4

4. Click format then numbers then select plain text as shown.(Format> Number> Plain text)

072220 0924 Howtoaddzer5

5. If you type any leading zeros within the shaded numbers, they will not disappear.

072220 0924 Howtoaddzer6

6. You may as well use the Google sheets Custom Formats method. In this method, simply highlight the range of cells you desire custom formatting. Click formats then the number, click more formats on the pop-up menu page that appears then select Custom number format in the next pop up menu that appears.(Format > Number > More formats > Custom number format).

072220 0924 Howtoaddzer7

Custom number formats

Identify the number of digits you desire in the selected cells. For instance in my case, for the cells I have selected I can key in up to 6 digit values because in my custom field I selected 6 zeros then click Apply. Kindly note that if you enter more than the number of digits selected through custom zeros, all the leading zeros above 6 digits will disappear once you click enter.

072220 0924 Howtoaddzer8