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

10 Great Excel Navigation Shortcuts

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

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 live 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 training on a date that's convenient for you.

Copyright 2021 Avantix® Learning

You may also like

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

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

You can apply strikethrough to text or values in Excel to cross out or create a line through a cell or part of a cell. There are 3 common ways to apply strikethrough in your Excel worksheets – using a built-in keyboard shortcut, using the Font dialog box, or by adding a command to the Quick Access Toolbar.

How to Lock the Position of a Slicer in Excel

How to Lock the Position of a Slicer in Excel

Slicers are a great tool in Excel to filter tables or pivot tables. You can lock the position of a slicer in an Excel worksheet using a two-step process. First, select the slicer or slicers and Disable resizing and moving in the Format Slicer task pane (and choose other options based on your preferences). Second, protect the sheet using the Review tab in the Ribbon. There are a few important options you’ll need to select to get this to work.

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 | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | 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