How to Replace Blank Cells With 0 (Zero) in Excel & Google Sheets

This tutorial demonstrates 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). Enter 0 in the Replace with box and 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.