BITS & BYTES: Replace blank cells in Excel

2013-01-02T15:38:00Z 2013-01-02T16:36:16Z BITS & BYTES: Replace blank cells in ExcelApril Miller Cripliver Times Business Columnist nwitimes.com
January 02, 2013 3:38 pm  • 

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.

Opinions are solely the writer's. April Miller Cripliver, of Chesterton, has a doctorate in management information systems and is a computer hardware and software consultant. E-mail your computer questions to nwitimes@cripliver.com, and specify your operating system and other pertinent PC information.

Copyright 2014 nwitimes.com. All rights reserved. This material may not be published, broadcast, rewritten or redistributed.

activate-button-3
Follow The Times

Latest Local Offers

In This Issue

Professionals on the Move Banner
Get weekly ads via e-mail

Poll

Loading…

Should Chicago’s Pullman neighborhood receive national park status?

View Results