Comments on: How to remove all blank rows in Excel

In this quick tip I will explain why deleting Excel rows via select blank cells -> delete row is not a good idea and show you 3 quick and correct ways to remove blank rows without destroying your data. All solutions work in Excel 2016, 2013 and lower. Continue reading →

Comments page 2. Total comments: 129

  1. the post is really very helpful for people who are searching for a quick resolution to empty data row issue....

  2. Again, only in case I'm not the only one. Here's what works for me:
    1) Save a copy because the following process will delete the header row and you'll need it back.
    2) Click in Cell A1.
    3) Click Cmd+Shift+End to select to the end of used cells.
    4) Go to Data and click on Filter
    5) There's now a drop down arrow in every column. Click on the one at A1.
    6) Everything you do in this dialog box kind of only happens to the items you can see, believe it or not. So go to the lower right of the dialog box and stretch it down as far as you can.
    7) Uncheck "Select All." This may or may not actually deselect all. Scroll down to see if any other items are still selected. If so, try clicking on it. It selects that one and probably deselects many other items but not necessarily all of them. The taller the dialog box, the better your chances of getting most of them. Repeat this process until you truly have nothing selected.
    8) Scroll down to the bottom and check "(Blanks)". Close the dialog box by clicking outside it anywhere.
    9) Go to Edit. What used to say "Delete" now says "Delete Row". Click it. This is such a big deal to Microsoft that there'll be a warning box. Click that, too. At this point, you may notice that your header row is gone.
    10) Click to the left of Row 1 and on the Home tab, click Insert to make a blank header row.
    11) Open the copy you made in Step 1 and copy Row 1 to the clipboard.
    12) Paste that into your new Row 1. Save.

  3. I'm only posting this so others won't think they're crazy. I've seen the Filter/ (Blanks) method other places online and I'm sorry but it doesn't work for me since there's no "OK" button to click. I follow along up to that point and the only button in that dialog box is "Clear Fllter." If that's what you're seeing too, poor beleaguered Excel user, you're not alone. I guess I'll have to remove hundreds of blank lines manually. Probably won't kill me.

  4. Thanks for your helpful tutorial here. Saved me a lot of time!

  5. Very good !!!!

  6. what a great! thank you so much for your helppppppppppppppp

  7. Thanks a lot

  8. THANK YOU! You're a lifesaver. Makes sanitizing listings as an auditor a breeze now.

  9. Actually, I just found a great, super quick way to get rid of extra rows...

    1) save the original Excel file as "tab-delimited" text file, then...
    2) open that file in notepad or TextEdit (Mac), then
    3) in the text app select the whole empty row (triple click on Mac)
    4) do a "find" in the text app, paste the empty-row and click "replace" with empty field (nothing)
    5) then just click "replace all" and boom! all those damn empty rows gone!

    Then just copy-paste back into Excel :)

    At any rate, I tried this on my Mac, and it worked like a charm, I'm not sure if Notepad on the PC has the same find-replace as the Mac's TextEdit, I assume it does, but if it doesnt then maybe you can try another basic word-processing app that allows simple find-and-replace

    Good luck!

  10. I have a list of names with 2 empty rows between after every second name on the list (i.e. 2 rowns with names and two blank rows). I want to delete only 1 empty row and leave the second empty row as it is. Any ideas?

  11. i my excel sheet there are 1600 rows.when i apply filter( by text or number).it is not applying to the entire sheet.it is applying only upto range of 50 or 100 rows.its becoming difficult to filter function apply again and again. ** my sheet has blank CELLS also**

  12. Thanks a lot for the tutorial.
    'Remove blank rows using a key column' was helpful.

  13. This has saved my life!!!! I was sent tons of spreadsheets with awful structure and now I won't have to spend this entire century trying to manipulate them... Thank you so much !

  14. Thanks that was perfect bro

  15. Perfect. I was one of the people trying the F5 method and pulling my hair out. This worked exactly as you said it would.
    Thanks a bunch for the help.

  16. thanks. worked perfectly. saved me some time

  17. Hi,

    the first two worked but I downloaded your quick tools add in but it is not recognizing the empty rows and once the tool runs, it shows 0 empty rows.

    can you help me in this.

    regards,

    rajeev

  18. Hi,

    the first two worked but I downloaded your quick tools add in but it is not recogniszing the empty rows and once the tool runs, it shows 0 empty rows.

    can you help me in this.

    regards,

    rajeev

  19. it worked perfectly, thank so much

  20. Brilliant!

  21. The 3rd option is what I tried and it works up to the point that I clear the filter and the screen seems to freeze. The data is still there but you can't seem to click on anything. I say "seems" because I found out that the program is still working like it should it's just that the screen doesn't refresh. I just click the X and save the work. When you return to the program it works correctly without the blank rows.

  22. Elegant! Thank you!

  23. Fantastic short cut and saves much time...thanks a lot and appreciated..

  24. Thanks

  25. Thank u great work

  26. very helpful.thanks a lot.

  27. Excellent post. Thanks a lot.

  28. Thank you. Very detailed explanation.

  29. Nice trustworthy tutorial over other faulty tricks shown on internet

  30. Didnt work for me. Tried it twice

  31. Thank you very much keep up the good work

  32. Very Helpful. Thank you.

  33. Oh my..this is so helpful. Thanks a lot!!!!

  34. Thanks A lot, it worked.
    You've just saved me an hour of dull work and taught me something new. Thanks again

  35. Option 3 works easy.Thankyou

  36. The only solution that works!Thank u man!

  37. Thank you :) Very Helpful

  38. Thank you :)

  39. Very useful tool, thanks a lot ,it is helpfull

  40. Very Nice post.

  41. Thanks :) it is very helpful

  42. Hello Team,

    Could you please hlep me to remove the blank columns(I have all header column names but no data). Here I have to remove no data columns(with name).

    Thanks in advance. :)

    LOkesh P

  43. Thanks for this informative tutorial

  44. Thank you very much saved a lot of time.

  45. Superb, especially with such a detail explaination.
    thx heaps :)

  46. Thank you!!

  47. Is there a way to remove blank rows by formulas instead of filtering?

  48. Thanks Alex, invaluable little tutorial, was having issues with the ubiquitous 'Go to Special >Blanks etc' that you refer to in your first paragraph. Nice to see a more considered approach :)

  49. It was very effective! Thanks a ton!! :)

  50. Thank you SOOOOOOOOOOO much. well described, great 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 :)