How to Replace Blanks With Zeroes in Excel


Typing "0" in each individual blank cell in Microsoft Excel would be a tedious process. Fortunately, Microsoft Excel has a built-in function with which you can select all the blank cells within a given range and type "0" only once to place a zero in every selected blank cell. If you need to later replace a zero in one of the cells with another value, you can type over the zero with the new value or formula.

Things You'll Need

  • Microsoft Excel
  • Click in cell A1.

  • Type "3" and press "Enter." This will serve as a nonblank cell for the following example.

  • Select a range of cells in an Excel worksheet that contains the blank cells you want to convert to zeros. Click and hold the left mouse button in the top left cell of the range, drag the mouse cursor to the lowest right cell of the range and release the left mouse button. For the following example, click and hold the left mouse button in cell A1, drag the mouse cursor to cell C7 and release the left mouse button. This highlights the range of cells A1 to C7.

  • Click on the "Home" tab at the top of Excel.

  • Click on the "Find & Select" drop-down menu in the "Editing" menu.

  • Click on "Go To Special."

  • Click in the circle to the left of "Blanks."

  • Click "OK." This highlights all the blank cells in the selected range.

  • Type "0."

  • Press and hold "Ctrl" and press "Enter." This places a "0" in each highlighted blank cell. In the example, Excel leaves cell A1 alone because it is not blank.

Tips & Warnings

  • You can type any other value in Step 9 to replace the blank cells with that value.
  • Steps 1 and 2 are only for the example to prevent Excel from giving an error message when finding blank cells in the selected range. You can skip these steps if you already have a range that contains blank and nonblank cells.
  • If you press "Enter" without first holding "Ctrl" in Step 10, Excel will place a "0" in only the active cell.

Related Searches


  • Photo Credit Stockbyte/Stockbyte/Getty Images
Promoted By Zergnet



You May Also Like

  • How to Convert a String to Int in Excel

    When you import numbers from another source, spaces and non-printing characters sometimes accompany the numbers. These non-numeric characters can make Excel 2013...

  • Help for a Blank Cell in Excel

    As with all spreadsheets, every workbook is made up of cells. When you open a new one, they're all blank. This can...

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!