04 Safest Methods: How to Delete Blank Rows in Excel?
A. CHALLENGES OF HOW TO DELETE BLANK ROWS IN EXCEL?
Every Excel user facing a big challenge, especially handling a big dataset, how to delete blank rows in Excel?
There are so many challenges explained below:
(01) It is a time-consuming factor to find, select and delete the blank rows manually one by one.
(02) If we go through Excel Autofilter (Excel Shortcut: Alt+D+L), simply filter ‘Blanks’ and delete the row. But this will not give a correct result.
Because filter will work columnwise or vertically, few cells in a row may be blank, but the filter considered those rows as blanks. It is impossible to find out these types of cases one by one from a huge dataset.
(03) If we go through Data Sort (Excel shortcut: Alt+D+S; sequentially press Alt, D, S), it is impossible to sort a number of columns from a big dataset. Because we don’t know which rows have blanks. Additionally, if there are a number of formulas in the dataset (such as nested IFs, COUNTIFS), after sorting they may not work perfectly.
(04) If we go through directly ‘Go To Special‘ dialog box (press Ctrl+G or F5 ➪ Click ‘Special’) and then select Blanks which will select all the blank cells in the dataset.
If we delete all the blank cells blindly it will delete all the blank rows including partial blank rows.
■ Note: We had detail discussed on Excel shortcuts in separate two tutorials, suggested you read these tutorials:
80+ Excel Shortcuts with ALT Key || Best Hotkey of Keyboard Shortcuts
90+ Best Excel CTRL Shortcuts | Useful Keyboard Shortcuts |
B. PROCEDURE OF HOW TO DELETE BLANK ROWS IN EXCEL?
We can delete blank rows in Excel using any of the following 03 methods:
(i) HOW TO DELETE BLANK ROWS IN EXCEL ➢ USING THE COUNTIFS FUNCTION
If we apply the COUNTIFS function to count the nonblank cells in a row. If the row becomes empty It returns zero (o). We filter out value zero and delete them.
• STEP 1: PLACE THE COUNIFS FUNCTION
We put a COUNTIFS function in a black cell just after the ending cell of the subject heading i.e., in the cell J2.
The Syntax for the COUNTIFS function is:
Place an equality sign (=) in cell J2 and type ‘coun…’, select COUNTIFS from the below suggestion list with the down Arrow key (⬇) and then press the Tab key on the keyboard. COUNTIFS syntax appears with an open parenthesis.
Note: the upper or lower case does not matter in syntax, Excel by default considers it in upper case.
The Complete formula is =COUNTIFS(A2:I2,”<>”&””)
A2:I2 = criteria_range1 which refers to the subject heading starting from A2 and ends till I2.
“<>”&”” = criteria1 which refers to non blanks.
The formula returns the result =9 which means there are 9 non-blank cells in the subject heading.
• STEP 2: EXTEND THE FORMULA TILL TO THE END OF THE RANGE
Copy (Ctrl+C) the cell J2 with formula and selects till to the end of the range.
Then press Alt+E+S+R (sequentially press Alt, E, S, R) or Alt+Ctrl+V+R (press Alt+Ctrl+V, then press R) which will select the ‘Formulas and number formats‘ in the Paste Special dialog box. Then press Enter or click OK.
As a result, the formula is copied to the selected range without cell formatting.
■ Note: We had detail discussed on Paste Special in a separate tutorial, suggested you read these tutorials: Paste Special in Excel Vs Break Link – Which one is Better?
• STEP 3: APPLY FILTER
It is best practice to apply the Excel AutoFilter by selecting the entire range with Shortcut Ctrl+A ➪ Then press Ctrl+Shift+L or Alt+D+F+F (press sequentially Alt, D, F, F) which will apply the AutoFilter.
Then open the filter on column J ➪ Press Alt+ Down arrow (⬇) which will open the filter list ➪ Uncheck all the checkboxes by clicking on the Select All checkbox ➪ Then select only zero (0) checkbox ➪ Finally, click OK. As a result, all the blank rows are selected.
■ Note: We had detail discussed on Excel Filter in a separate tutorial, suggested you read these tutorials: 04 Simple to Advanced Methods: How to Filter in Excel?
• STEP 4: DELETE BLANK ROWS
Select the first blank row by Shift+Spacebar ➪ Then select rest blank rows by clicking Shift+ Down arrow (⬇) or Shift+Page Down ➪ Then Press Alt+; (semicolon) which will select the only visible cell (this step is mandatory for filtered items, sometimes it can be deleted unfiltered items also) ➪ Finally press Ctrl+- (minus) which will delete all the blank rows in Excel.
• STEP 5: REMOVE FILTER
After deleting blank rows in Excel, clear the filter by pressing Alt+A+C (sequentially press Alt, A, C) or press Alt+D+F+S (sequentially press Alt, D, F, S).
(ii) HOW TO DELETE BLANK ROWS IN EXCEL ➢ USING THE COUNTBLANK FUNCTION
If we apply the COUNTBLANK function, empty rows return the highest value. We filter out the highest value and delete them.
• STEP 1: PLACE THE COUNTBLANK FUNCTION
We put a COUNTIFS function in a black cell just after the ending cell of the subject heading i.e., in the cell J2.
The Syntax for the COUNTBLANK function is:
Place an equality sign (=) in cell J2 and type ‘coun…’, select COUNTBLANK from the below suggestion list with the down Arrow key (⬇) and then press the Tab key on the keyboard. COUNTBLANK syntax appears with an open parenthesis.
■ Note: the upper or lower case does not matter in syntax, Excel by default considers it in upper case.
The Complete formula is =COUNTBLANK(A2:I2)
A2:I2 = criteria_range1 which refers to the subject heading starting from A2 and ends till I2.
The formula returns the result =0 which means there is no blank cell in the subject heading. If the formula returns the value 1 which indicates that there is a single blank cell in the selected range. Reasonably, the formula always returns the highest value against the blank rows.
• STEP 2: EXTEND THE FORMULA TILL TO THE END OF THE RANGE
Copy (Ctrl+C) the cell J2 with formula and selects till to the end of the range.
Then press Alt+E+S+R (sequentially press Alt, E, S, R) or Alt+Ctrl+V+R (press Alt+Ctrl+V, then press R) which will select the ‘Formulas and number formats‘ in the Paste Special dialog box. Then press Enter or click OK.
As a result, the formula is copied to the selected range without cell formatting. Please notice that the formula returns the highest value in the case of blank rows and our target to filter out this highest value.
• STEP 3: APPLY FILTER
It is best practice to apply the Excel AutoFilter by selecting the entire range with Shortcut Ctrl+A ➪ Then press Ctrl+Shift+L or Alt+D+F+F (press sequentially Alt, D, F, F) which will apply the AutoFilter.
Then open the filter on column J ➪ Press Alt+ Down arrow (⬇) which will open the filter list ➪ Uncheck all the checkboxes by clicking on the Select All checkbox ➪ Then select only the highest value, in this case we only consider the highest number 9 and select this checkbox ➪ Finally, click OK. As a result, all the blank rows are selected.
■ Note: We had detail discussed on Excel Filter in a separate tutorial, suggested you read these tutorials: 04 Simple to Advanced Methods: How to Filter in Excel?
• STEP 4: DELETE BLANK ROWS
Select the first blank row by Shift+Spacebar ➪ Then select rest blank rows by clicking Shift+ Down arrow (⬇) or Shift+Page Down ➪ Then Press Alt+; (semicolon) which will select the only visible cell (this step is mandatory for filtered items, sometimes it can be deleted unfiltered items also) ➪ Finally press Ctrl+- (minus) which will delete all the blank rows in Excel.
• STEP 5: REMOVE FILTER
After deleting blank rows in Excel, clear the filter by pressing Alt+A+C (sequentially press Alt, A, C) or press Alt+D+F+S (sequentially press Alt, D, F, S).
(iii) HOW TO DELETE BLANK ROWS IN EXCEL ➢ USING THE ‘GO TO SPECIAL’ BLANKS OPTION
If we want to use ‘Go To Special’ blanks option, then we use it tactfully. We always try to select that column which has no extra blank cells. Suppose, in the given example, we can select either column A or Coulmn B for this purpose, but we cannot select column C (cell C3 is blank) or Coulmn D (cell D12 is blank) because there are extra blank cells in the column range.
• STEP 1: SELECT A COLUMN
As explained, we can select a column which has no extra blank cell. In this case, we select column B either with Ctrl+Spacebar or by clicking on B located on the column address bar.
■ Note: We had detail discussed on Selection of Cells, Columns and Rows in a separate tutorial, suggested you read these tutorials: 09 Points Help You How to Select Multiple Cells in Excel
• STEP 2: SELECT ‘BLANKS’ IN THE ‘GO TO SPECIAL’ DIALOG BOX
➢ After selecting the column, press Ctrl+G or press the F5 key which will open the Go To dialog box ➪ Then either press Alt+S (hold down the Alt key and then press S) or click on the Special button which will open the Go To Special dialog box.
Equivalently, Go to the Home tab ➪ Click Find & Select dropdown ➪ Select Go To Special which will open the Go To Special dialog box.
➢ In the Go To Special dialog box, select Blanks option either press only the ‘K‘ key or click on the Blanks radio button ➪ Finally, press Enter or click OK.
As a result, all the blank cells in column B are selected.
• STEP 3: DELETE BLANK ROWS IN EXCEL
After selecting blank cells, press Ctrl+- (minus) which will open ‘Delete dialog box ➪ Choose any of the four options according to the requirement, but in this case we select ‘Entire row‘ radio button ➪ Click OK or press Enter.
As a result, we can easily delete blank rows in Excel.
(iv) HOW TO DELETE BLANK ROWS IN EXCEL ➢ USING THE ‘FIND AND REPLACE’ DIALOG BOX
We cannot apply the Find and Replace option in the entire worksheet, rather we used it in a specific column where does not have any extra blank cells.
• STEP 1: SELECT A COLUMN
As explained, we can select a column which has no extra blank cell. In this case, we select column B either with Ctrl+Spacebar or by clicking on B located on the column address bar.
■ Note: We had detail discussed on Selection of Cells, Columns and Rows in a separate tutorial, suggested you read these tutorials: 09 Points Help You How to Select Multiple Cells in Excel
• STEP 2: SELECT BLANK CELLS WITH THE ‘FIND AND REPLACE’ DIALOG BOX
➢ After selecting the column, press Ctrl+F which will open the Find and Replace dialog box.
Equivalently, Go to the Home tab ➪ Click Find & Select dropdown ➪ Select Find which will open the Find and Replace dialog box.
➢ Find what input box leaves blank.
➢ Click the ‘Options’ button to explore the Advanced options.
⇒ Select Sheet from the drop-down list from Within option.
⇒ Select Values from the drop-down list from Look in option.
⇒ Select the Match entire cell contents checkbox.
➢ Finally, click Find All which will suggest all the blank cells in column B.
• STEP 3: SELECT AND DELETE ALL BLANK ROWS IN EXCEL
➢ Press Ctrl+A which will select all the blank cells and press Esc(ape) key to close the Find and Replace dialog box.
➢ After selecting blank cells, press Ctrl+- (minus) which will open ‘Delete dialog box ➪ Choose any of the four options according to the requirement, but in this case we select ‘Entire row‘ radio button ➪ Click OK or press Enter.
As a result, we can easily delete blank rows in Excel.
Hi, I'm Mr KING Admin of KINGEXCEL.INFO .Thank you so much and I hope you will have an interesting time with KINGEXCEL.INFO. Mail: quantriexcel@gmail.com - PHONE : +84779313987.