How to Fill Blank Cells with 0 in Google Sheets [3 Ways] (2023)

Filling blank cells with a specific value may seem like a trivial task, but when dealing with large data sets that contain blank cells interspersed with other non-blank cells, it's not as easy as select and paste.

In this tutorial, we show you 3 ways to fill empty cells with 0 in Google Sheets:

• using a filter
• userfind and replaceresource
• using a formula

For the last two methods, there can be two more cases:

• when your dataarea contains text data
• when your data range contains numeric data

The technique used will vary depending on the type of data. Therefore, we will also see how to adapt the techniques for use in the two situations above.

Use a filter to fill empty cells with 0 in Google Sheets

Consider the following data set, where you have numeric and text data, but are interspersed with empty cells:

We want to fill these empty cells with zeros. we can use oneFilterto do this as shown step by step below:

• Select the range of cells you want to work with (cells A1:A13 in this case). Include the header row.
• OfDatamenu, select 'create a filter'. Alternatively, you can simply click 'create a filter' in the toolbar as shown below:

• This creates a filter for your data. You should see the filter icon next to the column headers:

• Click on the filter icon.
• Click on the menu that appears'Clear'.

• This will clear all filter values ​​from the list.
• Then click '(empty rooms)' from the list of values. This allows you to filter your data to only show rows where the selected column contains blank fields.

• Click OK.
• You should now only see empty values ​​in the filtered column. All other rows have been filtered and hidden.

• Type 0 in the first empty cell of the column.

• copy is valueto the rest of the cells in the filter area by dragging the fill handle down.

• Remove filter by clicking'remove filter' button.

Once the filter is removed, all hidden values ​​should reappear and you should find all empty cells now replaced with 0.

The great advantage of this method is that it works regardless of whether your data set contains text or numeric data. Also, it keeps all your formulas and values ​​intact in non-empty cells without the need to change their formats.

Using Find and Replace to replace empty cells with 0 in Google Sheets

The other technique to replace empty cells with 0 in Google Sheets is to usefind and replaceGoogle Sheets feature.

There are slight variations to this method depending on whether your data is numeric (just numbers) or alphanumeric (text and numbers).

This is because this method involves the use ofregular expressions, which only works with text (or alphanumeric) data.

We will see both cases in the following sections.

How to replace empty cells with 0 when you have a text (or mixed) column

Consider the following list of data, where you have text and numeric data, but are interspersed with empty cells:

We want to fill these empty cells with zeros. we can usefind and replaceresource to do this. ISLANDfind and replaceThe dialog in Google Sheets looks like this:

Normally in Excel we could leave it like this.Meetblank field and have Excel replace all blank spaces with 0. Unlike Excel, however, Google Sheets does not allow you to exit the fieldMeetempty field. As such, you must use a regular expression to express whitespace.

In Google Sheets, you can use the regular expression "^s*$" to specify an empty value. This term means "a cell that contains only spaces and can have any number of spaces."

Now let's see step by step howfind and replaceempty cells in Google Sheets with 0:

• Select the range of cells you want to work with (cells A2:A13 in this case).
• Ofto editmenu selectfind and replace. Alternatively, you can just use the keyboard shortcut CTRL+H.
• Enter the regular expression "^s*$" in the input field next to'Meet'.
• Enter the value 0 in the input field next to 'replace with'.
• Check the boxes next to'match case',e'Search using regular expressions'.

• click notreplace everythingbutton.

• Clickmade.

All empty cells in the selected data range should now be filled with the value 0.

How to replace empty cells with 0 when you have a numeric column

As mentioned above, Google Sheets allows you to only use regular expressions with text data. So if you have a numeric dataset, you must first convert it to text before applying it.find and replacemethod for it.

So, let's see the steps to replace empty cells in a numeric dataset with 0 in Google Sheets. To demonstrate this, we will consider the following data set:

Here are the steps:

• Select the range of cells you want to work with (cells A2:A7 in this case).
• OfFormatmenu, navigate toNumber->plain text. This converts all the numbers in the data area to plain text.

• Ofto editmenu selectfind and replace. Or use the shortcut CTRL+H.
• Enter the regular expression "^s*$" in the input field next to'Meet'.
• Enter the value 0 in the input field next to 'replace with'.
• Check the box next to 'Search using regular expressions'.

• click notreplace everythingbutton.

• You should now see all empty cells replaced with 0's.

• Convert the data back to numeric data by navigating toFormat->Number->Automatic.

Use a formula to fill empty cells with 0 in Google Sheets

You can also use a formula to fill empty cells with 0. In this exercise, we cover two different formulas that you can use:

• formula using the ISBLANK function
• formula using function N

The first formula works with any type of data, while the second formula is only suitable for numeric data.

Since the N function supports an array formula, you can use it to quickly fill all empty cells with zeros instead of copying the formula to each cell.

It's important to note that using the formula method to fill empty cells with zeros may end up changing the format of your data.

This is because you use the formula in a separate cell and paste the results back into the original cells. So you may need to reformat your column when you're done.

Using the ISBLANK formula to replace empty cells with 0 (when you have a column of text)

The task of the ISBLANK function is quite simple. It only requires one parameter, which is the value we want to test (if it is empty or not). If it is blank, it returns TRUE; otherwise, it returns FALSE.

We need to wrap an IF function around this to handle two cases: if the function IS BLANK it returns TRUE and if it returns FALSE.

We specify the following return values ​​for the IF function:

• if blank then 0
• If not empty, same as original value.

So if we test cell A2, our formula will be:

=IF(IS BLANK(A2);0;A2)

Let's see step by step how to put this formula into practice:

• Create a blank column next to the column you want to work with (you can delete this column later when you're done). In this case we only use column B.
• In the first cell of this blank column (cell B2), enter the formula: =IF(ISBLANK(A2),0,A2)
• Drag the fill handle of this cell down until you reach the last row of its range.

• Select the newly updated cells of your new column (column B).
• Press CTRL+C to copy cells.
• Select the first cell in its original column (column A) and press CTRL+Shift+V toto insertonly values.
• Delete column B.

If your data had dates or another data format, it may be lost, so you may need to reapply the format for some of these values.

The only downside to this formula is that the ISBLANK function is not dynamic, so you cannot work with a set of values ​​at once. You must calculate the result for individual cells.

Use the N formula to replace empty cells with 0 (when you have a numeric column)

The n function simply returns the numeric value of the given argument. So if you pass a date, it will return the numeric value of the date. If you pass a number, it returns the same value, and if you pass a text value or a space, it returns 0 (since there is no numeric value for this).

As such, the N function is perfect when you want to fill all empty cells with zeros. Because this function works well with the ARRAYFORMULA function, you can combine the two to calculate the results of an entire column of data at once.

So if we try cells A2:A7, our formula will be:

=FORMULAMATRIZ(N(A2:A7))

Let's say you have the following list of numbers with empty cells between them:

Here are the steps to replace empty fields with 0:

• Create a blank column next to the column you want to work on. Let's use column B for that in this case.
• In the first cell of this empty column (cell B2), enter the formula: =ARRAYFORMULA(N(A2:A7)).
• you have to findthe whole columnfilled with results where you have the original values ​​from column A and the empty fields replaced with 0 (no need to copy the formula).

• Copy this cell (B2).
• Select the first cell of the original column (column A) and press CTRL+Shift+V to insert values ​​only.
• Delete column B.

And so! You should keep all the original numeric values ​​and 0 in place of all the blank cells.

Conclusion

This tutorial showed three ways to fill empty cells with 0 in Google Sheets. Since Google Sheets generally tends to treat text and numeric data ranges differently, we'll show you what to do in both cases. We hope you found ours.quick tricksuseful and easy to follow.

Related:

• How to fill out Google Sheets
• How to delete the content of Google Sheets (in 2 seconds)