Q: I have an Excel spreadsheet. I want to replace all the blank cells with a zero. How do I do this easily and quickly?
A: This trick for entering a zero into each empty cell is invaluable to teachers or other scorekeepers who need to perform calculations on a group of numbers. Excel is programmed to ignore cells that are empty, giving results that are often unreliable.
Here’s an example:
Student A has the following scores for the last five assignments:
85
78
(blank)
56
64
Student B has the same scores for the last five assignments, but look at the third assignment’s score:
85
78
0
56
64
If you ask Excel to average the scores for these two students, Student A’s average will be reported as 70.75 (or 283 divided by 4), whereas Student B’s average will be 56.6 (or 283 divided by 5). Neither student turned in the third assignment, but because Excel skips blank cells, Student A’s average score is reported as being higher than Student B’s.
If you have quite a few of these blank cells, it would be tedious to enter a zero into each blank cell, and you might miss one or two on a large spreadsheet. Here’s a much easier way:
1. Press the F5 key on your keyboard. The Go To dialog box appears.
2. Click the Special… button.
3. Click the radio button for Blanks.
4. Click OK. All empty cells should now be selected.
5. Type a zero.
6. Press Ctrl-Enter. You should now see a zero in each empty cell.
Note that you can use this to replace the contents of blank cells with just about anything, such as a word, another number, or a special character. Simply change Step 5 above to the character(s) you want to appear in those blank cells.
Please Wait…