May 26

0 comments

Remove empty rows in Excel

By Sharyn Baines

May 26, 2021

Delete, Filter, Hide, Rows

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 delete rows in excel

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 delete blank rows in excel

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. 

how to remove empty rows in excel

Then right click any of the row numbers and select Delete from the shortcut menu.

how to remove blank rows in excel

Excel shortcut delete rows

To delete a single row, click on to any cell in the row.

how to delete empty rows in excel sheet

Press Shift + Spacebar to select the entire row.

excel delete empty rows quickly

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.

excel delete blank rows not cells

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.

excel remove empty rows between data

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).

excel delete empty rows in range

Remove the check mark from the Select All option in the list and then scroll right to the bottom of the list. Select (Blanks).

microsoft excel remove blank rows

Click OK.

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.

excel delete blank rows only

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.

excel help remove blank rows

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.

excel remove blank rows between data

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.

excel remove blank rows without sorting

Select Blanks in the Go To Special dialogue box. This option tells Excel to only select blank rows or blank cells.

excel delete blank rows bottom of spreadsheet

Click OK. This should have selected all your blank rows.

excel remove blank rows in between

Press Ctrl + - and a Delete dialogue box will appear asking you what you want to do.

in excel remove blank rows

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.

how to delete all blank rows in excel

Now press Shift + spacebar. This will select the entire row.

how to delete rows in excel

Now press Shift + Ctrl + Down Arrow. This will select to the very last row in the worksheet.

how to delete blank rows in excel

Place your mouse pointer over the row numbers and right-click. Select Hide from the shortcut menu.

how to remove empty rows in excel

The rows will now be hidden. The rows will remain in the worksheet but will not be visible.

how to remove blank rows in excel

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.

remove empty rows in excel

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.

excel delete blank rows keyboard shortcut

The last row will now be visible below the last row of your data.

excel how delete empty rows

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.

can excel delete blank rows

Right-click over either of the row numbers so that the shortcut menu is shown, and then select Unhide.

microsoft excel delete empty rows

Your hidden rows will once again be visible.

Was this Blog helpful? Let us know in the comments below.

Sharyn Baines

About the author

Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand. She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!

>