Microsoft

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?

About

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.

5 comments
DBlayney
DBlayney

The "smart" solutoons only work if you are trying to fill _all_ blanks cells in a defined rectangular area. Only Damiross's solutions works if you want to enter a value in any seection of non-contiguous cells. Others require the value to be numeric. To answer Bob Duncan: Yes it does!

bob77duncan
bob77duncan

Question ? - Will this solution also work for formulas/equations? Bob Duncan

zimmerwoman
zimmerwoman

I just learned it existed from a column a couple of weeks ago. I am using the Dickens out of it and spreading the word in my office. We do things the long way so often. I love finding new shortcuts to know and tell.

david.hanshumaker
david.hanshumaker

Yes, it will. I do it all the time. You might need to pay careful attention to absolute and relative addresses in your expression though.

ssharkins
ssharkins

That's really my only mantra -- find a solution that's efficient and that you can remember.