How to Quickly and Easily Delete Blank Rows in Excel

Blank rows can be an annoyance when you’re working with a spreadsheet, but deleting them by hand or trying to remove all the blank rows in Excel can be difficult, if not impossible. Fortunately, there’s an easy way to quickly remove all the blank rows in Excel and save yourself some time and frustration. You just need to know where to look! Here’s how to delete blank rows in Excel.

Type the formula =SUM(A1:A1048576)

This is a simple formula that will quickly sum all of the values in a given column. Just select the range of cells you want to check for blanks, enter the formula, and hit Enter. If there are any blank cells within the range, the sum will be zero. Otherwise, it will be equal to the number of non-blank cells. Now that we know which rows have data and which don’t, we can delete them!

To do this with a keyboard shortcut, simply hold down CTRL while pressing the DELETE key on your keyboard. You’ll see a dialog box asking if you’re sure you want to delete these cells; just click OK. For those who prefer the mouse, right-click on the row numbers area in an empty part of your spreadsheet (outside of any individual cell) and click Delete from the context menu.

Select the entire column

To delete blank rows, you first need to select the entire column. To do this, click on the column header. For example, if you want to delete blank rows in column A, click on the header for column A.

Click Data (one sentence): Next, click on the Data tab at the top of the screen.
Click Filter (one sentence): Then, click on the Filter button. This will bring up a drop-down menu.
Click on the arrow next to Column A (two sentences): In the drop-down menu, click on the arrow next to Column A. This will open up a second drop-down menu. Select all cells that contain data and uncheck all cells that are empty.

Copy, then select cell B1 and hit enter

Did you know that you can quickly and easily delete blank rows in Excel? Here’s how – Select the entire row.

Press Delete on your keyboard.
All of the cells will be cleared from the selected row, including any empty cells.
Alternatively, if you want to clear just one cell, select it before pressing Delete. The same result as above.
To delete all of the cells in a column: go into Edit Mode (Ctrl+E), highlight the columns you want to delete, then press Delete. The whole column will be deleted.

Select entire worksheet by hitting control + shift + end

This keyboard shortcut will select the entire worksheet—including any blank rows. From there, you can hit delete to get rid of all the blank rows at once. If you only want to delete certain blank rows, you can use the filter tool. First, click on the data tab, then click filter. This will bring up a drop-down menu in each column header. Click on the arrow in the column header of the column where your blanks are located.

Select blanks from the list, and hit OK. Now all of your blank cells will be highlighted. You can hit delete to remove them, or select a specific range of cells to delete by hitting control + shift + 8. If you’re looking for a quick way to remove these types of cells, we recommend using the formulas =SUBSTITUTE(SUBSTITUTE($A$1, , ), , ) for columns and =SUBSTITUTE(SUBSTITUTE($B$1, , ), , ) for rows.

Optional – convert all cells containing TRUE back to zero

You can quickly delete blank rows in Excel using a simple filter. Just select all the cells that contain data, click the Data tab, then click Filter. Next, click the drop-down arrow next to the column you want to filter and select Blanks from the list. Finally, click the delete icon or press Ctrl+-. There are also many other ways to remove or delete empty rows in Excel.

The quickest way is to use the shortcut key Alt+Enter, which will toggle between displaying only the selected row (which will be highlighted) and displaying all of the cells in your worksheet. To remove an entire row of empty cells at once, just highlight it and hit Delete on your keyboard. If you don’t want to delete anything at all, but simply hide rows with empty cells for now, just type = into any cell within that row and hit Enter on your keyboard – this will hide everything until you type something else into any cell within that row again.