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

  1. Thnks somuch you are so good

  2. Thanks for the fantastic tip

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

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

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

  6. This is excellent!

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

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

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

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

  10. thank you

  11. Thank You lot, i got what expected exactly

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

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

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

  15. THANKS ALOT. YOU ARE GREATE .

  16. This is extremely helpful!

  17. Thanks a lot. It saved lot of work

  18. Speechless!..thanks so much!

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

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

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

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

    jone

    daid

  23. Thank for such valueable post

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

  25. Very helpful very good much help ful love it

  26. Thank you so much!

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

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

  29. How do I fill up?

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

  31. this is great, but how we can apply for date , i got below type answer, kindly solve
    06/04/2015
    06/04/2015
    06/04/2015
    42100
    06/04/2015
    42100
    06/04/2015
    42100
    06/04/2015
    42100
    06/04/2015
    42100
    06/04/2015
    06/04/2015
    07/04/2015
    42101
    07/04/2015
    07/04/2015
    42101
    07/04/2015
    07/04/2015

  32. you are my hero! ..... thaaaaaaaanks

  33. Thank you very very much.

  34. Has anyone ran into the issues of hitting "crtl enter" and it doe not autofill the cells? I have ran this exact formula before but today it is not working?

  35. You are amazing man.

    You don't know that you have saved my 3 days of work and I have done that work in 5 minutes. Great job man.. God bless you.

    :-)

  36. i need formula

    2.254+152.00

    plz mere ko point ke above wali value ko hatane ka formula chayiye

    1. It is too late. However, try rounding off values. Point ke above wali value hat jaayenge.

  37. Thank you very much. You just saved me many hours

  38. This was a huge help! Thank you!

  39. Thanks a lot you made my day

  40. It's the perfect one what im finding for and got it. Thanks

  41. Find and Replace. Genius shortcut for what I needed. Thanks

  42. The Best Option...

  43. This worked, thanks!

  44. Thank you so much. Fantastic.

  45. thank you so much you made my day

  46. I understand Good

  47. I press f2 on my mac when trying to replace the empty numbers with zeros and it only leads me to brighten my screen.

    1. Hello Maria,
      Most likely you need to press the fn key at the same time, i.e. use the keyboard combination fn + F2.

  48. When I do this method of the '=b2' it simply enters b2 all the way down. What did I do wrong?

    1. You must be directly entering the command by "Enter" command, make sure you are using "Cntrl+Enter" at the same time, your issue will be solved

    2. Hi,

      I know its a bit late after the fact but the issue is your column must be in 'text' format, same thing happened to me I changed it to 'General' and the above steps worked correctly.

  49. You just made my day!!

    Now I can fill all those blank cells and make the Pivot Table work with "sum" instead of "count" which is so annoying when working with large files...

    Thank you so much!

  50. Thank you Sweety! :)

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