Cara menggunakan return blank google sheets

Filling empty cells with a particular value may seem like a trivial task, but when you’re dealing with large datasets containing empty cells laced between other non-empty cells, it’s not as easy as selecting and pasting.

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

• using a filter
• using the Find and Replace feature
• using a formula

For the latter two methods, there can be further two cases:

• when your data range contains text data
• when your data range contains numerical data

The technique used will vary depending on the type of data. So, we will also look at how to customize the techniques to use in the above two situations.

Table of Contents

Using a Filter to Fill Empty Cells with 0 in Google Sheets

Consider the following dataset where you have both text and numerical data, but they are interspersed with blank cells:

We want to fill these blank cells with 0’s. We can use a Filter to accomplish this, as shown step-by-step below:

• Select the range of cells that you want to work with (cells A1:A13 in this case). Include the header row.
• From the Data menu, select ‘Create a filter’. Alternatively, you can just click on the ‘Create a filter’ button from the toolbar, as shown below:

• This creates a filter for your data. You should see the filter icon next to the column header(s):

• Click on the filter icon.
• From the menu that appears, click on ‘Clear’.

• This will clear all the filter values from the list.
• Next, click on ‘(Blanks)’ from the list of values. This lets you filter your data to display only the rows where your selected column has blanks.

• Click OK.
• You should now only see the blank values in your filtered column. All other rows have been filtered out of sight.

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

• Copy this value down to the rest of the cells in the filter range by dragging down the fill handle.

• Remove the filter by clicking the ‘Remove filter’ button.

When your filter is removed, all your hidden values should come back into view, and you should find all the blank cells now replaced with 0.

The great thing about this method is that it works irrespective of whether your dataset contains text or numeric data. Moreover, it keeps all your formulas and values in the non-blank cells intact, without you needing to change their formats.

Using Find and Replace to Replace Blank Cells with 0 in Google Sheets

The second technique to replace blank cells with 0 in Google Sheets is using the Find and Replace feature of Google Sheets.

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

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

We will look at both cases in the following sections.

How to Replace Blank Cells with 0 when you have a Text (or Mixed) Column

Consider the following data list where you have both text and numerical data, but they are interspersed with blank cells:

We want to fill these blank cells with 0’s. We can use the Find and Replace feature to accomplish this. The Find and Replace dialog box in Google Sheets looks like this:

Normally in Excel, we could simply leave the Find field blank and have Excel replace all blanks with 0. However, unlike Excel, Google Sheets does not allow you to leave the Find field empty. As such, you need to use a regular expression to express a blank space.

In Google sheets, you can use the “^s*$” regular expression to indicate a blank value. This expression means ‘a cell containing only whitespace, and it can have any number of white spaces’.

Now let us see step by step how to Find and Replace blank cells in Google Sheets with 0:

• Select the range of cells that you want to work with (cells A2:A13 in this case).
• From the Edit menu, select Find and Replace. Alternatively, you can just use the CTRL+H keyboard shortcut.
• Enter the regular expression “^s*$” in the input box next to ‘Find’.
• Enter the value 0 in the input box next to ‘Replace with’.
• Check the boxes next to ‘Match case’, and ‘Search using regular expressions’.

• Click on the Replace all button.

• Click Done.

All the blank cells in your selected data range should now get populated with the value 0.

How to Replace Blank Cells with 0 when you have a Numeric Column

As mentioned before, Google Sheets lets you use regular expressions with only text data. So, if you have a numeric dataset, you will need to first convert it to text before you can apply the Find and Replace method to it.

So, let us look at the steps to replace blank cells in a numeric dataset with 0 in Google Sheets. To demonstrate, we will consider the following dataset:

Here are the steps:

• Select the range of cells that you want to work with (cells A2:A7 in this case).
• From the Format menu, navigate to Number->Plain text. This converts all the numbers in the data range to plain text.

• From the Edit menu, select Find and Replace. Or use the CTRL+H shortcut.
• Enter the regular expression “^s*$” in the input box next to ‘Find’.
• Enter the value 0 in the input box next to ‘Replace with’.
• Check the box next to ‘Search using regular expressions’.

• Click on the Replace all button.

• You should now see all blank cells replaced with 0.

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

Using 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 tutorial we will cover two different formulas that you can use:

• formula using the ISBLANK function
• formula using the N function

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

Since the N function is compatible with an arrayformula, you can use it to quickly fill all the blank cells with 0s, instead of having to copy the formula down to each cell.

It’s important to note that using the formula method to fill empty cells with 0s might end up changing the formatting of your data.

This is because you are using the formula in a separate cell and then pasting the results back to the original cells. So, you might need to reformat your column after you’re done.

Using the ISBLANK Formula to Replace Blank Cells with 0 (when you have a Text Column)

The ISBLANK function’s task is quite simple. It takes just one parameter, which is the value we want to test (whether it is blank or not). If blank it returns TRUE, otherwise it returns FALSE.

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

We specify the following return values for the IF function:

• if blank then 0
• If not blank then the same original value.

Thus, if we’re testing cell A2, our formula will be:

=IF(ISBLANK(A2),0,A2)

Let’s see step-by-step how to put this formula into action:

• Create a blank column next to the column you want to work with (you can delete this column later, once you’re done). In this case, we are simply using column B.
• In the first cell of this blank column (cell B2), enter the formula: =IF(ISBLANK(A2),0,A2)
• Drag the fill handle for this cell down till you reach your range’s last row.

• Select the recently updated cells of your new column (column B).
• Press CTRL+C to copy the cells.
• Select the first cell of your original column (column A) and press CTRL+Shift+V to paste values only.
• Delete column B.

If your data had dates or some other data formatting, they might be lost, so you might need to reapply formatting for some of those values.

The only downside to this formula is that the ISBLANK function is not dynamic, so you cannot work with a set of values in one go. You have to compute the result for individual cells.

Using the N Formula to Replace Blank Cells with 0 (when you have a Numeric Column)

The N function simply returns the numeric value of the argument provided. So, if you pass a date it returns 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 blank, it returns a 0 (since there’s no numeric value for it).

As such, the N function is perfect for when you want to populate all blank cells with 0s. Moreover, since this function works well with the ARRAYFORMULA function, you can combine the two to compute results for a whole column of data in one go.

So, if we’re testing cells A2:A7, our formula will be:

=ARRAYFORMULA(N(A2:A7))

Let’s say you have the following list of numbers with blank cells in between:

Here are the steps to replace the blanks with 0s:

• Create a blank column next to the column you want to work on. Let’s just use column B for that in this case.
• In the first cell of this blank column (cell B2), enter the formula: =ARRAYFORMULA(N(A2:A7)).
• You should find the entire column populated with results, where you have the original values of column A and the blanks replaced with 0s (without needing to copy the formula down).

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

That’s it! You should have all the original numerical values retained and 0s in place of all the blank cells.

Conclusion

This tutorial showed you three ways to fill empty cells with 0 in Google Sheets. Since Google Sheets usually tends to treat text and numerical data ranges differently, we showed you what to do in both cases. We hope you found our quick-fix tricks useful and easy to follow.

Most Popular Posts

5 Simple Ways to Highlight Duplicates in Google Sheets

IF CONTAINS Google Sheets Formulas [2 Clever Options]

How to Apply Formula to Entire Column in Google Sheets

How to VLOOKUP From Another Sheet in Google Sheets

Nahid Akhter

Nahid Akhter is a writer, programmer, and online course content creator. She has an MS in Computer Science and has been in the education line for more than 14 years. Her specialization is in programming and Tech-writing, and her areas of interest include Office productivity, Artificial Intelligence, and Web design and development.

Bagaimana cara menghilangkan garis di Google Spreadsheet?

Apakah di spreadsheet bisa menggunakan rumus?

Apa saja rumus spreadsheet?

Langkah memasukkan data ke Google Sheet?