How to Replace Blank Cells with Zero in Google Sheets

The Find and Replace function is useful when you need to replace blank cells in your Google Sheets data set with a zero.

Before working with a dataset, you might find that some entries have blank values. It’s a common procedure to convert these blanks into a numerical value, usually a 0.

We can use the Find and Replace function to search for a particular value and replace each instance with another specified value.

Using regular expressions, we can specify that we will be searching for blank cells and replacing them with a zero. The Find and Replace function can be found within the Edit menu.

Let’s begin with a quick use case where we will need to replace blank cells with zero. 

In this example, we have a dataset with multiple rows with numerical values. Each row in the dataset represents a single student in a class. Each column represents the student’s score in a particular test or requirement. 

There are cases where a cell in this dataset would be blank due to either the student missing the test or a student not submitting a requirement on time. As the professor, you are strict with deadlines, so you want to convert these blank cells into a score of zero. 

How do we go about this?

With the Google Sheets Find and Replace function, we can easily look for blank cells and replace them with any value that we want. This use case is just one way to use the Find and Replace function for this purpose. 

Since many mathematical functions treat blank cells differently from a numerical zero, it’s essential to clean your data in this way to prevent any miscalculations. Data such as the total count, average, or minimum values may become inaccurate if blank data is not converted to zeros.

Now that we know when to use the find-and-replace function with filling in blank cells with zeros, let’s dive into how to use it on an actual sample spreadsheet.

 

 

Replacing Blank Cells with Zero in Google Sheets

Let’s look at a real example of using the Find and Replace function in a Google Sheets spreadsheet to replace blank cells with zeros.

In the example below, we can see that column B and column C have blank cells. Using the Find and Replace function, we could fill those cells with zero.

replace blank cells with zero to have a clean dataset

 

The values of our AVERAGE and MEDIAN functions change when we replace our blank cells with zeros. This is because most mathematical functions ignore blank cells when performing their computations.

replace blank cells with zero to prevent incorrect calculations

 

You can make your own copy of the above spreadsheet using the link attached below. 

If you’re ready to try replacing blank cells with zeros in Google Sheets, let’s start writing it step-by-step!

 

 

How to Replace Blank Cells with Zero in Google Sheets

In this section, we will go through each step needed to start replacing blank cells with zeros in Google Sheets. This guide will show you how to clean the dataset, as seen in the previous example. 

Follow these simple steps to start using the Find and Replace function:

  1. First, make sure that the dataset we have requires cleaning. In the dataset below, columns B and C are numerical data, so replacing blank cells here with zero is appropriate.
    Look if the dataset has blank cells
  2. Next, we’ll have to select the Find and Replace function, which you can find under the Edit menu. Alternatively, you can type in the shortcut Ctrl+H to bring the tool up.
    Select the Find and replace function
  3. Next, we should fill up the required fields. Our regular expression ^\s*$ allows us to search for only blank cells. In the next field, we type in the number 0. Make sure that the option to search using regular expressions is checked.
    Use a regular expression to replace blank cells with zero
  4. The next step is to indicate the specific range we would like to apply our find and replace logic to. In this example, we’ve selected the entire dataset.
    Select the cell range where we want to replace blank cells with zero
  5. After writing down these values, click the Replace all button to make the necessary replacements.
  6. Finally, you can click on Done to exit the pop-up. Your dataset should now look something like this:
    Final result of our find and replace function

 

 

Frequently Asked Questions (FAQ)

  1. Why should I replace blank cells with zeros?
    It’s helpful to fill blank cells with a number, symbol, or value if you plan on using the dataset as the source for a pivot table. It’s also necessary to perform mathematical functions on the dataset.
    It’s important to check if your function handles blanks as you might expect it to. 
  2. Why does the find and replace function not work for all cells?
    Double-check if you have the right regular expression. It’s also possible that blank cells are not truly empty. In that case, you might have to change the regular expression to include other whitespace characters.

That’s all you need to remember to replace blank cells with zeros in Google Sheets. This step-by-step guide shows how easy it is to clean datasets with missing data.

The Find and Replace function is just one example of a powerful and versatile function you can find in Google Sheets. With so many other Google Sheets functions out there, you can surely find one that suits your use case. 

Are you interested in learning more about what Google Sheets can do? Stay notified of new Google Sheets tutorials and guides like this by subscribing to our newsletter!

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
1 comment
  1. I noticed that in the picture with the caption that has “In this example, we’ve selected the entire dataset”, the dialog box shows “No matches found”, even though there are empty cells visible. I’m actually having that issue, using same regex and boxes checked that you have – and my data has empty cells – and I’m also getting “No matches found”. Any idea why it wouldn’t find those cells?

    Thanks, Jeff

Leave a Reply

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

You May Also Like