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

  1. Thanks!

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

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

  4. thank you!!!

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

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

  7. Thank you.Just what I needed

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

  9. This is what I need. Thanks

  10. Thanks. This information is very informative.

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

  12. Just to say thank you. Appreciated!!

  13. Thanks. This information is very informative.

  14. Kudos for this post, excellent work

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

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

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

  18. Thanks

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

  20. Thanks a lot..

  21. Thnks somuch you are so good

  22. Thanks for the fantastic tip

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

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

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

  26. This is excellent!

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

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

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

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

  30. thank you

  31. Thank You lot, i got what expected exactly

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

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

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

  35. THANKS ALOT. YOU ARE GREATE .

  36. This is extremely helpful!

  37. Thanks a lot. It saved lot of work

  38. Speechless!..thanks so much!

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

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

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

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

    jone

    daid

  43. Thank for such valueable post

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

  45. Very helpful very good much help ful love it

  46. Thank you so much!

  47. Hi,

    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.

  48. Thanks... It saved me lot of time

  49. How do I fill up?

  50. @ashwith
    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:"

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