In this tutorial, you will learn how to replace blank cells with zero in Excel and Google Sheets.
Replace Blank Cells With Zeros
If you have a list of numbers with blanks cells, you can easily replace each with a zero. Say you have the data set below in Column B.
To replace the blanks – in cells B4, B6, B7, and B10 – with zeros, follow these steps:
1. Select the range where you want to replace blanks with zeros (B2:B11) and in the Ribbon, go to Home > Find & Select > Replace.
2. In the pop-up window, leave the Find what box empty (to find blanks). (1) Enter 0 in the Replace with box and (2) click Replace All.
This way, each blank in the data range is populated with the value zero.
Note: You can also achieve this using VBA code.
Replace Blank Cells With Zero in Google Sheets
1. Select the range where you want to replace each blank with zero (B2:B11), and in the Menu, go to Edit > Find and replace (or use the keyboard shortcut CTRL + H).
2. In the Find and replace window, (1) enter “^\s*$” for Find. In Google Sheets, “^\s*$” stands for a blank value, so type that in instead of leaving the Find field blank.
Next, (2) enter 0 for Replace with. (3) Check Match case and (4) Search using regular expressions, then (5) click Replace all.
As a result, all originally blank cells now have zero values instead.