Filling empty cells with a value may seem trivial, but it can get tricky when dealing with large datasets containing blank cells scattered between populated ones. In Google Sheets, a few easy ways exist to fill empty cells with 0 – even in big sheets with mixed data types.
In this beginner tutorial, we’ll walk through 3 simple methods to fill empty cells with 0 in Google Sheets:
- Using a filter to temporarily view and fill empty cells with 0 in Google Sheets
- Leveraging Find and Replace to populate blanks with zeros
- Entering a formula like =IF(ISBLANK(A1), 0, A1) to replace empties
The best approach depends on your data types.
Let’s get started filling those blank cells.
Copy Sample Sheet
If you intend to follow our step-by-step guide for filling empty cells with 0 in Google Sheets, please copy our sample data by clicking the link below.
Filling Empty Cells with 0 in Google Sheets Using Filters
The easiest way to fill empty cells with 0 in Google Sheets is by using filters. Filters temporarily hide rows so you can see and fill only empty cells.
To show you how to fill empty cells with 0 in Google Sheets we are going to use the following mixed sample data with blank cells scattered throughout:
Our goal with the above sample data is to fill all the blank cells with 0. Read on as we break down the steps together. After reading this step-by-step guide, you should be able to fill empty cells with 0 in Google Sheets seamlessly.
Let’s dive right in.
Step 1: Select Data Range
The first thing we need to do before we can fill empty cells with 0 in Google Sheets is to select the Data range. For this example, we will select the entire column A that contains the data with blank cells to be filled. Also, don’t forget to include the header.
Step 2: Apply Filter
After selecting the range of data you want to work with, the next step is to apply the filter, which is super easy.
To do that, navigate to the Data menu and select the option for Create a filter.
If the steps we discussed above sound complicated, you can click on the filter icon located on the toolbar. You’ll get the same result.
After applying the filter like we showed you above, you should have something like this:
Looking at the screenshot above, you can see that we have been able to add the filter icon to our spreadsheet. This shows that we have successfully applied filter to our selected data range.
Now, let’s proceed to the next step.
Step 3: Clear Filter
After successfully applying the filter to our selected range, let’s clear the filter. To do that, click on the filter Icon. From the drop-down options, click on the option for Clear. Clicking on this option will clear all the filter values from the list.
Step 4: Filter for Blank Cells
Having cleared the filter values, we must filter our data to only display blank cells. To do that, you only need to click on Blanks from the list of values. This simple action allows you to filter your data to display rows with blanks.
After selecting selecting Blanks, go ahead and click on OK. Google Sheets should instantly filter your data to only display cells with blank data.
Here is what ours looks like:
Step 5: Type 0 in the First Blank Cell
Since our goal is to fill empty cells with 0 in Google Sheets, let’s go ahead and type 0 in the first blank cell in our spreadsheet. For this guide, that would be cell A2.
Having done that, copy this value down to the rest of the cells in the filter range. You can do that by dragging down the fill handle.
The video below shows you how that works:
Step 6: Remove Filter
After executing the last step as we showed you, remove the filter. To do that, navigate to the data menu and select the option for Remove filter.
Once you remove filter, all the hidden values in your spreadsheet should now come back in full view. And what’s even more impressive is that the blank cells you earlier had in your spreadsheet should now display 0.
Here is what ours looks like:
The best part about using filters is that it works for text or number data. It also doesn’t affect any existing data or formulas you have in the non-empty cells.
Using Find and Replace to Fill Blank Cells with 0 in Google Sheets
In the previous section, we showed you how to use a filter to fill empty cells with 0 in Google Sheets. Now, we will take it a step further and introduce another way to do this.
Our second method for filling blank cells with 0 in Google Sheets involves using the “Find and Replace” feature.
This method has slight variations depending on whether your data is numeric (numbers only) or alphanumeric (text and numbers). This can be especially helpful if you’re building your own financial templates (including planning for IRA contributions).
We will look at both cases in the following sections.
How to Put 0s in Blank Cells When You Have Text and Numbers Mixed
The first example we would like to discuss when using the Find and Replace feature to fill empty cells with 0 in Google Sheets is when dealing with data featuring both text and numbers.
Imagine you have data like the one below with both text and numbers, but some cells are blank and you need to put 0s in the blank cells. You can execute this using the Find and Replace tool.
The Find and Replace box in Google Sheets looks like this:
But before we go over the steps showing you how to use the find and replace feature to fill empty cells with 0 in Google Sheets, let’s provide more context.
In Excel, you could leave the Find field empty and have Excel replace all blank cells with 0. But Google Sheets doesn’t let you leave Find empty. So you need to use a regular expression to show a blank space.
In Google Sheets, you need to use the “^s*$” regular expression for a blank value. This means “a cell with only whitespace and any number of whitespace characters.
Now, that you understand how this works, let’s review the steps together.
Step 1: Highlight your Data
To use the find and replace feature in Google Sheets to fill empty cells with 0, you first need to choose the range of data you want to work with. So for this example, we will select cells A2 to A13.
Step 2: Edit > Find and Replace
After selecting the range of data you want to work with, navigate to the Edit menu and select the option for Find and Replace.
Alternatively, you can use the shortcut CTRL+H to get the same result. Feel free to choose what works best for you.
Step 3: Type this Regular Expression ^s*$
After selecting the Find and Replace option, Google Sheets will launch the Find and Replace window. What we want to do here is type this regular expression ^s*$ in the input box next to Find.
With that completed, let’s now go ahead and enter 0 in the input box next to Replace with.
After typing 0 in the input box next to Replace with, the next thing you want to do is check the box next to Match case and Search using regular expressions.
Step 5: Click on Replace All and Done
After executing all the instructions we showed you in step 4, click on Replace all, then Done.
This action should fill empty cells with 0 in Google Sheets.
Here is what ours looks like:
From the screenshot above, you can see that we have filled the empty cells in our selected range with 0 and achieved this by simply leveraging the find and replace tool in Google Sheets.
How to Put 0s in Blank Cells When You Have Only Numbers
As I mentioned before, Google Sheets only lets you use regular expressions on text data. So if your data is numbers only, you need to convert it to text before using Find and Replace.
We will use the numeric-based sample data below to show you how to fill empty cells with 0 in Google Sheets.
With our data nicely put together, let’s review the step-by-step process.
Step 1: Choose your Range of Data
Kick things off by choosing the range of data you want to work with. For this example, we will choose cells A2:A9.
Step 2: Format > Number > Plain Text
With the range of data, you want to work with selected, head over to the Format menu and select the option for Numbers. This action should reveal several more options. From those options, choose the one for Plain text.
Step 3: Edit > Find and Replace
After converting your selected data to plain text, navigate to the Edit menu and choose the Find and Replace option.
This action should launch the Find and Replace tool.
Step 3: Type this Regular Expression ^s*$
Once the find and replace tool launches, type this ^s*$ into the input box next to Find.
In the input box next to Replace with, enter 0.
Once you type 0 in the input box for Replace with, check the box next to Match case and the one next to Search using regular expressions.
Step 5: Hit Replace All and Done
After completing the instructions we detailed above, hit Replace all, then Done. Google Sheets should instantly fill all empty cells within your selected range with 0.
Here is what ours looks like:
Looking at the image above, you can see that we have successfully filled the blank cells in our selected range with 0. And we were able to do that using the find and replace feature in Google Sheets.
Using a Formula to Fill Empty Cells with 0 in Google Sheets
In addition to the methods we’ve discussed for filling empty cells with 0 in Google Sheets, did you know there’s also a straightforward formula-based approach?
Indeed, and in this section, we’ll guide you through the process.
Let’s say you’re working with a dataset like the one below and tasked with replacing all the blank cells with 0 using a formula. Here’s how you can accomplish this task efficiently:
Step 1: Choose a Blank Cell
Start by choosing a blank cell in your spreadsheet. This is where we want our results to be generated. For this example, we will go with cell C2.
Step 2: Enter the Formula
Now that you have selected an area in your spreadsheet where you want the result generated, navigate to the formula bar and type in the following formula:
=IF(ISBLANK(A2), 0, A2)
This formula checks if the cell in column A2 is blank. If it is, it returns 0; otherwise, it returns the value in the cell.
Step 3: Hit Enter
Once you type the formula we showed you in the previous step, hit Enter on your keyboard. This action authorizes Google Sheets to generate the result in the selected cell.
Here is what ours looks like:
Step 4: Copy the Formula Down
So far, we have only generated the result for cells A2. Now, let’s do the same for the other cells. But instead of repeating the steps over and over again, we can use the Google Sheets auto-fill option to automate the process. Here is how to go about it.
Drag the fill handle (a small square at the bottom-right corner of the cell) down to fill the rest of the cells in your helper column with the formula. This action will automatically adjust the formula for each row.
The video below should provide better insight.
Step 5: Review the Results
After executing all the steps we detailed so far, take a few minutes to review the results. You should see 0s imputed in the blank cells if you did everything right.
Here is what ours looks like:
This method is particularly useful as it doesn’t alter your original data immediately, giving you a chance to review the changes before making them permanent. It’s also a dynamic solution. This means, if your original data changes, the helper column will automatically update to reflect those changes.
Final Thoughts
As we conclude this tutorial, it’s clear that filling empty cells with 0 in Google Sheets is not just a trivial task but an essential skill for maintaining data integrity, especially in large datasets.
The journey through these easy yet effective methods – using filters, Find and Replace, and formulas – demonstrates how versatile Google Sheets can be in handling different data types, whether text, numbers or a mix of both.
- The Filter Method: An intuitive way to visually segregate and fill empty cells. This approach is particularly user-friendly, ensuring you don’t alter existing data or formulas, making it perfect for beginners.
- Find and Replace Technique: By employing regular expressions, this method offers a quick solution, especially for alphanumeric datasets. It’s a powerful tool that, once mastered, can significantly streamline your data management process.
- Formulas for Dynamic Updating: The formula-based method, using =IF(ISBLANK(A2), 0, A2), offers a dynamic way to fill empty cells. It’s particularly useful as it allows for real-time updates without permanently altering your original data, giving you the flexibility to adjust as your dataset evolves.
Remember, each method has its own set of strengths, and the best choice depends on your specific data type and requirements.
Whether you’re working with sparsely populated datasets or conducting complex calculations, these techniques ensure that your data remains clean, accurate, and reliable.
We hope today’s guide has helped you grasp how to fill empty cells with 0 in Google Sheets. If you need more clarification, feel free to comment; we will be happy to respond.