How to Replace Blank Cells with Zero in Google Sheets


The easiest way to replace blank cells with zeros in Google Sheets is to use the Find and replace function within the Edit tab.

The following example shows how to use this function in practice.

Example: Replace Blank Cells with Zero in Google Sheets

Suppose we have the following data that shows the points scored by various basketball teams:

Suppose we would like to replace the blank cells in the Points column with zeros.

To do so, click the Edit tab and then click Find and replace.

In the new window that appears, type ^\s*$ to find blank cells and use 0 as the replacement.

Type in the cell range you’d like to use this formula on, then check the boxes next to Match case and Search using regulars expressions.

Google Sheets replace blank cells with zeros

Then click Replace all and then click Done.

Each of the blank cells in the Points column will automatically be replaced with zeros:

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Count Number of Occurrences in Google Sheets
How to Check if Value is in Range in Google Sheets
How to Count Unique Values in Google Sheets

5 Replies to “How to Replace Blank Cells with Zero in Google Sheets”

  1. Thank you for sharing this tip! Far better than using add-ons with unknown code that will do this for you (and maybe something else…)

  2. Hi,

    this tip works great for individual rows of cells. How would one request this to be automatically updated at every moment?
    Lets say I have the cells (A3:A100) and I want to say:
    if blank: say “0”
    if not blank: don’t change value of cell.

    And if I were to delete a certain cell, it should automatically display 0. How would this be possible? Because in the way you showed here, you would have to manually fill these cells again.

Leave a Reply

Your email address will not be published. Required fields are marked *