How to Delete Blank Rows in Excel if Certain Column is Blank
Use this technique if you have a table with a lot of empty cells dispersed over many columns, and you simply need to eliminate the rows with no data in any cell in any column.
Since there is no key column in this situation, we cannot tell whether the row is empty or not. The helper column is therefore added to the table
Step 1: Create a Blank Column and Insert CountBlank Formula
Let us add the “Blanks” column at the end, Insert the following formula in the first cell of the “Blanks” column
=COUNTBLANK(A2:D2) and Copy the formula throughout the entire column.
In the example, a “Blanks” column (E) is added, and the formula “=COUNTBLANK(A2:D2)“calculates the number of blank cells in each row. Pressing Enter displays the count of blank cells in column E for each row.
Step 2: Apply Filter
Now that we have added a key column to our table, add a filter to the Blanks column, to show only rows with the max value (4). Here, number 4 represents that all the cells in a certain row are empty.
Step 3: Select Filtered Rows and Remove Rows
Select all the filtered rows and remove the rows by right-clicking and selecting Delete option. Clear the filter applied under the Data tab.
You may now eliminate the helper column. Alternatively, you can add a fresh filter to the column to only display the rows with one or more blank cells.
How to Delete Blank Rows in Excel
Excel is the best tool to play with your data. It will provide you with everything that can make your life very easy. But in Excel, one thing that irritates us a lot is the blank rows. In many cases, we need to format our data many times, which leads to blank rows.
To sort out this problem you can delete blank rows from Excel. This article will guide you through practical and straightforward methods to delete blank rows in Excel, ensuring your spreadsheets remain tidy and functional.
Contact Us