Video: How to remove blank rows in excel

How many times have you realized that hundreds of records are left out from your calculations because of blank rows? Let's check a popular misconception about finding them and consider three ways you can quickly remove empty rows in this video.

Delete blank rows in Excel: video transcript

Blank rows can easily interfere with the results of your calculations. This video will show you several ways you can delete empty rows in Excel and consider one way you should avoid.

A tricky tip for Excel: delete empty rows and lose data

There is a popular suggestion to use F5 and "Go To Special" dialog to select blanks and then simply delete entire rows. What they don't tell you is that this way you'll delete all rows that contain even just one blank cell, so you risk losing a lot of data.
Delete the entire row with one blank

Let's look at three other approaches that you can use without corrupting your table.

Delete all blank rows in Excel with the help of a formula

If all your columns are equally important and you want to delete completely empty rows, you can use a a formula in a helper column. Add the column to the right of your table and enter

=COUNTBLANK, add a reference to the row it's in, from the first to the last cell. The formula will count the number of empty cells in this row. Copy it down to the end of your table:

  • Press Ctrl + Shift + End to see the last cell in your range
  • go to your new column and select it with the help of Ctrl + Shift + Up keys
  • paste the formula

Now we can separate the blanks from the rest of our data. Go to Data tab and click the filter button, use our new column header, unselect all, and look for the biggest number, which should be the number of cells in your rows. I have five cells here, so these are the rows I need.
Filter and delete blanks counted by the formula

Pick the first cell by pressing Ctrl + Home and then select all these rows with the help of the shortcut Ctrl + Shift + End.

Now you can either right-click and choose to "Delete rows", or use the shortcut Ctrl + minus to remove blank rows.

Filter out blanks and delete empty rows in Excel

If you want to find records that miss just the main details, such as the IDs, you can filter the range by your key column. Use the same shortcuts to select the table and make sure you don't leave anything out: Ctrl Shift End, and click the filter button.

  • Apply it to your key column
  • Click "Select all" to clear the check-boxes, scroll to the end of the list and select "Blanks"
  • Click Ok and you'll get all rows without the key records
    Filter and delete blanks by your key column

To delete empty rows, select them using the same shortcut: Ctrl + Shift + End. Now you can right-click and choose to delete entire rows, or use the shortcut Ctrl -. Now you can clear the filter from the same tab.

A special add-in to remove empty rows in Excel

Of course if you have Ultimate Suite, you get a much simpler and quicker way to delete blank rows: you'll find it under Ablebits Data tab. Look for the Clean group and click Empty rows. Confirm that's what you want to do and get your table ready.
Empty Rows add-in to delete blanks

You can get ultimate Suite with a discount by using the coupon code Preview.

Now you can be sure your tables are clean and ready for work. If you still have questions about removing empty rows in Excel, please post them as comments, we'll do our best to help you.

You may also be interested in

4 comments to "Video: How to remove blank rows in excel"

  1. ZEEFAH says:

    I am puzzled in getting a report from one excel sheet based on specific ID.
    Prices 75 45 7 70 30 40 65 7 30 115 75 55 7
    Meal Meal-1 Meal-2 Meal-3 Meal-4 Meal-5 Meal-6 Meal-7 Meal-8 Meal-9 Meal-10 Meal-11 Meal-12 Meal-13
    ID 15-Sep 16-Sep 15-Sep2 16-Sep3 15-Sep4 16-Sep5 15-Sep7 15-Sep8 15-Sep9 15-Sep10 15-Sep11 15-Sep12 15-Sep13
    1 117 230 27 27 225 146 127 214 225 232 79 127
    2 79 20 27 34 227 104 142 214 214 225 102 127
    3 153 79 27 117 93 209 209 227 146 127
    4 31 61 27 214 42 209 102 100 39 127
    5 3 32 209 106 80 110 151 127
    6 227 98 209 143 100 230 74 127
    7 225 16 34 29 45 127
    8 110 34 69 79 31 127
    9 79 121 98 17 61 132
    10 88 81 79 64 32 132
    11 46 71 27 75 93 153
    12 214 223 23 75 125 153
    13 51 15 130 107 12 153
    14 95 111 11 107 224 153

    Result Sheet:

    Date: This cell matches ID (This sheet) and Serial (ResultSheet) and finally extract date from ResultSheet.
    Left this date if ID does not matches

    Meal Served: This cell look at the "Date" and than finally extract "Meal" from ResultSheet

    Qty: This cell also look at the "Date" then count IDs (ResultSheet), matches with ID

    Unit: This cell also look at the "Meal" and than finally extract "Price" from ResultSheet

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