How to fill empty cells with 0, with value above/below in Excel
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 2010 and 2013.
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.
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.
Table of contents
152 responses to "How to fill empty cells with 0, with value above/below in Excel"
This worked great for me!!!
Nice! and helped me with thousand of records. Thank you
I hope you work with Microsoft team of excel help to describe everything as you did here. Thank you
Awesome... saved me alot of time. IT WORKS PERFECTLY
Thank you. helped me with thousand of records.
how can fill slush in blank cell
How could I fill the blank cells with formulas that change? Let's say i want to add vlookup only in blank cells but the first reference must change in every cell so that it will be the horizondal value.
Thank you for this!! My wrist was hurting from dragging and I knew there had to be a better way. Talk about a life saver!!
Thank you.
Thanks a lot, great shortcut!
you are in point of fact a excelkent webmaster. The website loading pace is incredible.
It kind of feels that yyou are doing any unique trick.
Moreover, Thee contdnts are masterwork. you've performed a fantatic task in this topic!
Thanks for this, extremely time saving method, works perfect!!
Hurrah! After all I got a weeb site from where I be able to in fact take valuable facts regarding my study and
knowledge.
Saved me a lot of time. Brilliant, thank you!
Wow! Thank you so much. You saved a lot of time for me! ;)
Thank you so much....amazing short cut.....
thank you for these :)
EXCELLENT SIR, I AM APPLY THIS FORMULA THIS IS WORKING AND HELP FULL. THANK YOU ONCE AGAIN SIR
You mean Miss, not Sir. Why would you assume the writer is a man? You are talking to an excel Queen here.
Thank you very much sir, it saved me lot of time and tension
Great tip! Thanks a lot :)
Grt!
thank you so much - amazing tips..
Do you know if there's any way to do this in google sheets?
Thanks!
Thank you for the clear concise instructions!
Its very helpful.Thank you so much for this detailed explanation
What a great piece! Thanks a lot.
Learnt something new today.. thanks a lot
THANK YOU SO MUCH..Saved me lots of time. God Bless!
I would like to highlight every other row in my Excel worksheet to facilitate viewing data. Is there an easy way to do that? I have over 50 rows each month.
Hello!
I am not sure I fully understand what you mean.
What lines do you want to highlight? Please describe your problem in more detail.
Hi Brad,
You can use predefined Excel table styles (Banded rows feature) or set up your own conditional formatting rule to alternate row color. We have a special tutorial that covers both methods in detail: How to highlight every other row in Excel
Thats amazing! You helped me save so much time.
Thanks a lot for sharing the wonderful tips!
Really helpful! Thanks so much!!
Thank you so much!
That's gr8!
Thanks . Many Many help us.
Thanks . Many Many help us jillal
I work in the financial services and you lot just saved me my job!!! Thank youuuuuu :)
Really Helpful ! Thank you
Filter the Blank Cells.
if C1 contain the data to fill & 1st blank cell is C2
then type +C2=C1
then drag it to the below.....
This is easy for me..
Hello, this does not seem to be working for me.... In the cells to be filled with the above cell content, only the formula entered appears, not the value. eg in the empty cells I see "=A2" but not the value...
had 3k records to fill in the gaps for.. saved my life
Bless you! :D :D
Thank you, worked perfectly :)
I'm so grateful that you exist. TRULY