How To Keep Trailing Zeros In Google Sheets

Google Sheets, much like other spreadsheet software, often poses challenges when it comes to displaying numbers in specific formats. A common issue many face is the automatic removal of trailing zeros from numbers. This is typically due to the default numerical formatting in Google Sheets. However, there’s no need to worry – keeping trailing zeros in Google Sheets is simple and easy to achieve. Here’s how:

Adding Trailing Zeros to Google Sheets

To retain the trailing zeros in your data, you need to alter the format of the cell to ‘Plain Text’. This changes the format of the cell to text, allowing it to retain any leading or trailing zeros. Here’s how to do it:

  1. Select the cell(s) where you want to retain the trailing zeros.
  2. Click on the ‘Format’ menu at the top of the Google Sheets interface.
  3. From the dropdown menu that appears, hover over ‘Number’.
  4. Another dropdown menu will appear. Select ‘Plain Text’ from this menu.

Use the TEXT Function

Another method of retaining trailing zeros is by using the TEXT function in Google Sheets. This function allows you to change the format of the number to text while also specifying the format that you want the number to be in.

Here’s the syntax for the function:

=TEXT(value, format_text)

For instance, if you have the number 123.4 in cell A2 and you want to display it as 123.400, you would use:

=TEXT(A2, “0.000”)

This would give you a text string that looks like a number but has the trailing zeros.


With these simple and easy steps, you’re now armed with the knowledge needed to keep trailing zeros in Google Sheets. Just remember that if you convert a cell to ‘Plain Text’ or use the TEXT function, the cell will contain text and not a number. This means that you won’t be able to use these cells in calculations or numerical operations unless you convert them back to numbers. Take note of this to avoid any confusion or errors in your future works!