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:
- Select the data range. Sherinx suggested [Ctrl]+A; in the case of the example sheet, select C2:N4.
- Press [F5].
- Click the Special button.
- Click the Blanks option.
- Click OK.
- Type 0.
- 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?