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

  1. Thank you!

  2. thanks a lot!!!

  3. Thank You So Much

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

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

  6. Very Nice.... Thanks!!!

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

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

  9. This formula is amazing. Thank a ton

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

  11. Thanks

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

  13. Thank you very much

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

  15. UN TOUT GRAND MERCI!

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

  17. This helps.. thankyou so so much

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

  19. Amazing tips

  20. Thanks a lot

  21. Thanks!

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

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

  24. thank you!!!

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

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

  27. Thank you.Just what I needed

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

  29. This is what I need. Thanks

  30. Thanks. This information is very informative.

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

  32. Just to say thank you. Appreciated!!

  33. Thanks. This information is very informative.

  34. Kudos for this post, excellent work

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

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

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

  38. Thanks

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

  40. Thanks a lot..

  41. Thnks somuch you are so good

  42. Thanks for the fantastic tip

  43. 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:

    DLM0509
    DLM0509
    DLM0509
    DLM0509
    DLM0509
    DLS0074
    DLS0074
    DLS0074
    DLS0074
    DLS1491
    DLS1491
    DLS1491
    DLA4254
    DLS1483
    DLS1483
    DLS1483
    DLS1483
    DLS1483
    DLS1483
    DLS1483
    DLA3341
    DLA3341
    DLA3341

    1. Hello, Udayan,

      I'm afraid there is no way to get the result you need using a formula. You'd better use a macro. I'm sorry, we can't help you with this, please try to find the solution in VBA sections on mrexcel.com or excelforum.com.

  44. 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:
    DLM0509
    DLM0509
    DLM0509
    DLM0509
    DLM0509
    DLS0074
    DLS0074
    DLS0074
    DLS0074
    DLS1491
    DLS1491
    DLS1491
    DLA4254
    DLS1483
    DLS1483
    DLS1483
    DLS1483
    DLS1483
    DLS1483
    DLS1483
    DLA3341

    Please help!!

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

  46. This is excellent!

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

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

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

  49. Thanks, easy instructions to follow - great help.

  50. thank you

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