How To Add Zero Before Number In Google Sheets

Google Sheets, like its counterpart Microsoft Excel, presents a range of powerful features that allow you to manipulate data in various ways. One such feature is the ability to add zeros before numbers. Here’s an easy step-by-step guide on how to do it.

Add Zero Before Number Using ‘Format Cells’ Option

The simplest method to add zero before numbers in Google Sheets is by using the ‘Format cells’ option. Follow these steps:

  1. Select the cells you want to format.
  2. Right-click the selected cells and click on ‘Format cells’.
  3. Choose ‘Custom number format’.
  4. Enter the format in which you want your numbers to appear. For example, if you want a two-digit number and wish to add a zero before single-digit numbers, you can type ’00’ in the format code box.
  5. Hit ‘Apply’.

The changes will now be reflected in your selected cells. Any single-digit number will have a zero added before it, and the data in the cell will remain the same.

Add Zero Before Number Using Formula

If you want to add zero before numbers using a formula, the TEXT function can be used. Here’s how you can do it:


In this formula, ‘A1’ is the cell reference that you want to format. The “00” indicates that the result should be a two-digit number. So, if cell A1 contains the number 5, the formula will return the text string “05”.


Adding zeros before numbers in Google Sheets can be done quickly and easily using either the ‘Format cells’ option or the TEXT function. Both methods are effective and can be used depending on your specific needs.