Comments on: 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. Continue reading →

Comments page 3. Total comments: 201

  1. how can fill slush in blank cell

  2. Thank you. helped me with thousand of records.

  3. Awesome... saved me alot of time. IT WORKS PERFECTLY

  4. I hope you work with Microsoft team of excel help to describe everything as you did here. Thank you

  5. Nice! and helped me with thousand of records. Thank you

  6. This worked great for me!!!

  7. superbr very helpfull it saved my 6 hors work

  8. 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.

    1. 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"

  9. Extremely helpfull! thank you very much

  10. THANK YOU ABLEBITS!

  11. Thank you!

  12. thanks a lot!!!

  13. Thank You So Much

  14. 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!

  15. 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.

  16. Very Nice.... Thanks!!!

  17. 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?

  18. It's really nice and very easy,'
    Thanks

  19. This formula is amazing. Thank a ton

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

  21. Thanks

  22. 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?

  23. Thank you very much

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

  25. UN TOUT GRAND MERCI!

  26. 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.

  27. This helps.. thankyou so so much

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

  29. Amazing tips

  30. Thanks a lot

  31. Thanks!

  32. 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.

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

  34. thank you!!!

  35. This made my life simple . enormous time is saved .

  36. This made my life simple . enormous time is saved .

  37. Thank you.Just what I needed

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

  39. This is what I need. Thanks

  40. Thanks. This information is very informative.

  41. 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..

  42. Just to say thank you. Appreciated!!

  43. Thanks. This information is very informative.

  44. Kudos for this post, excellent work

  45. 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

  46. 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!

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

  48. Thanks

  49. 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.

  50. Thanks a lot..

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