Office solution: How to enter the same value in non-contiguous cells

This week, learn the solution to last week's Excel challenge: How would you enter the same value into numerous non-contiguous cells.

Last week, we asked you to share the simplest way to enter the same value, 0 in this case, into the non-contiguous cells in the following sheet.

Damiross was the first to respond with an old, but handy trick. First create a non-contiguous selection by holding down the [Ctrl] key while clicking all the empty cells. Then, type 0 and press [Ctrl]+[Enter] to enter 0 in all of the selected cells. This certainly works, but if you've lots of blank cells to fill, you'd want something a bit more efficient.

Electronics_md then suggested using Find and Replace. By leaving the Find What control empty, the search will find all empty cells. Enter a 0 in the Replace With control and click Replace All. It's a great solution.

Tink shared keyboard shortcuts for both of the above solutions - thanks Tink!

Zimmerwoman's solution was to use Paste Special to add 0 to all of the cells in the data range. Now, that's innovative and I didn't think of it. This solution only works if the value you're entering is 0 and it might have some far-reaching repercussions, but I can't think of any off the top of my head.

Sherinx was the first to suggest the solution I had in mind - use Paste Special as follows:

  1. Select the data range. Sherinx suggested [Ctrl]+A; in the case of the example sheet, select C2:N4.
  2. Press [F5].
  3. Click the Special button.
  4. Click the Blanks option.
  5. Click OK.
  6. Type 0.
  7. Press [Ctrl]+[Enter] to enter 0 in all of the selected (blank) cells.

This technique automates Damiross' solution - the Paste Special option selects all of the empty cells for you, by passing the [Ctrl]+Click process. In addition, it'll work with any value, not just 0. Thanks for another great challenge!

What's challenging you lately?

By Susan Harkins

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.