by In this article you'll learn a trick to select all empty cells in an Excel spreadsheet at once and fill in blanks with value above / below, with zero or any other value.
To fill or not to fill? This question often touches blank cells in Excel tables. On the one hand, your table looks neater and more readable when you don't clutter it up with repeating values. On the other hand, Excel empty cells can get you into trouble when you sort, filter the data or create a pivot table. In this case you need to fill in all the blanks. There are different methods to solve this problem. I will show you one quick and one VERY quick way to fill empty cells with different values in Excel.
Thus my answer is "To Fill". And now let's see how to do it.
How to select empty cells in Excel worksheets
Before filling in blanks in Excel, you need to select them. If you have a large table with dozens of blank blocks scattered throughout the table, it will take you ages to do it manually. Here is a quick trick for selecting empty cells.
- Pick the columns or rows where you want to fill in blanks.
- Press Ctrl + G or F5 to display the Go To dialog box.
- Click on the Special button.
Note. If you happen to forget the keyboard shortcuts, go to the Editing group on the HOME tab and choose the Go To Special command from the Find & Select drop-down menu. The same dialog window will appear on the screen.
The Go To Special command allows you to select certain types of cells such as ones containing formulas, comments, constants, blanks and so on.
- Select the Blanks radio button and click OK.
Now only the empty cells from the selected range are highlighted and ready for the next step.
Excel formula to fill in blank cells with value above / below
After you select the empty cells in your table, you can fill them with the value from the cell above or below or insert specific content.
If you're going to fill blanks with the value from the first populated cell above or below, you need to enter a very simple formula into one of the empty cells. Then just copy it across all other blank cells. Go ahead and read below how to do it.
- Leave all the unfilled cells selected.
- Press F2 or just place the cursor in the Formula bar to start entering the formula in the active cell.
As you can see in the screenshot above, the active cell is C4.
- Enter the equal sign (=).
- Point to the cell above or below with the up or down arrow key or just click on it.
The formula
(=C3)shows that cell C4 will get the value from cell C3. - Press Ctrl + Enter to copy the formula to all the selected cells.
Here you are! Now each selected cell has a reference to the cell over it.
Note. You should remember that all cells that used to be blank contain formulas now. And if you want to keep your table in order, it's better to change these formulas to values. Otherwise, you'll end up with a mess while sorting or updating the table. Read our previous blog post and find out two fastest ways to replace formulas in Excel cells with their values.
Use the Fill Blank Cells add-in by Ablebits
If you don't want to deal with formulas every time you fill in blanks with cell above or below, you can use a very helpful add-in for Excel created by Ablebits developers. The Fill Blank Cells utility automatically copies the value from the first populated cell downwards or upwards. Keep on reading and find out how it works.
- Download the add-in and install it on your computer.
After the installation the new Ablebits Utilities tab appears in your Excel.
- Select the range in your table where you need to fill empty cells.
- Click the Fill Blank Cells icon on the Ablebits Utilities tab.
The add-in window displays on the screen with all the selected columns checked.
- Uncheck the columns that don't have empty cells.
- Select the action from the drop-down list in the bottom-right corner of the window.
If you want to fill the blanks with the value from the cell above, choose the Fill cells downwards option. If you want to copy the content from the cell below, then select Fill cells upwards.
- Press Fill.
Done! :)
Besides filling empty cells, this tool will also split merged cells if there are any in your worksheet and indicate table headers.
Check it out! Download the fully-functional trial version of the Fill Blank Cells add-in and see how it can save you much time and effort.
Fill empty cells with 0 or another specific value
What if you need to fill all the blanks in your table with zero, or any other number or a specific text? Here are two ways to solve this problem.
Method 1
- Select the empty cells.
- Press F2 to enter a value in the active cell.
- Type in the number or text you want.
- Press Ctrl + Enter.
A few seconds and you have all the empty cells filled with the value you entered.
Method 2
- Select the range with empty cells.
- Press Ctrl + H to display the Find & Replace dialog box.
- Move to the Replace tab in the dialog.
- Leave the Find what field blank and enter the necessary value in the Replace with text box.
- Click Replace All.
It will automatically fill in the blank cells with the value you entered in the Replace with text box.
Whichever way you choose, it will take you a minute to complete your Excel table.
Now you know the tricks for filling in blanks with different values in Excel 2013. I am sure it will be no sweat for you to do it using a simple formula, Excel's Find & Replace feature or user-friendly Ablebits add-in.
195 comments
This is awesome trick... thanks a lot u saved my day
Thank you . Such a useful and helpful piece. it solved my problems
Thanks, easy instructions to follow - great help.
thank you
Thank You lot, i got what expected exactly
This is superb! It has saved me a crazy amount of work, but my only question is it possible to use this feature directly with blank cells actually containing a formula? E.g. IF statement, when the condition is false, returns "" meaning technically a blank cell? Thanks for your help =)
Hello Thanks a lot for the great trick to fill in blank cells..
But unfortunately, for few set of data i dint get the blanks filled even after following same procedure.. It says "no cells were found".
I have tried this on some other set of data and it works.. So i am confused.
Both the data are numbers!
Please help me find a way out.
In 2017, and this article just saved me a lot of time. Big thanks !
THANKS ALOT. YOU ARE GREATE .
This is extremely helpful!
Thanks a lot. It saved lot of work
Speechless!..thanks so much!
I had around 70,000 rows and honestly this saved my time. Thank you so much.
Big help! Thanks a lot! Hope you continue do this kind of tutorial. Saves lot of time ^_^
how to fill blank cells with combined numerical and letters ex(kiran123 bellow rows
copy cells with values just above it in column with blanks and muliptle values in between
smith
jone
daid
Thank for such valueable post
Love it helps in studies and information gave me confidence for test
Very helpful very good much help ful love it
Thank you so much!
Hi,
Excel formula to fill in blank cells with value above / below.
Doing the steps referring with the value above is working. However, if I do the same with value below it is not working. Can anyone help? I want the below value to be copied on the above blank cells.
Thanks in Advance.
Thanks... It saved me lot of time
How do I fill up?
@ashwith
It looks like all the cells are not in the DATE format. Format the required cells/column/row as DATE and select the required format on the drop down menu. If leading zeros are required in the date, you can FORMAT CELLS>NUMBERS>CUSTOM, then input dd/mm/yyyy (or mm/dd/yyyy)under "Type:"
this is great, but how we can apply for date , i got below type answer, kindly solve
06/04/2015
06/04/2015
06/04/2015
42100
06/04/2015
42100
06/04/2015
42100
06/04/2015
42100
06/04/2015
42100
06/04/2015
06/04/2015
07/04/2015
42101
07/04/2015
07/04/2015
42101
07/04/2015
07/04/2015
you are my hero! ..... thaaaaaaaanks
Thank you very very much.
Has anyone ran into the issues of hitting "crtl enter" and it doe not autofill the cells? I have ran this exact formula before but today it is not working?
You are amazing man.
You don't know that you have saved my 3 days of work and I have done that work in 5 minutes. Great job man.. God bless you.
:-)
i need formula
2.254+152.00
plz mere ko point ke above wali value ko hatane ka formula chayiye
It is too late. However, try rounding off values. Point ke above wali value hat jaayenge.
Thank you very much. You just saved me many hours
This was a huge help! Thank you!
Thanks a lot you made my day
It's the perfect one what im finding for and got it. Thanks
Find and Replace. Genius shortcut for what I needed. Thanks
The Best Option...
This worked, thanks!
Thank you so much. Fantastic.
thank you so much you made my day
I understand Good
I press f2 on my mac when trying to replace the empty numbers with zeros and it only leads me to brighten my screen.
Hello Maria,
Most likely you need to press the fn key at the same time, i.e. use the keyboard combination fn + F2.
When I do this method of the '=b2' it simply enters b2 all the way down. What did I do wrong?
Hi,
I know its a bit late after the fact but the issue is your column must be in 'text' format, same thing happened to me I changed it to 'General' and the above steps worked correctly.
You must be directly entering the command by "Enter" command, make sure you are using "Cntrl+Enter" at the same time, your issue will be solved
You just made my day!!
Now I can fill all those blank cells and make the Pivot Table work with "sum" instead of "count" which is so annoying when working with large files...
Thank you so much!
Thank you Sweety! :)
Thanks a lot
you're a genius thaaaanks
how can I fill the missing data of a row with the avarage of that row?
very nice..thank you
thanks
You're goddess. Thank you very much!
Markus