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?