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 4. Total comments: 201

  1. Thank you so much....amazing short cut.....

  2. Wow! Thank you so much. You saved a lot of time for me! ;)

  3. Saved me a lot of time. Brilliant, thank you!

  4. Hurrah! After all I got a weeb site from where I be able to in fact take valuable facts regarding my study and
    knowledge.

  5. Thanks for this, extremely time saving method, works perfect!!

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

  7. Thanks a lot, great shortcut!

  8. Thank you.

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

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

  11. how can fill slush in blank cell

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

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

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

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

  16. This worked great for me!!!

  17. superbr very helpfull it saved my 6 hors work

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

  19. Extremely helpfull! thank you very much

  20. THANK YOU ABLEBITS!

  21. Thank you!

  22. thanks a lot!!!

  23. Thank You So Much

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

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

  26. Very Nice.... Thanks!!!

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

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

  29. This formula is amazing. Thank a ton

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

  31. Thanks

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

  33. Thank you very much

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

  35. UN TOUT GRAND MERCI!

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

  37. This helps.. thankyou so so much

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

  39. Amazing tips

  40. Thanks a lot

  41. Thanks!

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

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

  44. thank you!!!

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

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

  47. Thank you.Just what I needed

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

  49. This is what I need. Thanks

  50. Thanks. This information is very informative.

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