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
- user
*find and replace*resource - 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.

Index

## 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 one*Filter*to 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.
- Of
*Data*menu, 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 of*regular 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 use*find and replace*resource to do this. ISLAND*find and replace*The dialog in Google Sheets looks like this:

Normally in Excel we could leave it like this.*Meet*blank field and have Excel replace all blank spaces with 0. Unlike Excel, however, Google Sheets does not allow you to exit the field*Meet*empty 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 how*find and replace*empty cells in Google Sheets with 0:

- Select the range of cells you want to work with (cells A2:A13 in this case).
- Of
*to edit*menu select*find 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 not
*replace everything*button.

- Click
*made*.

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 replace*method 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).
- Of
*Format*menu, navigate to*Number*->*plain text*. This converts all the numbers in the data area to plain text.

- Of
*to edit*menu select*find 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 not
*replace everything*button.

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

- Convert the data back to numeric data by navigating to
*Format*->*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)

## FAQs

### How to Fill Blank Cells with 0 in Google Sheets [3 Ways]? ›

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.

**How to fill empty cells with 0 with value above below in Google sheet? ›**

Fill Blank Cells With Value Above in Google Sheets

If you want to populate a cell with the value above, you can very simply **enter a formula (that refers to the cell above) in the first blank cell, and then use the CTRL key to copy the formula to multiple cells**.

**How do I replace n A with 0 in Google Sheets? ›**

It's worth noting that we can **use the IFERROR() function** to replace #N/A values with any value that we'd like. In the previous examples we simply replaced #N/A values with zeros or blanks because these are the most common replacement values used in practice.

**How do I fill all the way down in Google Sheets? ›**

**4.**

**What is the keyboard shortcut for Autofill (Fill Handle) in Google Sheets?**

- CTRL + R to autofill horizontally (right)
- CTRL + D to autofill vertically (down)
- CTRL + Enter to autofill what you've selected.

**How do you make a cell blank if there is no value in Google Sheets? ›**

How do you make a cell blank if another cell is blank in Google Sheets? You can **combine the ISBLANK formula with the IFERROR function**. An example of the mixed formula is as follows: =IF(ISBLANK(A1), IFERROR(0/0,), A1/B1).

**What does N () do in Google Sheets? ›**

**Returns the argument provided as a number**.

**How do you fill all cells with value in Google Sheets? ›**

**To use the fill handle:**

- Select the cell you want to use. A small square—known as the fill handle—will appear in the bottom-right corner of the cell.
- Hover the mouse over the fill handle. ...
- Click and drag the fill handle over the cells you want to fill. ...
- Release the mouse to fill the selected cells.

**How do I get a zero in front of a number in Google Sheets? ›**

**Using TEXT and REPT to add leading zeros**

- Using the TEXT function only, we enter the formula =TEXT(B3,"0000000")
- Using the TEXT and REPT functions, we enter the formula =TEXT(B3,REPT(0,7))