Fill All Blank Cells in an Excel Range With a Desired Value
How to fill blank cells in Excel with a desired value? In this tutorial we’ll be selecting all the blank cells and fill them in.
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
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.
This is followed by either F5 or Ctrl+G or a Home/Find & Select/Go ToSpecial… command
Whereas the first two keyboard shortcuts (F5 or Ctrl+G) will take to the GoTo window where you have to select Special…
The Home/Find & Select/Go ToSpecial… will take you to the desired command directly.
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
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.
This brings us ever so close to eternal happiness in Excel 🙂
Learn more
Check out our YouTube channel and subscribe for more amazing Excel tricks!
Follow us on LinkedIn.
Check out our brand new R Academy!
Comments 14
Leave a Reply
Excel Unplugged acknowledgements
Donate
Gasper, that is cool! & quick! Much better than Ctrl + click a cell… Thanks!
Thanks! Hope it saves you plenty of time in the future 🙂
Great, thanks!
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
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.
hi,
very cool tip ever
Brilliant tip – really useful. Just one niggle … I also want to unlock cells where a formula returns an empty cell … is that possible?
Hi,
How would I select the blank cells in just one column as oppose to the entire sheet?
And how would I be able to select blank cells up to the point that fall under an above cell with a value but not go to other blank cells that fall in the same column but under another value e.g.
This is how my data is:
Fred 200
130
504
Jane 250
372
Peter 100
This is how I want it to be:
Fred 200
Fred 130
Fred 504
Jane 250
Jane 372
Peter 100
There were two sets of blank cells – those under Fred and those under Jane.
Is there a way to fill the first set of blank cells with one set of information (Fred as in this example) and fill the second set of blank cells with another set of info. insted of fill all blank cells with the same info.?
You would have to select the ranges individually, write up the desired value and press Ctrl+Enter… There is no way (except with a formula (for example) IF(ROw()
The data isn’t displaying correctly so I am reposting it:
This is how my data is:
Fred 200
[blank] 130
[blank] 504
Jane 250
[blank] 372
Peter 100
But you could try with Power Query and the Power Query Fill/Down command which is different than the Excel one.
The easiest way is to insert a blank column after the names column.
Assuming ‘Fred’ is in A1, then in B1, put =A1, and in B2 put =IF(A2=””,B1,A2) and copy that all the way down.
You can then Copy and Paste Values in column B, and delete column A, if you want to tidy it up afterwards.
Thanks Stevie… So that is another formula solution, but I would still opt for a Power Query one.
Thanks Stevie. That is another possible formula solution. But the Power Query solution is far more elegant although it has to be said it does create a new table…