Office challenge: How would you enter the same value into numerous non-contiguous cells?

This week, test your Excel skills by answering this simple user question: How can I enter the same value in several non-contiguous cells?

Leaving a cell blank can have unexpected consequences. For instance, the value 0 and a blank cell might visually suggest the same thing - there's no valid entry - but many Excel functions will evaluate them differently. For example, Excel's AVERAGE() function will return 20 when evaluating three cells with the following values: 10, blank, and 30. If you enter a 0 into the blank cell, AVERAGE() returns 13.3. Excel totally ignores the blank cell, but evaluates the 0. For this reason, it's a good idea to use 0s instead of leaving cells blank (unless you have a specific reason for doing so - you might want Excel to ignore the blank cells) in a numeric data range.

Now, let's suppose a user is working with a sheet similar to the one shown below and he needs to fill all the blank cells with the value 0. (It could be any value, but let's stick with the zero example.) In a small sheet, entering all those 0s would be a quick task, but imagine a sheet with hundreds or thousands of blank cells! Can you help this user enter the value 0 into all those blank cells, without entering them one-by-one?


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks