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, we'll focus on methods that work with all versions of Excel. In future articles, we'll take a look at other methods available in Excel 365.

Here, we're 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 classroom Excel 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:

Sort dialog box in Excel to sort and remove or delete blank rows.

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:

 Excel Go to Special dialog box to find and delete blank rows.

To find and remove blank rows using Go to Special:

  1. Select one column where there are blank cells in the column (we're assuming here that the rest of the row is blank). If there is sensitive data above or below the list that you don't 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:

 Excel dialog box to delete blank rows after right-clicking.

Although you can also use the Find command to find blanks, it's 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:

Display blanks using Filter in Excel to remove or delete blank rows.

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 receive new 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 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

Copyright 2022 Avantix® Learning

You may also like

How to Password Protect Your Excel Worksheets and Workbooks

How to Password Protect Your Excel Worksheets and Workbooks

You can password protect worksheets and workbooks in Excel. If you want to prevent other users from opening or modifying an Excel workbook, you can encrypt the file with a password. You can also protect workbook structure where you can prevent users from deleting, renaming, moving or unhiding worksheets. Password protection can be added to your Excel file in several ways.

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

Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca

Summary
How to Delete Blank Rows in Excel (5 Fast Ways to Remove Empty Rows)
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
Publisher Name
Avantix Learning

Pin It on Pinterest

Share This