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 2019, 2016, 2013 and lower.
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.
All over the Internet you can see the following tip to remove blank lines:
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.
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: (
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.
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.
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.
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:
=COUNTBLANK(A2:C2)
.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.
With the Ultimate Suite added to your Excel ribbon, here's what you do:
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!
112 responses to "How to remove all blank rows in Excel"
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.
Hello!
We have a tool that can solve your task in a couple of clicks: Ablebits Tools - Delete Blanks.
This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
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 ...................
Very helpful, thank you.