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.

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

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.

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.

See also

4 Responses 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

  2. ZEEFAH says:

    please tell me if i can send you the excel document for solving my problem.

  3. ZEEFAH says:

    I have sent the excel document for reference.

Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard