Annyca Tabassum
In Google Sheets when you deal with a large dataset, it is very difficult to find and replace blank cells. But luckily by using some methods you can easily overcome this problem. In this article, we will try to explain the ways to find and replace blank cells in Google Sheets.
A Sample of Practice Spreadsheet
You can download the practice spreadsheet from the download button below.
3 Ways to Find and Replace Blank Cells in Google Sheets
You can apply three techniques to find blank cells and replace them with a 0 (zero) value. The techniques are: using the find and replace option, inserting filter, and applying a function
To show all the methods we create a dataset containing the Fruits Name and Quantity (Kg).
1. Using Find and Replace Option
You can easily replace the blank cells with 0 by applying the Find and Replace feature. To do so,
- First, select a range of cells in which you want to apply the technique. Here, we select the range C5:C10.
- Then go to the Edit bar and select the Find and Replace feature. You can also use the CTRL+H shortcut to open the find and replace option.
- After that Find cell type ^\s*$ to find blank cells and in the Replace option type 0.
^\s*$- This is a regular expression for whitespace.
- Now, check the ‘Match case’ and ‘Search using regular expressions’ boxes. You will find the blank cell number at the top of the Find bar.
- In the end, click on the Replace All button and then press Done.
- Finally, you will find the blank cells replaced with 0 in the dataset.
Read More: How to Use REPLACE Function in Google Sheets (4 Examples)
2. Inserting Filter Feature
By inserting the filter option, you can separate or filter the blank cell data and then easily fill them with 0 or whatever you want to input. To apply this technique,
- In the beginning, select the column and go to the Data menu and select Create a filter option.
- Now, you can see a filter icon next to the selected column.
- Go to the filter icon and you will find all the values selected to filter.
- Now, click on the “Clear” option to clear the list.
- Then, select only “Blank” from the list and press OK.
- You can find the bank values in the filtered dataset.
- Now, insert 0 in the blank cell.
- After that drag down the fill handle to insert the value into the entire column.
- In the end, press ‘Remove filter” to remove the filter option.
- Finally, you will find the whole dataset where the blank cells fill with 0 value.
Read More: How to Use Data Validation and Filter in Google Sheets (4 Ways)
3. Applying Functions
We can also apply formulas to find and replace blank cells. But if you apply the formula directly to your dataset it might change the formatting. To avoid this error, we create a new column name “Formula Outcome” to apply the formulas and then paste it into the main desired column.
Read More: How to Find and Replace Multiple Values in Google Sheets
Similar Readings
- How to Filter with QUERY for Multiple Criteria in Google Sheets (An Easy Guide)
- Filter By Rows in Google Sheets (An Easy Guide)
- How to Delete Filter Views in Google Sheets (An Easy Guide)
3.1 ISBLANK Function
The ISBLANK function is very simple and basic. This function checks whether the referenced cell is truly blank or not. To get the exact result we combine this ISBLANK function witty the IF function.
- First, select a cell where you want to apply the formula. Here, we select cell D5.
- Then insert the IF function.
- After that add the ISBLANK function.
- Now, insert the C5 cell as the value argument.
- The whole ISBLANK function is used as a logical_expression parameter in the IF function.
- Now, add 0 for the value_if_true parameter.
- Apply the cell C5 for value_if_false.
- Press ENTER, and you will find the result.
=IF(ISBLANK(C5),0,C5)
Formula Breakdown
- ISBLANK(C5): The ISBLANK function only returns TRUE or FALSE. For blank cell it presents TRUE on the other hand, for value it turns FALSE.
- IF(ISBLANK(C5),0,C5): It specifies the ISBLANK function. If it returns TRUE then the value will be 0, if it returns FALSE then the original value remains.
- Now, apply the formula to the entire column by dragging down the fill handle.
- After that select the entire new column and press CTRL+C to copy and click on CTRL+SHIFT+V to paste only the values in the source column.
- Finally, delete the Formula Outcome column. The blank cells now fill with the 0 value.
3.2 N Function
The N function is very handy if you want to replace your all-blank cells with 0 because if you insert a number, you will get the same as you insert, but if you insert text or blank cells it automatically returns 0. You will be able to input the formula into the entire column by combining the N function with the ARRAYFORMULA function.
- In the earlier stage, select cell D5.
- Now, apply the ARRAYFORMULA function.
- After that, insert the N function.
- Then add the entire source column as value.
- Press ENTER and you will find the outcome in the entire column.
=ARRAYFORMULA(N(C5:C10))
Formula Breakdown
- C5:C10: Represents the range from where the blank cells will be found.
- N(C5:C10): Returns the same number for the numbered cell and 0 for the blank cells.
- ARRAYFORMULA(N(C5:C10)): This helps to apply the formula to the entire column in one go.
- Copy the results by clicking on CTRL+C and paste only the value in the source column by pressing CTRL+SHIFT+V.
- Finally, remove the new generate column and you will find the blank cells filled with 0 in the original dataset.
Things to Remember
- Carefully input the regular expression.
- Apply the formulas in different cells to avoid format errors.
Conclusion
We believe this article will be helpful for you if you want to find and replace blank cells in Google Sheets. For further queries feel free to comment. Moreover, you can explore the OfficeWheel website to learn more about any functions of Google Sheets.
Related Articles
- How to Use Wildcard in Google Sheets (3 Practical Examples)
- Google Sheets: The FILTER Function (A Comprehensive Guide)
- Filter Entries if it Does Not Contain Value in Google Sheets (2 Easy Ways)
- How to Filter Based on a Cell Value in Google Sheets (2 Easy ways)
- Applying Filter with REGEXMATCH Function in Google Sheets (Easy Examples)
- How to Create Filter Views in Google Sheets (An Easy Guide)
- Set a Filter in Google Sheets (An Easy Guide)
