Easy Ways to Remove Blank or Empty Rows in Excel

by Avantix Learning Team | Updated September 14, 2023

Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019, 2021 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 in-person 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.

In this article, we'll review 5 ways to delete blank rows:

  1. Delete blank rows using the context menu
  2. Delete blank rows using a keyboard shortcut
  3. Delete blank rows by sorting
  4. Delete blank rows using Go to Special
  5. Delete blank rows by filtering

1. Delete 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. Delete 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. Delete 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. Delete 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. Delete 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 2024 Avantix® Learning

You may also like

What is Power Query in Excel?

What is Power Query in Excel?

Power Query in Excel is a powerful data transformation tool that allows you to import data from many different sources and then extract, clean, and transform the data. You will then be able to load the data into Excel or Power BI and perform further data analysis. With Power Query (also known as Get & Transform), you can set up a query once and then refresh it when new data is added. Power Query can import and clean millions of rows of data.

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