In this tutorial, you will learn how to stop google sheets from deleting zeros.
Google Sheets tries hard to predict how users might want their data to display to make editing and maintaining spreadsheets easier, but sometimes it gets things very wrong. Take, for example, the frequent frustration of trying to enter numbers with leading zeros, for example: 007. If you enter this number into a cell in a new Google Sheet, the number 7 will appear without the leading zeros. This can be a huge frustration as there’s no immediately obvious way to correct the issue, but fortunately there are a couple of easy ways to get around it
Format as Plain Text
If you don’t plan to do any math on the number you’re trying to enter, the best solution to this problem is to enter it as plain text. This zero deleting issue comes from the fact that Google Sheets assumes you will want to perform calculations on the number, so it cleans it up for this purpose. Typically, though, this isn’t what users want when they enter text with leading zeros, so simply telling Google Sheets the number is actually text not a number works well. Here’s how:
Select the cell you want to enter leading zeros in, then open the Format menu, hover over Number and choose Plain Text
It won’t look like anything has changed, but when you reenter the data with leading zeros in the cell, Google Sheets will no longer delete the zeros
You can get a similar effect by simply typing a single quotation mark (‘) at the start of the value with leading zeros. This character doesn’t display in the cell but tells Google Sheets to treat that value as text not a number and display the leading zeros
Custom Number Formatting
If you need to use the number to do math, you’ll want to create a custom number format with leading zeros. Here’s how:
Select the cell you want to format and open the Format menu, hover over Number to expand the submenu, then go all the way to the bottom of the submenu and choose Custom Number Format
In the popup that appears, type a string of zeros. The number of zeros corresponds to the total number of digits that will display in the number, including any non-zero digits. Zeros will be prepended to the number till it reaches the target number of digits. So to get 7 to display as 007, type 3 zeros, then click Apply
The number will appear with leading zeros, but when you select the cell and look in the formula bar, you will see that under the hood it’s still just a regular number
You can now perform calculation on this number as if it did not have the leading zeros
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to stop google sheets from deleting zeros. Want more? Check out all the Google Sheets Tutorials.