sc_excel_blankwith0_imgheader

Using Excel 2010 we are going to look at how to replace blank cells with zeros. Selecting the cells manually is, of course one option, but if the spread sheet is large then the procedure would be rather time consuming. For this exercise we will use a couple of short-cuts.

Tools used for this exercise:

  • Find & Select, then the “Go To Special …” command
  • The “Ctrl” “Enter” keyboard short cut.


Suppose we have the following spread sheet:
sc_excel_blankwith0_img01
And we would like to change all the blank cells in the numerical part of the sheet with zeros.

The first step is to select the range:
sc_excel_blankwith0_img02

Part 1: Getting the Blank Cells:

Next click on the Drop-down arrow beside the Find & Select command on the Home Tab, and select “Go to Special …”
sc_excel_blankwith0_img03
The following dialog box will open:
sc_excel_blankwith0_img04
Click on the button beside “Blanks” and then on OK

The cells, within the range we initially choose, are now selected. (The white box is the active cell and the cells shaded blue are the other selected cells):
sc_excel_blankwith0_img05

Part 2: Entering Data and Using Ctrl Enter:

Next simply type a “0” into the active cell:
sc_excel_blankwith0_img06
And rather than using the Enter or checkmark, click and hold on the “Ctrl” key then hit the “Enter” key. This will populate all the selected cells.

Here are the results:
sc_excel_blankwith0_img07
The spread sheet now has a zero placed in each of the blank cells for the data range!