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.

  1. Pick the columns or rows where you want to fill in blanks.
    Select the columns or rows where you want to fill in blanks
  2. Press Ctrl + G or F5 to display the Go To dialog box.
  3. Click on the Special button.

    Press Ctrl + G or F5 to display the Go To dialog box

    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.

    Choose Go To Special from the Find & Select drop-down menu to display the Go To Special dialog

    The Go To Special command allows you to select certain types of cells such as ones containing formulas, comments, constants, blanks and so on.

  4. Select the Blanks radio button and click OK.
    Choose Blanks to get only empty cells selected

Now only the empty cells from the selected range are highlighted and ready for the next step.
The empty cells become highlighted

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.

  1. Leave all the unfilled cells selected.
  2. 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.

  3. Enter the equal sign (=).
  4. Point to the cell above or below with the up or down arrow key or just click on it.
    Enter the equal sign and point to the cell above to display the formula in the empty cell

    The formula (=C3) shows that cell C4 will get the value from cell C3.

  5. Press Ctrl + Enter to copy the formula to all the selected cells.

    Press Ctrl + Enter to fill all the blank 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.

  1. Download the add-in and install it on your computer.

    After the installation the new Ablebits Utilities tab appears in your Excel.

  2. Select the range in your table where you need to fill empty cells.
  3. Click the Fill Blank Cells icon on the Ablebits Utilities tab.
    Install the add-in and find the Ablebits Utilities tab in the Excel window

    The add-in window displays on the screen with all the selected columns checked.
    Click Fill Blank Cells icon to open the add-in dialog window

  4. Uncheck the columns that don't have empty cells.
  5. 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.

  6. Press Fill.

    Choose the columns and the action for the add-in to fulfill

Done! :)

Press Fill to fill empty cells in Excel

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

  1. Select the empty cells.
  2. Press F2 to enter a value in the active cell.
    Press F2 to enter the value in the active cell of the selected range
  3. Type in the number or text you want.
  4. Press Ctrl + Enter.
    Press Ctrl + Enter to copy the input value from the active cell into all the blanks

A few seconds and you have all the empty cells filled with the value you entered.

Method 2

  1. Select the range with empty cells.
    Select the range in which you need to fill empty cells
  2. Press Ctrl + H to display the Find & Replace dialog box.
  3. Move to the Replace tab in the dialog.
  4. Leave the Find what field blank and enter the necessary value in the Replace with text box.
    Type in the value in the Replace with text box to fill in blanks with it
  5. 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.

155 responses to "How to fill empty cells with 0, with value above/below in Excel"

  1. Markus says:

    You're goddess. Thank you very much!

  2. Anonymous says:


  3. sanjay says:

    very nice..thank you

  4. Juan says:

    how can I fill the missing data of a row with the avarage of that row?

  5. hanae says:

    you're a genius thaaaanks

  6. S says:

    Thanks a lot

  7. Jinath says:

    Thank you Sweety! :)

  8. Ed says:

    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!

  9. Cyndi says:

    When I do this method of the '=b2' it simply enters b2 all the way down. What did I do wrong?

    • Allen says:


      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.

    • Rohan says:

      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

  10. Maria says:

    I press f2 on my mac when trying to replace the empty numbers with zeros and it only leads me to brighten my screen.

  11. Krish says:

    I understand Good

  12. phani says:

    thank you so much you made my day

  13. Ashwini Singh says:

    Thank you so much. Fantastic.

  14. Felipe says:

    This worked, thanks!

  15. Rajesh says:

    The Best Option...

  16. Dan says:

    Find and Replace. Genius shortcut for what I needed. Thanks

  17. Sivakumar says:

    It's the perfect one what im finding for and got it. Thanks

  18. purshotam says:

    Thanks a lot you made my day

  19. rmw says:

    This was a huge help! Thank you!

  20. Daniel says:

    Thank you very much. You just saved me many hours

  21. KANA SHARMA says:

    i need formula


    plz mere ko point ke above wali value ko hatane ka formula chayiye

  22. Sudhir says:

    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.


  23. Dustin says:

    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?

  24. George says:

    Thank you very very much.

  25. omar says:

    you are my hero! ..... thaaaaaaaanks

  26. ashwith says:

    this is great, but how we can apply for date , i got below type answer, kindly solve

  27. Mike says:

    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:"

  28. Alice says:

    How do I fill up?

  29. Vaithi says:

    Thanks... It saved me lot of time

  30. Oli says:


    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.

  31. Cindie Lucas says:

    Thank you so much!

  32. Cinderella says:

    Very helpful very good much help ful love it

  33. Prerna says:

    Love it helps in studies and information gave me confidence for test

  34. Amit Kamble says:

    Thank for such valueable post

  35. kim says:

    copy cells with values just above it in column with blanks and muliptle values in between



  36. kiran says:

    how to fill blank cells with combined numerical and letters ex(kiran123 bellow rows

  37. Juliet says:

    Big help! Thanks a lot! Hope you continue do this kind of tutorial. Saves lot of time ^_^

  38. Noufal says:

    I had around 70,000 rows and honestly this saved my time. Thank you so much.

  39. Ronald says:

    Speechless!..thanks so much!

  40. ravi says:

    Thanks a lot. It saved lot of work

  41. Butera says:

    This is extremely helpful!

  42. KRISHNA says:


  43. Mike says:

    In 2017, and this article just saved me a lot of time. Big thanks !

  44. Aparna says:

    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.

  45. Christopher says:

    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 =)

  46. Ranjth says:

    Thank You lot, i got what expected exactly

  47. Biniyam says:

    thank you

  48. AK says:

    Thanks, easy instructions to follow - great help.

  49. Tee says:

    Thank you . Such a useful and helpful piece. it solved my problems

  50. Shy says:

    This is awesome trick... thanks a lot u saved my day

  51. Gunin Gogoi says:

    This is excellent!

    Thanks Ablebits for this most valuable contribution and make our work-life easier!!

  52. Chandra Sekhar says:

    I Raied So Much Times But Results Is Zero But Here Am Vewing This Site I Slove Simple Method I Slove It Thanks A Lot.................................

  53. Udayan Tripathi says:

    If I am have entry in below format:
    DLM0509 5
    DLS0074 4
    DLS1491 3
    DLA4254 1
    DLS1483 7
    DLA3341 3
    So can I get these entries in below manner:

    Please help!!

  54. Udayan Tripathi says:

    If I am have entry in below format:
    DLM0509 5
    DLS0074 4
    DLS1491 3
    DLA4254 1
    DLS1483 7
    DLA3341 3
    Is there is any direct formula to get the entries in below manner:


  55. Geoff says:

    Thanks for the fantastic tip

  56. Padon says:

    Thnks somuch you are so good

  57. Archana Topwal says:

    Thanks a lot..

  58. Quixote says:

    Are you kidding with this?

    Even in the 2010 and 2013 versions(this article written in 2014) the Get & Transform Add-In was available. Thankfully, it is now a native feature in 2016.

    Go to the Data Tab
    Click on From Table
    Highlight your column in the visual editor
    Right click the header
    Fill - > Down

    That's it.

    You need to delete this article. This was a poor workaround back then. Stop using this program in the same ways you were back in 2003.

  59. Srinivas says:


  60. Maryam says:

    You saved me a lot of time, Thank you so much!

  61. Ash says:

    Thank you so much, you wont believe how complicated other sites have made this, I didnt even know there was such a simple way to do this. You've made my day! Thanks so much!

  62. Y Murali Krishnan says:

    I understood the solutions above very much. But Please tell me any method to fill a cell with 0(zero) only if the cell above is a non-blank cell (even if i have multiple cell below the non-blank cell, i want to fill only one cell below the non-blank cell

  63. Homer Vargas says:

    Kudos for this post, excellent work

  64. Joss Thomas says:

    Thanks. This information is very informative.

  65. GB says:

    Just to say thank you. Appreciated!!

  66. Shaf says:

    i tried this but it didn't work.. instead of filling with the above cell, I want it to fill with the below cell.. and the total number of blank cells in between is not the same.. does this only work when the blank cells in between is the same amount? I.e. for the example above, the blank cells under Box and Envelope is the same..

  67. MUNIR KHAN says:

    Thanks. This information is very informative.

  68. Suresh Satyavarapu says:

    This is what I need. Thanks

  69. Anser Mehmood says:

    Thanks A lot for this tutorial. This is exactly the same what I was looking for. Thanks

  70. Pratyush says:

    Thank you.Just what I needed

  71. Makesh S says:

    This made my life simple . enormous time is saved .

  72. Makesh S says:

    This made my life simple . enormous time is saved .

  73. Phil says:

    thank you!!!

  74. Pablo Ramon says:

    You have saved me a lot of trouble. Thank you.

  75. Garry says:

    Thanks for this...I have an issue though with line breaks. I have a spreadsheet of data that is filtered so for example, lines 10-15 might be hidden from my desired results - I want an 'N' in every cell from 8 down, but as cells 10-15 default to a '0', making everything after call 15 also a '0' - can page/line breaks be discounted?

    I would usually Ctrl,Shift and arrow down to highlight all blank cells I need an 'N' in but I have many breaks with the document being over 40000 lines big.

  76. Shazal says:


  77. Mahendran Palanichamy says:

    Thanks a lot

  78. R. Jonny says:

    Amazing tips

  79. Fred says:

    Thanks for the very helpful tip! - just wish I'd looked for it before I started, instead of halfway through my task

  80. Girija says:

    This helps.. thankyou so so much

  81. Erin says:

    This is a huge fix for me. It's something I've been struggling with for years, I am so happy I found this today!!! It's saving me hours of work.

  82. CMOI says:


  83. Santosh says:

    Amazing & Excellent, got the precise information, & helped me in working on MIS, Thank you.

  84. sabir says:

    Thank you very much

  85. Bindhu says:

    Hi Ekaterina, the instructions are very clear and useful to , however I have a question I selected the rows with blank cell, however by using GO to special option I am not able to select blank cells . I get a prompt message there are no blank cells! What could be the probably reason how can i fix this?

  86. Praful says:


  87. Rajesh Dhokte says:

    Thank for useful and easy to understand help.....

  88. Mrs. Lohita Tatineni says:

    This formula is amazing. Thank a ton

  89. Sathish says:

    It's really nice and very easy,'

  90. LB says:

    I followed the steps and it did not select all the blank cells. Only the first blank cell was selected. Does this work with a certain version of excel? Does the data need to be formatted a certain way?

  91. ST says:

    Very Nice.... Thanks!!!

  92. Trevor B says:

    Woah woah woah, all of these are way harder than this is. Also these examples show trying to replace blanks with above or below text. Which is not realistic. Typically 0 = #N/A.
    1- New Sheet
    2-Place any text under the last row of data in the first sheet. Ex: 100 rows put text place "123" under all columns in row 101.
    3-Create a formula IFERROR(='sheetname'$A2,"iftruestatement", "iffalsestatement") copy and paste all the way down first column now drag to the right however many columns you have
    4-crtl+a or select all cells and re paste as plain text and delete row 101.

  93. Jake says:

    This has been really helpful thank you. I'm having an issue though where I'm trying to copy the value in the cell above but once I've highlighted the empty cells, as per the instructions above, when I press F2 or click in the formula box the active cell is always on the 1st row so there is no cell above that I can select in order to complete the formula. If I select a cell in the correct area it un-highlights all of the highlighted empty cells and so then I go through the process again of highlighting the empty cells and when it's time to enter the formula it selects a cell on the 1st row again. Is there any way to get around this? I need it to select any cell on row 2 or below. Thanks!

  94. MG says:

    Thank You So Much

  95. VM says:

    thanks a lot!!!

  96. Davi Jamelli says:

    Thank you!

  97. Paula Murray says:


  98. Nelly says:

    Extremely helpfull! thank you very much

  99. Paulo says:

    Thanks Man, but one more issue.. when I already selected the cells need to be filled with the upper or lower data, it says "no cells found".. please help me with this.

    • Paulo says:

      I have found a solution for my problem :D
      simply select the column you editing then format to "general" -
      go to DATA - Text to Column - (next- next - (tick "text")finish)
      then you can do the "go to special" fucntion without facing "no cells were found"

  100. Rajasekhar reddy says:

    superbr very helpfull it saved my 6 hors work

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)