Excel Unplugged

Fill All Blank Cells in an Excel Range With a Desired Value

Here’s a nifty little trick I use every day while working in Excel. It’s a two part trick. Part one will be selecting all the blank cells in a range or rather reducing a selection or a range to blank cells within that range. Part two will be how to enter a value or a formula(!) into multiple cells in Excel simultaneously.

Here’s our example in Excel

Fill All Blank Cells in an Excel Range With a Desired Value

Let’s say we want to write a value of 0 (zero) into all the empty Cells. Now for those of you thinking this would be far easier by using the Ctrl+H (Find and Replace) I have only this to say. The method described here is much cooler and also, there are cases where Ctrl+H does not work, but this does 🙂

Ok so let’s get to it.

Reducing a Selection to blank cells only

Our final goal is to put a value into all blank cells, but to do this we must first select only those cells. This can be easily accomplished by selecting an entire range of data by using a Ctrl+A shortcut in Excel.

Fill All Blank Cells in an Excel Range With a Desired Value

This is followed by either F5 or Ctrl+G or a Home/Find & Select/Go ToSpecial… command

Fill All Blank Cells in an Excel Range With a Desired Value

Whereas the first two keyboard shortcuts (F5 or Ctrl+G) will take to the GoTo window where you have to select Special…

Fill All Blank Cells in an Excel Range With a Desired Value

The Home/Find & Select/Go ToSpecial… will take you to the desired command directly.

Fill All Blank Cells in an Excel Range With a Desired Value

Now this is one of the best commands in Excel. In our case we will use the Blanks option. But take a look at some other options available in this window and let your imagination go wild!

Ok so we select Blanks and we get

Fill All Blank Cells in an Excel Range With a Desired Value

So our selection was reduced only to cells that are blank. This leads us to part two…

Enter a value or a formula into multiple cells in Excel simultaneously

There is only one trick here and that is the following key combination Ctrl+Enter. So without clicking (!!! If you click anywhere the whole selection is lost and you are back to square one), you type a value you would like to put in all of the selected cells and press Ctrl+Enter. And that value now resides in all selected cells.

Fill All Blank Cells in an Excel Range With a Desired Value

This brings us ever so close to eternal happiness in Excel 🙂

Comments 7

  1. Deb says:

    Gasper, that is cool! & quick! Much better than Ctrl + click a cell… Thanks!

    1. Thanks! Hope it saves you plenty of time in the future 🙂

    2. Bobby says:

      Great, thanks!

  2. One very useful addition …
    If you want to excel database to fill the blank cell next vrujednostima above, after the step where empty cells are selected, type the equal sign (“=”) and press CTRL + ENTER.
    This I read about fifteen years ago in a book and I believe that is one of the best practices that I have learned.
    Andrija

    1. Hi,
      not sure I understand you correctly. seems to me that all this method would give is an equal sign (=) in a cell. Could be I’m misunderstanding your comment.

  3. sai babu says:

    hi,

    very cool tip ever

  4. Stevie D says:

    Brilliant tip – really useful. Just one niggle … I also want to unlock cells where a formula returns an empty cell … is that possible?

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: