Need help removing empty rows in Excel?
Let’s look at several options as each one is super useful depending on the number of rows you need to delete.
How to remove a blank row in Excel
The easiest way to get rid of one single row is just to right click over the row number and then select Delete from the shortcut menu.
How to remove multiple adjacent blank rows in Excel
If you're wanting to delete adjacent empty rows, you can just click and drag over the row numbers to select them, right click any of the row numbers, and then click Delete.
How to remove random blank rows in Excel
If you have non-adjacent empty rows you can select each one by holding down your Ctrl key, then with the Ctrl key still pressed down, click the row numbers you want to delete.
Then right click any of the row numbers and select Delete from the shortcut menu.
Excel shortcut delete rows
To delete a single row, click on to any cell in the row.
Press Shift + Spacebar to select the entire row.
Once the row is selected, can press Ctrl + - (minus sign) to delete the row.
Delete empty rows from a large table of data
Using your Ctrl + right click to select random rows for deletion is good when you have a small table of data. However, when you have a large table of data with a large number of empty rows this isn’t the easiest way to delete them.
Instead try the following.
Select all the data in your table, including the empty rows.
Hint: To quickly select from the top to the bottom of a large table, and include blank rows try this. Select the top left cell in the table. Hold Shift and then click the bottom right cell of the table. This will select all cells in between both points.
The reason a point-to-point click is better than using Ctrl + A to select the data, is that Ctrl + A will select a table of adjacent data and will stop at the first empty row. You want to include the entire table, including the empty rows. Therefore, a point-to-point selection makes selecting a little quicker.
Scroll back up the screen to the top of the table so that you can see the headings at the top of each column.
Go to the Data tab and then select Filter. This will turn on Filter.
Now go to any of your column titles at the top of your table and select one of the Filter drop-down arrows (as shown below).
Remove the check mark from the Select All option in the list and then scroll right to the bottom of the list. Select (Blanks).
You should now be seeing all the blank rows that were within the area that you selected. A blue row number indicates that each of the rows is part of the filtered data set.
Now all you need to do is select all the rows with blue numbers. Be sure to select the entire row. The best way to select the entire row is to click and drag over the row numbers.
Now right-click over the row numbers and then select Delete from the shortcut menu.
On the Data tab, click the Clear button to remove the Filter. You will now see all of your data with the empty rows now removed.
Remove blank rows using Go To Special, Select Blanks
If you're not familiar with filtering and you've never filtered before, here's another way.
Select the entire table of data and then press Ctrl + . (period or full-stop) to quickly move back to the top of the selected data.
Select the Home tab and then click Find & Select. Now, click Go to Special.
Select Blanks in the Go To Special dialogue box. This option tells Excel to only select blank rows or blank cells.
Click OK. This should have selected all your blank rows.
Press Ctrl + - and a Delete dialogue box will appear asking you what you want to do.
Select Entire row and then click OK.
The empty rows should now be removed.
Useful tip: Ctrl + Down Arrow will take you to the first instance of an empty row. So, if you press this you will be able to see if there are remaining rows in your data table that haven’t been removed.
Delete rows in Excel that go on forever
If you are trying to remove ALL the blank rows at the bottom of your data you may have found that the options above haven’t helped, and the blank rows still remain.
This is because Excel has a set number of rows per worksheet. These will remain visible, even if you try to delete them.
However, you can hide them.
To do this, click on to a cell in the first row that you want to hide.
Now press Shift + spacebar. This will select the entire row.
Now press Shift + Ctrl + Down Arrow. This will select to the very last row in the worksheet.
Place your mouse pointer over the row numbers and right-click. Select Hide from the shortcut menu.
The rows will now be hidden. The rows will remain in the worksheet but will not be visible.
How to unhide only the rows at the end of your worksheet
Hiding the empty rows is quite easy. However, bringing them back can be a little fiddly, depending on your data.
If you only have hidden rows at the end of your data, you can easily select the entire worksheet (press Ctrl+ A until the entire worksheet is selected) then right-click over the row numbers and select Unhide from the shortcut menu.
However, if you have hidden rows inside your data area the above option will unhide these as well. To avoid this follow the steps below.
Press F5 to open the Go To dialogue box. In the Reference box type A1048576 and then click OK.
This will move the cell selector to the last row in column A. You still won’t be able to see the cell on screen at this point. To see the row, from the Home tab select Format, Hide & Unhide, Unhide Rows.
The last row will now be visible below the last row of your data.
Now select over the last row of your data and row 1048576 so that both rows are selected. In doing this you are showing Excel that you want to unhide any hidden rows that are between the two selected rows.
Right-click over either of the row numbers so that the shortcut menu is shown, and then select Unhide.
Your hidden rows will once again be visible.
Was this Blog helpful? Let us know in the comments below.