How to keep zeros in Google Sheets

When referring to “keeping zeros” in Google Sheets, it generally means preserving leading zeros in numerical values. By default, Google Sheets will automatically remove leading zeros from numbers.

This tutorial will teach us how to keep zeros in Google Sheets. There are multiple methods to keep zeros in Google Sheets. We can keep zeros by adding an Apostrophe prior to the number, else the Text format can be utilized. Another method that we can implement is to use a custom format.

Let’s say we have product codes for some products. The format of the product code includes three leading zeros. We will be adding the leading zeros to the product codes.

Method 1: Adding an Apostrophe

The quickest method to keep zeros in Google Sheets is to simply add an Apostrophe (‘) prior to the number.

Step 1 – Activate the Cell and Add an Apostrophe

• Activate the cell containing the number by double-clicking on the cell.
• Add an Apostrophe (‘) before the number.

Step 2 – Now Add the Zeros

• Now add the zeros to the number.

Step 3 – Utilize the Same Steps to Keep Zeros in Each Cell

• Utilize the same steps to keep zeros in each cell.

Method 2: Applying the Text Format to Keep Zeros

Another convenient method to keep zeros is to apply the text format to the cells. By doing this the zeros are not automatically removed by Google Sheets.

Step 1 – Locate the Format Menu

• Choose the targeted cell in which you aim to keep the zeros.
• Locate the Format tab.

Step 2 – Locate the Plain Text Format

• Locate the “Plain text” format in the “Number” side menu.

Step 3 – Now Add the Zeros

• Add the zeros in the cells.

Method 3: Utilizing the Text Function

Another method that we can adopt is to utilize the TEXT function. With the TEXT function, we can apply a custom format containing the required number of zeros.

Step 1 – Enter the TEXT Function

• Enter the TEXT function in a targeted cell.

          TEXT(B2, “0000000”)

• The first parameter i.e. B2 is the cell containing the number i.e. code without zeros.
• The second parameter is the format to be applied. This format should contain the (number of digits + number of zeros to be added) number of zeros.
• In this case, we have a 4-digit code and we want to add three zeros so we will input 7 zeros.

Step 2 – Utilize the Autofill Feature to Add the Zeros to Each Product Code

• Utilize Autofill to add zeros to each product code in the list.