How to find blank rows in Excel

Easy Ways to Remove Blank or Empty Rows in Excel

by Avantix Learning Team | Updated April 5, 2021

Applies to: Microsoft®Excel® 2010, 2013, 2016, 2019 and 365 (Windows)

You can delete blank rows in Excel using several tricks and shortcuts. Check out these 5 fast ways to remove blank or empty rows in your worksheets.

In this article, well focus on methods that work with all versions of Excel. In future articles, well take a look at other methods available in Excel 365.

Here, were assuming your data is in ranges of cells with data arranged vertically below row headings or field names (and no merged cells). Excel recognizes data arranged in this way as a list or data set (or database). You can also use many of these strategies with Excel tables.

Recommended article: 15 Microsoft Excel Keyboard Shortcuts to Speed Up Formatting

Do you want to learn more about Excel?Check out our virtual classroom or live classroomExcel courses >

If you want to delete one row, you can delete the row manually:

  1. Select the row. Click its heading or select a cell in the row and press Shift + spacebar.
  2. Right-click the selected row heading. A drop-down menu appears.
  3. Select Delete.

1. Deleting blank rows using the context menu

To delete multiple contiguous blank rows using the context menu:

  1. Drag across the row headings using a mouse or select the first row heading and then Shift-click the last row heading.
  2. Right-click one of the row headings. A drop-down menu appears.
  3. Select Delete.

To delete multiple non-contiguous blank rows using the context menu:

  1. To select non-contiguous rows, click the heading of the first row and then Ctrl-click the headings of the other rows you want to select.
  2. Right-click one of the row headings. A drop-down menu appears.
  3. Select Delete.

2. Deleting blank rows using a keyboard shortcut

To delete multiple contiguous blank rows using a keyboard shortcut:

  1. Drag across the row headings using a mouse or select the first row heading and then Shift-click the last row heading.
  2. Press Ctrl + (minus sign at the top right of the keyboard) to delete the selected rows.

To delete multiple non-contiguous blank rows using a keyboard shortcut:

  1. To select non-contiguous rows, click the heading of the first row and then Ctrl-click the headings of the other rows you want to select.
  2. Press Ctrl + (minus sign at the top right of the keyboard) to delete the selected rows.

3. Deleting blank rows by sorting

An easy way to delete blank rows is to sort the data so that blanks appear at the bottom and you can then ignore them.

To delete blank rows by sorting:

  1. Select the entire range of data (not just the column you want to sort).
  2. Click the Data tab in the Ribbon.
  3. Select Sort in the Sort & Filter group. A dialog box appears. Assuming you have a header row, select My data has headers.
  4. Beside Sort by, select the field or column with the blanks you want to remove and then select the appropriate sorting option (such as A-Z, smallest to largest, ascending or descending) to display blanks at the bottom of the data set.
  5. Click OK. Blank rows will now appear at the bottom of the data set and can be ignored.

Below is the Sort dialog box:

4. Deleting blank rows using Go to Special to highlight blanks

A great way to remove blank rows in a range of data is to use Go to Special.

The Go To Special dialog box displays the following options:

To find and remove blank rows using Go to Special:

  1. Select one column where there are blank cells in the column (were assuming here that the rest of the row is blank). If there is sensitive data above or below the list that you dont want to delete, select the cells in the column from the first cell in the range to the last cell in the range (you could click in the first cell and Shift-click in the last cell).
  2. Press Ctrl + G. The Go To dialog box appears.
  3. Click Special to display the Go To Special dialog box. Alternatively, you can click the Home tab in the Ribbon and then select Go To Special from the Find & Select drop-down menu.
  4. Select Blanks in the Go to Special dialog box and click OK. Excel will select all of the blank cells within the selected range.
  5. Right-click one of the selected blank cells and select Delete. A dialog box appears.
  6. Select Entire Row.
  7. Click OK.

The Delete dialog box appears as follows:

Although you can also use the Find command to find blanks, its much easier to use Go to Special.

5. Deleting blank rows using filtering

You can also delete blank rows using filtering (traditionally called AutoFiltering).

To delete blank rows using Filter:

  1. Select the range of cells that includes all of the data in the data set (including blank rows).
  2. Click the Data tab in the Ribbon.
  3. Select Filter in the Sort & Filter group. Alternatively, you can press Ctrl + Shift + L. Arrows appear beside the field names.
  4. Click the arrow beside the field name with the blank cells in rows you want to delete.
  5. Turn off or de-select Select All.
  6. Select Blanks. You will likely need to scroll down to select Blanks.
  7. Select the row headings of the filtered rows by clicking the first row heading and Shift-clicking the last row heading.
  8. Right-click one of the selected headings. A drop-down menu appears.
  9. Select Delete Row.
  10. Click the Data tab in the Ribbon and select Clear in the Sort & Filter group to remove the filtering.

In the example below, the Promotion field is filtered to display blanks:

You will now be able to sort, filter and create pivot tables with the list.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receivenew articles, join our email list.

More resources

How to Use Flash Fill in Excel (4 Ways with Shortcuts)

How to Lock Cells in Excel (Protect Formulas and Data)

3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells

Use Conditional Formatting in Excel to Highlight Dates Before Today (3 Ways)

How to Replace Blank Cells in Excel with Zeros (0), Dashes (-) or Other Values

Related courses

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Power Query to Get and Transform Data

Microsoft Excel: New and Essential Features and Functions in Excel 365

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at if you'd like to arrange custom onsite training or an online course on a date that's convenient for you.

Copyright 2022 Avantix® Learning

You may also like

How to Convert Text to Numbers in Excel (5 Ways)

Here are 5 different ways to quickly convert numbers stored as text to numbers in a Microsoft Excel worksheet. You can use a Smart Tag, Paste Special or

Use Excel Conditional Formatting to Highlight Dates Before Today (3 Ways)

You can use conditional formatting in Excel to highlight cells containing dates before today or within a date range before the current date. In a worksheet, you can use conditional formatting to highlight selected cells by filling them with a color based on rules or conditions. This type of formatting is helpful if you want to highlight past due dates such as invoices that are 30, 60 or 90 days overdue.

How to Replace Spaces in Excel with Underscores (_), Dashes (-) or Other Values

You can quickly replace blank cells in Excel with zeros, dashes or other number or text values (0, -, N/A, Null or other text). Its useful to fill blank cells with a number, symbol or value if you want to use the data set as the source for a pivot table or use other data analysis tools.

MORE EXCEL ARTICLES >

Microsoft, the Microsoft logo,Microsoft Office and related Microsoft applicationsand logosare registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 |

Summary
Article Name
How to Delete Blank Rows in Excel (5 Fast Ways to Remove Empty Rows)
Description
You can delete blank rows in Excel using several tricks and shortcuts. Check out these 5 fast ways to remove blank or empty rows in your worksheets.
Author
Avantix Learning Team
Publisher Name
Avantix Learning