How to Remove Blank Rows in Excel

Posted May 26, 2021

1. How to remove a blank row in Excel

1. To remove a single blank row, right click over the row number. For example, if you wanted to removed row 6, right-click on top of the number 6 in the row header area. This will select the entire row and display the shortcut menu.

2. Select Delete from the shortcut menu. The row will be removed.

Product Sales spreadsheet with pop up menu and mouse over the Delete option

2. How to remove blank rows in Excel

To remove adjacent blank rows, click and drag over the row numbers to select the blank rows, right click any of the selected row numbers, and then click Delete.

Excel spreadsheet with two rows highlighted and a pop up menu with the Delete option being selected by the mouse

To remove non-adjacent blank rows...

1. Click the row number of the first blank row you want to remove. This will select the entire blank row.

2. Press and hold down the Ctrl key and then click the row numbers of the additional blank rows you want to remove. In the example below, row 10 was first selected and then rows 14 and 18 have been added to the selection using Ctrl + click.

Product Sales spreadsheet with empty rows 10, 14 and 18 selected

3. Now right click any of the selected row numbers and then select Delete from the shortcut menu. All of the selected blank rows will be removed.

Product Sales spreadsheet with empty rows 10, 14 and 18 selected. The pop up menu is displayed with the Delete option being selected by the mouse.

3. Shortcuts to remove blank rows in Excel

1. First click on to any cell in the blank row.

Product Sales spreadsheet with cell A10 selected

2. Then, press Shift + Spacebar to select the entire row.

Product Sales spreadsheet with row A10 selected

3. Once the row is selected, can press Ctrl + - (minus sign) to delete the row.

4. Filter to remove blank rows from a large list of data

Using your Ctrl + right click to select and remove random blank rows is good when you have a small list of data. However, when you have a large table of data with a large number of empty rows this isn’t the quickest way to delete them.

Use Filter to quickly remove blank rows in an Excel list.

Filter to find blank rows in Excel

1. Select all the data in your list, including the empty rows.

Hint: To quickly select from the top to the bottom of a large list, including the blank rows, first select the top left cell in the list. Pres and hold the Shift key and then click the bottom right cell of the list. This will select all cells in between both points.

Using this point-to-point click is better than using Ctrl + A to select the data, because Ctrl + A will only select the first empty row. You want to include the entire list, including the empty rows. Therefore, a point-to-point selection makes selecting a better option.

All rows to 807 are selected with an orange arrow pointing to cell D807 (the last cell of the Table) with the words "Click Here"

2. Scroll back up the screen to the top of the list so that you can see the headings at the top of each column.

3. Go to the Data tab and then select Filter. This will turn on Filter.

Data tab on the Home page with the Filter option being selected

4. Now go to any of your column titles at the top of your list and select one of the Filter drop-down arrows (as shown below).

Spreadsheet with the Filter on Name, 1 Litre, 2 Litre and 5 Litre Concentrate

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

Filter pop up with Blanks selected from the list in Excel

6. Click OK.

Remove blank rows from the filtered data

Excel will filter and display only the blank rows. A blue row number indicates that each of the rows is part of the filtered data set.

Spreadsheet showing blank rows filtered and the row numbers are in blue

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

8. Right-click over the row numbers and then select Delete from the shortcut menu.

Spreadsheet with the filtered blank rows selected and the pop up menu with the mouse selecting the option "Delete Row"

9. On the Data tab, click the Clear button to remove the Filter

Data tab on the Excel Ribbon with the Filter option selected and the mouse selecting the option "Clear" next to Filter

10. You will now see all of your data with the blank rows now removed.

5. Remove blank rows in Excel using Go To Special

If you're not familiar with filtering try this...

1. Select the entire list of data and then press Ctrl + . (period or full-stop) to quickly move back to the top of the selected data.

2. Select the Home tab and then click Find & Select. Now, click Go to Special.

On the Home tab the Find & Select option has been selected and a drop down menu is showing with the mouse selecting the "Go To Special" option

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

The Go To Special dialogue box with the option "Blanks"selected

4. Click OK. This will selected all the blank rows.

Product Sales spreadsheet with blank rows 13 & 17 selected

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

Delete dialogue box with the option "Shift cells up" selected

6. Select Entire row and then click OK.

The blank rows will 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.

6. Remove blank rows at bottom of Excel worksheet

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.

1. Click on a cell in the first row that you want to hide.

End of the table rows 807, 808, 809, 810 and 811 are shown. The data stops at cell 808 with the first cell in 809 selected.

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

End of the table rows 807, 808, 809, 810 and 811 are shown. The data stops at cell 808 with row 809 selected.

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

Excel spreadsheet with all rows to the very last row 1048576 selected

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

Excel spreadsheet with all rows to the very last row 1048576 selected and the pop up menu showing the mouse selecting the option "Hide"

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

Rows to 808 are visible with a grey area below the visible rows

7. How to unhide only rows at the end of worksheet

Hiding the empty rows is quite easy. However, bringing them back can be a little fiddly.

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 unhide only rows at the end of the worksheet follow the steps below...

1. Press F5 to open the Go To dialogue box. In the Reference box type A1048576 and then click OK.

Go To dialogue box with "A1048576" in the Reference box

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

Format option selected with HIde & Unhide selected from the drop-down menu and Unhide Rows being selected

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

Row 12 and 13 showing with row 1048576 below it in green

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

Rows 13 and 1048576 are both selected

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

Rows 13 and 1048576 are both selected with a pop up menu and the mouse selecting the option "Unhide"

Your hidden rows will once again be visible.

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

Elevate your Excel game and become a pro with our exclusive Insider Group

Be the first to know about new tutorials, videos, and tips for Microsoft 365 products. Join us now and claim your exclusive bonus, your list of Essential Excel Skills to become proficient in Excel!

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