Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Delete Blank Rows in Excel [Quick Tip]

Posted on January 26th, 2010 in Learn Excel - 118 comments

Delete Blank Rows in Excel

Go to Special - Select BlanksBlank rows or Blank cells is a problem we all inherit one time or another. This is very common when you try to import data from somewhere else (like a text file or a CSV file). Today we will learn a very simple trick to delete blank rows from excel spreadsheets.

  • Select your data
  • Press F5
    This opens “Go to” dialog in Excel. Now hit on that “select” button.
  • From “select special” screen, select “Blanks” (shown aside)
    Now, all the blank cells will be selected.
  • Just press CTRL and Minus sign (-)
  • Select “shift cells up” or “entire row” as needed.

That is all. Now you have successfully removed blank rows.

Bonus tip:

If you are looking for keyboard short-cut for this, here it is. Press them in the same order once you select the cells.

  • F5 ALT+s k Enter CTRL+ – u Enter

Remove Blank Rows in Excel – Video

Here is a short video showing this in action. Watch closely and get rid of those annoying blank cells.

(watch it on youtube)

Browse more quick tips.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

118 Responses to “Delete Blank Rows in Excel [Quick Tip]”

  1. Cornelius says:

    I was doing this with VBA, but this one is much easier and faster.

    There is one thing you should be careful about. If there are some missing values in whichever column they will be deleted as well so the data will displace and probably would not match with another column(s).

    • James says:

      Is there a way to avoid the displacement you mentioned? Such as a feature to select the rows that are empty across?

      • Mr Excelie says:

        Yes. Add filters to the columns. Order the column desired and you will have the blanks at top. Delete them. Voilà!

  2. Harvey says:

    Surprised, this works exactly the same in Mac Office 2008. Even the keystrokes are the same. Unbelievable. Thanks.

  3. Glen Feechan says:

    Another great tip, so simple. When I saw it, I thought “I bet that only works in 2007″ but it is exactly the same in 2003. You learn something every day.

  4. winston says:

    Check out the VBA (Macro) approach from Chip Pearson
    http://www.cpearson.com/Excel/deleting.htm#DeleteBlankRows

    Copy/paste into module in your personal.xls workbook and make always available when working with Excel

    I use this all day long

    Regards,
    w

  5. Rick Rothstein (MVP - Excel) says:

    Another way to select all the blank cells is to press Ctrl+F; delete all the text, if any, in the “Find what” field; press Alt+i (or click Find All), then press Ctr+A, and then press the Esc key (or click the Close button)… all the empty cells will now be selected… follow the remainder of the instructions from the blog.

    • Steve says:

      Thanks for this, I tried this method because the one above didnt work as I was using data that had been copied and paste value. The above method was not recognising the empty cells as blanks. Your method was able to identify the all empty cells and I was able to delete them.

    • Baz says:

      This way is much better, thank you.

  6. Boscom says:

    Thanks PHD for this and other posts….helping make us take small steps to becoming excel mini-Gurus at our work places! Will certainly share this…

  7. John McEwan says:

    Now, how do you find and delete the cells that return the null string (“”)?

  8. Dan Murray says:

    This is a nice trick, but I’m too old to remember function keys and ctrl minus key combinations. So, I created another way by making a custom toolbar. By creating a custom toolbar with icons that activate these same commands.

    I use Excel 2007 so these instructions only apply to that version. PHD can cover this in more detail (he probably already has).

    To create a custom toolbar click on the Excel ball in the upper left side of the menu. Click the Excel Options button in the lower right side of the dialog box. Then click Customize (the 5th item down the menu list). No you have the available options for adding to your custom menu.

    Change the Choose Commands from drop down list to “All Commands” then scroll down to the “Delete Sheet Rows” and add it to the list. Continue scrolling down till you find “Go To Special…..” and add that. Hit the OK button. You should now have a customized toolbar at your disposal to invoke the same commands PHD did with function keys and ctrl minus key combinations.

    I do a lot of work with large tables that contain gaps. This is a very nice little trick Chandoo. Thanks for posting it.

  9. Chandoo says:

    @Cornelius: Yes, we should be careful when deleting cells / rows using this method.

    @Harvey: Thanks, I didnt know Mac Excel had such good compatibility.

    @Glen: Thanks for the compliments. Also thanks for blogging about this and linking :)

    @Winston: good one, thanks for the pointer

    @Rick: That is a good find (pun intended).

    @Boscom: I am glad you like this…

    @John: You can use “Find” to find all the values that are empty (even if they are results of formulas). Select the cells, Press CTRL+F, clear any find text, click on “options” and select “look in values”. Just hit “Find all”. Now excel finds all cells that have empty values. Now, select all the matches from find list (use shift to select) and then close the find box. Delete the cells using CTRL+-

    @Dan: Good idea to use Quick Access Toolbar to launch Go to Special.

    • PETER F says:

      Brilliant explanation Chandoo. All the other suggestions were not working as i had values in the cells. I appreciate all the other ideas which led to your explanation. Thanks

  10. Rick Rothstein (MVP - Excel) says:

    @Chandoo and John

    In Chandoo’s comment to John about selecting empty strings, his advice after clicking the “Find All” button was “Now, select all the matches from find list (use shift to select)”… you do not have to do the selections one-at-a-time… you can select all the found cells all at once by pressing Ctrl+A.

  11. Rick Rothstein (MVP - Excel) says:

    As Cornelius warns, Chandoo’s method works only if the non-blank rows are completely filled (that is, there are no blank cells in the rows that have data in them). I would also note that cells with formulas evaluating to the empty string are considered non-blank and will not be deleted; so, like Cornelius’ warning, these cells will not be deleted while the blank (non-formula) cells next to them will. With these warnings in mind, and for those who are interested in such things, the VB equivalent to Chandoo’s method is a simple one-liner…

    Sub DeleteTheBlankRows()
    Cells.SpecialCells(xlCellTypeBlanks).Delete xlUp
    End Sub

  12. Rick Rothstein (MVP - Excel) says:

    @Winston… I think the following, much shorter, macro does the same thing as Chip Pearson’s code does…
    .
    Sub DeleteBlankRows()
    Dim X As Long, U As Range
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    With ActiveSheet
    For X = 1 To .UsedRange.Rows.Count
    If .UsedRange.Rows(X).SpecialCells(xlCellTypeBlanks). _
    Count = .UsedRange.Columns.Count Then
    If U Is Nothing Then
    Set U = Rows(X)
    Else
    Set U = Union(U, Rows(X))
    End If
    If U.Areas.Count > 100 Then
    U.Delete xlShiftUp
    Set U = Nothing
    End If
    End If
    Next
    End With
    U.Delete xlUp
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End Sub

  13. Rick Rothstein (MVP - Excel) says:

    @Winston… I copied the wrong code in my previous post; there is a single line that is not correct, but I am posting all the code to make it easier to copy/paste it…
    .
    Sub DeleteBlankRows()
    Dim X As Long, U As Range
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    With ActiveSheet
    For X = 1 To .UsedRange.Rows.Count
    If .UsedRange.Rows(X).SpecialCells(xlCellTypeBlanks). _
    Count = .UsedRange.Columns.Count Then
    If U Is Nothing Then
    Set U = Rows(X)
    Else
    Set U = Union(U, Rows(X))
    End If
    If U.Areas.Count > 100 Then
    U.Delete xlShiftUp
    Set U = Nothing
    End If
    End If
    Next
    End With
    If Not U Is Nothing Then U.Delete xlShiftUp
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End Sub

  14. Wendell says:

    When I want to remove blank rows of data. I just select the data and press sort Z>A. What are the limitations of that way?

  15. Rick Rothstein (MVP - Excel) says:

    @Wendell… That will work, of course, but it does have the side effect of leaving the data “out of order” which may or may not be a problem. If the existing data is in order with internal blank lines, then if there is a naturally sequential column (such as an “data entered date” or sequential “order number”), the data can be resorted on this column to put the data back into its original order. Otherwise, you can create a sequentially indexed column, sort the data Z-to-A according to one of the existing columns, delete the index numbers next to the blank rows them, and then sort the data A-to-Z on the index column to put the data back in the original order; but, of course, this involves (a small amount of) extra work to do.

  16. Chandoo says:

    @Rick… awesome discussion, thanks for your knowledge and passion. :)

  17. Chris says:

    I have been inserting a column and using edit, fill, series to get sequence numbers. Then sorting on the column containing blanks and deleting rows. It is quite fast but Chandoo’s idea sounds simpler.

    When I know I am going to do a lot of crazy sorting I sometimes add a sequence column just to get back to my original order.

    Many great tips – thank you!

  18. [...] the problem of actually removing various blank cells. First I tried to select all the blank cells and remove them using our technique from last week. But it failed as the blank cells are actually formulas with empty values. So I copy pasted the [...]

  19. Abdul Kader says:

    Always Brilliant,but be careful about deleting some needed data as
    Cornelius commented
    Regards

  20. kishore says:

    This Tip is awesome.. :)

  21. night-bird says:

    Amazing!!! Thank you so very much!!!

  22. Jared says:

    To Rick Rothstein (MVP – Excel):
    This was the perfect trick for what I needed to do. Thanks

  23. Rick Rothstein (MVP - Excel) says:

    @Jared,

    I presume you are referring to my method of selecting all the blank cells… you are quite welcome… I am glad you were able to make use of it.

  24. Scott Randell says:

    This is very helpful, but on my Mac with Excel 2008, it crashes (wheel spinning and “not responding” on Force Quit) every time I hit the control-. I am trying to delete blanks in columns of data that are a couple of thousand rows long though!

  25. Kevin MacDonald says:

    I tried this method but excel can’t seem to find an of the blank cells in the range i selected. There is nothing in them but when i click on them and then press delete, excel can find them. This defeats the purpose of the go to method because i have to click on every cell individually. Anyone have any ideas?

  26. Sweta says:

    Hi this method helped me. Thanks a lot!

  27. Thomas says:

    Great information. I could not get it to work at first. It took me a long time to figure out even though my cells were blank, they had some sort of value when I imported the file.

    So I copied all the cells then pasted as text into another spreadsheet. Then i was able to select all the blank cells and move everything over to the left.

  28. Jim says:

    This is not working for me. The rows are still there. Just blank now. CRTL x, CTRL -, nothing works.

  29. Ben says:

    Thanks.

    This worked perfectly and saved me a lot of work in both looking for a solution and performing the work!

  30. mike stein says:

    coooooooooooool saved me a LOT OF WORK
    thanks again!

  31. Tom says:

    Worked great. Many thanks.

    Certainly saved a lot of time for me!

  32. The Word Of A King says:

    Thought about trying to figure this out by myself then thought it would be a waste of time if someone already figured it out for me. Appreciate you taking the time to post this so we could glean from you!

  33. Bhishm Dhawan says:

    Thanks for the demo, this was very helpful. This is certainly a time saver tip. :) I appreciate it.

  34. sasisalon says:

    This work great, thanks for good example. I tried to find the way to remove empty cell like this and your tip is great. Thank you.

  35. Christian says:

    Thank you soooooooooo much worked, just have to qc the data to see if the values were copied correctly.

    thanks again

  36. BT says:

    I should stop Googling questions about Excel and just head straight to Chandoo.org!

  37. Parag says:

    Thanks, You rock!!!

  38. maher says:

    thanks you saved my day and time

  39. evelynmorry says:

    I follow the steps, and it works. Wonderful tip!

    But i wonder if there is a faster way to delete all the blank rows or not, and then i found that some excel applications can deal with this issue with only one click. The one I try now is Kutools for Excel, it is an excel add-ins collection and provides a function to delete all empty rows just by clicking on the function button. Very simple to use.

    Include but not limit to the delete blank rows function, Kutools for Excel can help deal with some repetitive work, such as rename multiple sheets, sort sheets , and so on.

  40. Hui... says:

    @Evelynmorry
    There are a number of tools which do exactly that
    You should inform readers that you work for ExtendOffice or risk ending up in the spam bin.

  41. David says:

    Thank you, appreciate the clearly written directions.

  42. dimas says:

    haha…
    this is cool :D

    Thanks for sharing mate ;)

  43. Hasmah says:

    Thanks so much!! this is a great help.
    I had done a row deleting the blanks manually which really waste time.
    This can be done in mere seconds :)
    Thanks a lot!!!!! :)

  44. jm says:

    Thanks so much!!!!!!!!!! It’s been really really useful!!

  45. Prachi says:

    Thanks! it worked :)

  46. Wes says:

    This was an awsome tip! Can’t get nearly as good help from Microsoft’s help menu!

  47. SHIV says:

    Thanks mate, you just saved me from deleting so many cells!!!
    simple but productive tip… :)

  48. mbcmac says:

    I have read many of these comments, I tried deleting with this method, but I only want to delete blank rows – not blank cells. As stated we must be careful all the data shifted up and was not readable. I used to know a way to delete only blank rows and can’t remember now. I will come back for more tips that may help in other areas. Thanks

  49. Sham says:

    Hi
    The post is helpful. However, I need to delete empty rows from multiple worksheet in a single file. I would have done it manually if I had one excel file. However, I have around 200 excel files with almost 100 sheets in a file. I appreciate if anyone could help me with VB code or a general way to save time.

  50. PostMortem says:

    Maybe i’m confused, but why wouldn’t you just custom “sort” them out instead of deleting or going through all those steps? I can understand if you want to remove/delete blank cells, but that messes with the data when there are values surrounding the blank cell. To “remove” (put blank rows at the bottow), couldn’t you just use a custom sort?

    All this information here is very valuable, but some are overkill for the topic at hand… aren’t they? Maybe it is just me.

    Good luck with your data crunching!

  51. D S Rajpurohit says:

    Thanks a lot

  52. LJ says:

    Nice tips! There’s always a way out of every challenge, either in Excel or in the real world. Thanks a lot!

  53. BDO. Galsi-I says:

    Thank you so much….it has proven to be of great help.

  54. YES! You saved my day! I was afraid I had to delte 1500 blank cells my self.

    Your karma has improved today :-)

  55. Mohd Salman says:

    Thanks a lot… it was a great help

  56. William Brody says:

    Fantastic! Very simple (and correct!) instructions. Thank you!

  57. Bobmo says:

    Thank you, Chandoo! You’re a genius! :-)

  58. arunaji says:

    awesome…. thank you so much. It saved lot of my time

  59. [...] Delete Blank Rows in Excel [Quick Tip] posted on January 26th, 2010 in Learn Excel [...]

  60. Deepak Gopalakrishnan says:

    Thank you :-D

  61. Shane says:

    Its not working for me. Is there a key you have to hit f5 with after you’ve highlighted the data?

  62. buffaloshark says:

    I just needed to delete a bunch of blank rows and found this site. Some of my filled in rows had one or two empty cells causing the problem of losing rows I actually needed.
    I found a fix. Instead of just hitting F5 so that the whole worksheet is selected click on one of the columns and then do F5 and select blanks. This results in only blanks in that column being selected. Then ctrl – and delete row works. Just have to have at least 1 column that is completely full

  63. Thanks !! a lot , This is very useful formula for excel users

  64. Keith says:

    Very useful. Thank you. :)

  65. MOhd Asif says:

    Hey Bro,
    Thank you very much,,
    very use ful for me.. You have given the info very cleary.

  66. Robert says:

    One limitation I found was that if you have a formula such as “=if(a1=1,true,””)” in a cell, then paste values, if the formula previously resulted in “”, excel does not consider that cell blank.  I was having difficult deleting these cells. 

    The roundabout way i found around this was to conditional format the entire group of cells with any CF, then “Go To – Special – column differences” and clear rules from selected cells. Then “Go To - Special – Conditional format” and you should have your “blank” cells.

  67. MG says:

    Works a treat, thank you very much for blogging this! :D

  68. Nirai says:

    Awesome dudeee. Saved me an hour work :)

  69. hloom says:

    Awesome tip. Saved a lot of time, thanks a lot.

  70. Arunkumar M says:

    i want to Delete a row by comparing two cells,if two cells are blank then delete that entire row.

  71. Christian says:

    You just saved me a shitload of work mate thank you!

  72. Great help! Saved me tonnes of time. Was faffing with filters before!

  73. Andrey says:

    Thank you!!!

  74. Danise says:

    What an awesome thing to know — this will save me a lot of time!

  75. Sreejith says:

    Tip is awesome!!Thanks for sharing..

  76. eko412 says:

    Hi all, i’m doing this “blankcellminator” but my data is just to big
    any idea how to do the “blankcellminator”? besides manually selected the area?

  77. Chetan says:

    Excellent !!! THank you verymuch for publishing this… I had been struggling with this from long time. 

  78. Srikanth Reddy says:

    Thanks for sharing the useful info..

  79. Thomas says:

    BRILLIANT!!! Thank you so much!

  80. Ashwani says:

    Great Tip. Thanks

  81. dave says:

    Guys, hard problem, need help
    I must report all the cells from a previous sheet that respect a particular condition, otherwise consider the cell like blank. the problem is that this must be automatic, in the sense that i need that this operation devolps every time with different records sheet. the problem is that excel, in the second sheet, consider the blank cells which are the result of the conditional formulas non like blank cells, and in fact when i try to order the numbers obtained like result, excel orders the blank cells before the numeric cells. I can not delete the formulas in the blank cells because it must be able to operate with different sheet record time after time (lenght can vary).
    question: How i can get blank cells like result from a formula that are considered not like number by excel and therefore ordered after numeric cells if sorted?

  82. julien says:

    Very helpfull, thanks a lot. If I have to delete all rows with “John” in it. Do you know how to do it? Thank you

  83. Gordon says:

    The Problem I see with several versions of Excel, but especially 2010 and above: Excel somehow places thousands of cells in range. I have data in columns A:F, but if I select all (A1 + Ctl+Shft+Down), it takes me over as far as column WWW. This is so many cells that Excel freezes and I have to restart.  I need a way to delete those blank columns that does not upset Excel.  Anyone else have that problem?

  84. Kasun says:

    Thank you very much.

  85. Peta says:

    Chandoo – thank you, thank you, thank you! I have just had to redo a whole heap of data analysis, due to a botched custom sort, this has saved me a truck-load of tedium – your a star!

  86. chris says:

    super helpful! saved me so much time!

  87. Esha says:

    Thanks you are a genius.. :-)

  88. Ned says:

    This is awesome. Thanks so much.

    Ned

  89. aurelia says:

    Hi Chendoo,

    Your post is a very good piece of information.
    However, could you please help me with another issue?
    In Excel 2007 I have made a macro selecting the entire column. The selection has written information in 65000 rows (the file was saved at that time using Excel 2003). Regardless of my erasing the data from the rows 1000 to 65000, the cells remain “active” somehow, and that makes the file huge. Unfortunately I can not CUT and PASTE the information in another sheet due to the fact that I have a lot of formulae linked to the information from this sheet.
    Do you have an idea on how I can get rid of the unused rows?

    Thank you!
    Aurelia

  90. Aurelia says:

    Hi Hui,

    Thank you very much for your reply.
    It did work with selecting and deleting the rows, but the result (the decrease in file’s size) was visible only after saving the file. :)

    Also, thank you very much for the links, as they are extremely useful for what I am doing right now.

    All the best,
    Aurelia

    • Hui... says:

      @Aurelia
      Thats great news
      Yes, the File will only change size when it is re-saved
      Until it is saved you can cancel any and all changes by not saving it

  91. Lisa Toney says:

    YEAH! Different ver of Excel but worked WONDERFUL! THANK YOU!

  92. Anonymous says:

    Superb Tip!! Made life easier in a Jiffy. Thanks a lot

  93. Nawaaz says:

    Thanks very much for this , made my job easier:) I’m glad i’m the 100th commenter :)

  94. Harimau says:

    Not sure if anyone’s mentioned this method, it uses the “Sort Z->A” method without (ultimately) breaking the order:

    1. In the column next to the data column (including blanks), type “1″, “2″, select those two cells, and then fill the remaining cells in the column for the data (drag the corner down to the end of the data), so that it fills the column with 1, 2, 3, 4, …

    2. Select the data column, sort reverse-alphabetically from Z->A (when asked, “Expand the selection”), thus placing the empty data rows below the filled data rows (but breaking the order).

    3. Either delete the blank data rows or copy the filled data rows (including the numbered column) to another sheet.

    4. Select the numbered column, sort from smallest to largest (when asked, “Expand the selection”), thus restoring the original order.

    5. Delete the numbered column.

    This approach is good and quick (and I don’t think it cares about whether a cell is truly empty or not) as long as you don’t mind navigating to the end of the data.

  95. Divya says:

    Thanks a ton!!

  96. […] enregistrer a nouvelle commande. Collez simplement le bout de code ci-dessous (que j'avais trouvé ici) puis fermez la fenêtre […]

  97. […] copy both columns to sheet 2 and: Delete Blank Rows in Excel, Remove Blank Cells in Excel | Chandoo.org – Learn Microsoft Excel Online You can do it with the macro recorder on and will get your […]

  98. […] might try: 1. Copy sheet 1 all data to each of the other sheets and: Delete Blank Rows in Excel, Remove Blank Cells in Excel | Chandoo.org – Learn Microsoft Excel Online Finally sort time […]

  99. Shadowed IN says:

    thanks man… good job!

  100. b mill says:

    AWESOME!! Thank you.

  101. […] I imported the text file into Excel and deleted the empty rows by using the following instruction: […]

  102. sirisha says:

    thanks .

  103. d baah says:

    thanks cuhh

Leave a Reply