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 →

Table of contents

Comments page 3. Total comments: 201

  1. I work in the financial services and you lot just saved me my job!!! Thank youuuuuu :)

  2. Thanks . Many Many help us jillal

  3. Thanks . Many Many help us.

  4. That's gr8!

  5. Thank you so much!

  6. Really helpful! Thanks so much!!

  7. Thats amazing! You helped me save so much time.
    Thanks a lot for sharing the wonderful tips!

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

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

  9. THANK YOU SO MUCH..Saved me lots of time. God Bless!

  10. Learnt something new today.. thanks a lot

  11. What a great piece! Thanks a lot.

  12. Its very helpful.Thank you so much for this detailed explanation

  13. Thank you for the clear concise instructions!

  14. Do you know if there's any way to do this in google sheets?
    Thanks!

  15. thank you so much - amazing tips..

  16. Grt!

  17. Great tip! Thanks a lot :)

  18. Thank you very much sir, it saved me lot of time and tension

  19. EXCELLENT SIR, I AM APPLY THIS FORMULA THIS IS WORKING AND HELP FULL. THANK YOU ONCE AGAIN SIR

    1. You mean Miss, not Sir. Why would you assume the writer is a man? You are talking to an excel Queen here.

  20. thank you for these :)

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

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

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

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

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

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

  27. Thanks a lot, great shortcut!

  28. Thank you.

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

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

  31. how can fill slush in blank cell

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

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

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

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

  36. This worked great for me!!!

  37. superbr very helpfull it saved my 6 hors work

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

  39. Extremely helpfull! thank you very much

  40. THANK YOU ABLEBITS!

  41. Thank you!

  42. thanks a lot!!!

  43. Thank You So Much

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

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

  46. Very Nice.... Thanks!!!

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

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

  49. This formula is amazing. Thank a ton

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

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