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 2021, 2019, 2016, and lower.

3 quick and correct ways to remove blank rows in Excel

If you are reading this article, then you, like me, are constantly working with large tables in Excel. You know that every so often blank rows appear in your worksheets, which prevents most built-in Excel table tools (sort, remove duplicates, subtotals etc.) from recognizing your data range correctly. So, every time you have to specify the boundaries of your table manually, otherwise you will get a wrong result and it would take hours and hours of your time to detect and correct those errors.

There may be various reasons why blank rows penetrate into your sheets - you've got an Excel workbook from another person, or as a result of exporting data from the corporate database, or because you removed data in unwanted rows manually. Anyway, if your goal is to remove all those empty lines to get a nice and clean table, follow the simple steps below.

Never remove blank rows by selecting blank cells

All over the Internet you can see the following tip to remove blank lines:

  • Highlight your data from the 1st to the last cell.
  • Press F5 to bring the "Go to" dialog.
  • In the dialog box click the Special… button.
  • In the "Go to special" dialog, choose "Blanks" radio button and click OK.
  • Right-click on any selected cell and select "Delete…".
  • In the "Delete" dialog box, choose "Entire row" and click Entire row.

This is a very bad way, use it only for simple tables with a couple of dozens of rows that fit within one screen, or better yet - do not use it at all. The main reason is that if a row with important data contains just one blank cell, the entire row will be deleted.

For example, we have a table of customers, 6 rows altogether. We want to remove rows 3 and 5 because they are empty.
We want to remove rows 3 and 5 because they are empty

Do as suggested above and you get the following:
Row 4 (Roger) is also gone because cell D4 in the "Traffic source" column is empty

Row 4 (Roger) is also gone because cell D4 in the "Traffic source" column is empty: (

If you have a small table, you will notice a loss of data, but in real tables with thousands of rows you can unconsciously delete dozens of good rows. If you are lucky, you will discover the loss in a few hours, restore your workbook from a backup, and will do the job again. What if you are not so lucky or you do not have a backup copy?

Further in this article I will show you 3 fast and reliable ways to remove empty rows from your Excel worksheets. If you want to save your time - go straight to the 3rd way.

Remove blank rows using a key column

This method works if there is a column in your table which helps to determine if it is an empty row or not (a key column). For example, it can be a customer ID or order number or something similar.

It is important to save the rows order, so we can't just sort the table by that column to move the blank rows to the bottom.

  1. Select the whole table, from the 1st to the last row (press Ctrl + Home, then press Ctrl + Shift + End).
    Select the whole table
  2. Add AutoFilter to the table: go to the Data tab and click the Filter button.
    Add AutoFilter to the Excel table
  3. Apply the filter to the "Cust #" column: click the arrow Autofilter drop-down arrow in the column header, uncheck the (Select All) checkbox, scroll down to the end of the list (in reality, the list is quite long) and check the checkbox (Blanks) at the very bottom of the list. Click OK.
    Excel Autofilter: show empty rows only
  4. Select all the filtered rows: Press Ctrl + Home, then press the down-arrow key to go to the first data row, then press Ctrl + Shift + End.
    Select all the filtered rows
  5. Right-click on any selected cell and choose "Delete row" from the context menu or just press Ctrl + - (minus sign).
    Right-click on any selected cell and choose Delete row
  6. Click OK in the "Delete entire sheet row?" dialog box.
    Delete entire sheet row dialog box
  7. Clear the applied filter: go to the Data tab and press the Clear button.
    Clear the applied filter
  8. Well done! All the blank rows are completely removed, and line 3 (Roger) is still there (compare with the previous version).
    All the blank rows are completely removed

Delete blank rows if your table does not have a key column

Use this method if you have a table with numerous empty cells scattered across different columns, and you need to delete only those rows that do not have a single cell with data in any column.
Excel Table with numerous empty cells scattered across different columns

In this case we do not have a key column that could help us to determine if the row is empty or not. So we add the helper column to the table:

  1. Add the "Blanks" column to the end of the table and insert the following formula in first cell of the column: =COUNTBLANK(A2:C2).

    This formula, as its name suggests, counts blank cells in the specified range, A2 and C2 is the first and last cell of the current row, respectively.
    Excel formula to count blank cells in the specified range

  2. Copy the formula throughout the entire column. For step-by-tep instructions please see how to enter the same formula into all selected cells at a time.
    Copy the formula throughout the entire column
  3. Now we have the key column in our table :). Apply the filter to the "Blanks" column (see the step-by-step instructions above) to show only rows with the max value (3). Number 3 means that all the cells in a certain row are empty.
  4. Then select all the filtered rows and remove whole rows as described above.
    As a result, the empty row (row 5) is deleted, all the other rows (with and without blank cells) remain in place.
    Empty row (row 5) is deleted
  5. Now you can remove the helper column. Or you can apply a new filter to the column to show only those rows that have one or more blank cells.
    To do this, uncheck the "0" checkbox and click OK.
    uncheck the "0" checkbox and click OK

    Show only those rows that have one or more blank cells

The fastest way to remove all empty rows - Delete Blanks tool

The quickest and impeccable way to remove blank lines is to the Delete Blanks tool included with our Ultimate Suite for Excel.

Among other useful features, it contains a handful of one-click utilities to move columns by drag-n-dropping; delete all empty cells, rows and columns; filter by the selected value, calculate percentage, apply any basic math operation to a range; copy cells' addresses to clipboard, and much more.

How to remove empty rows in 4 easy steps

With the Ultimate Suite added to your Excel ribbon, here's what you do:

  1. Click on any cell in your table.
  2. Go to the Ablebits Tools tab > Transform group.
  3. Click Delete Blanks > Empty Rows.
    Safely remove empty rows in Excel.
  4. Click OK to confirm that you really want to remove empty rows.
    Confirm that you want to remove empty rows.

That's it! Just a few clicks and you've got a clean table, all empty rows are gone and the rows order is not distorted!
All empty rows are gone and the rows order is preserved.

Tip. More ways to remove empty rows in Excel can be found in this tutorial: Delete blank lines with VBA, formulas and Power Query

Video: How to remove blank rows in Excel



Table of contents

124 comments

  1. I have been searching for a solution for an hour and finally found your article. This is exactly what I need! "Delete blank rows if your table does not have a key column" was super helpful, no other website had this option. THANK YOU!

  2. I record the results of our regular Senior Society Golf Competitions and have developed a file to calculate the cash prizes and check that there is no cheating etc.!!

    I have a problem with two cells (that I am aware of!) on a spreadsheet where the formula - mathematically absolutely correct and copied above and below - does not record a reference to another sheet.

    The one cell remains blank and the other N/A.

    B-Ball Number Gross Score R Player
    1st 2440 79 40 90 ANTON J DU TOIT
    1st 2809 81 40 90 EUGENE BRIAN VALENTINE RICKETTS
    2nd 7143 83 40 75 EDWARD RADFORD
    2nd 2176 85 40 75 JOHAN HAMMAN
    3rd 4571 87 39 60 DEON MINNAAR
    3rd 4576 89 39 60 GREGORY SMITH
    4th 6197 91 39 45 ALBERTUS JOHANNES LIEBENBERG
    4th 93 39 45 #N/A ATKINS
    5th 4567 95 37 30 TUEK JONG LEE
    5th 1045 97 37 30 GREGORY BAXTER

    The two formulae are referring to a sheet named Calculations:
    =IF(Calculations!D37>0;Calculations!D37;IF(Calculations!D60>0;Calculations!D60;Calculations!D83)) - returns a blank cell

    =IF(Calculations!D37>0;Calculations!J37;IF(Calculations!D60>0;Calculations!J60;Calculations!J83)) - returns a #N/A

    In both case the rows above and below record the correct values.

    I am using - as we cannot afford to upgrade - the old Excel 2010 version. Could this be the problem?

    Thanks

    Greg

      • Alexander Hi,

        Many thanks for your prompt comments.

        My thinking is that somehow these two specific cells have in some way been degenerated/degraded (I cannot recall the correct term now).

        I am going to try to delete the specific row and amend the sheet.

        Best regards from South Africa

        Greg

  3. Hi - I am already a customer and have ablebits for Google Sheets. I am wondering if your Delete Blanks works on a Mac?

    Thanks/sean

  4. Why not just sort the data, the blanks move to the bottom and out..

    • Sometimes the order is important too. IMO, the countblank formula is good.

  5. Very helpful, thank you.

  6. My Excel has 40,00,000 of entries in that 20,00,000 of blank rows..I have applied all the above but no use.
    kindly pls help me if you have any other ideas.

    Thanks,
    Santhosh Kumar.

      • Thank you so much for the help
        And how can one get the licence key?

    • Go to first row .... press shift + crtl + end ...... (for all data select, in 1 column) ....... then press ctrl + G .. (Go to .... ) ..... click on Special button, then choose Blanks .. and Click on OK button...... then click on delete (beside insert in Excel... see excel top right corner) ... then choose Shift Cell Up ... & Click on OK ...... Done ...................

      • Nice.. thanks..

      • Very nice trick. good job. This is the only usable trick in this article.

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