Easy Ways to Remove Blank Rows in Microsoft Excel Worksheets

by Avantix Learning Team | Updated August 27, 2020

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

You can delete blank rows in Microsoft Excel worksheets manually or you can use several tricks to delete multiple blank rows quickly. First, it’s essential that Excel users know how  to select rows quickly using shortcuts or by using Excel commands such as Go to Special. You can also remove blank rows by filtering or sorting. 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 specific versions of Excel such as Power Query and the Filter function (currently available only for 365 users).

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

Selecting rows in Excel worksheets

The following are common methods to select rows in Excel:

  • To select one row, click its heading or select a cell in the row and press Shift + spacebar.
  • To select multiple contiguous rows, drag across the row headings using a mouse or select the first row and then press Shift and click the last row.
  • To select non-contiguous rows, click the heading of the first row and then press Ctrl and click the headings of the other rows you want to select.

Deleting a single row

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

  1. Select a row.
  2. Right-click the selected row heading. A drop-down menu appears.
  3. Select Delete.

Instead of right-clicking, you can press Ctrl + – (minus sign at the top right of the keyboard).

Deleting multiple rows

If you want to delete multiple rows, you can select them and delete theme manually:

  1. Select a row or rows.
  2. Right-click one of the row headings. A drop-down menu appears.
  3. Select Delete.

Instead of right-clicking, you can press Ctrl + – (minus sign at the top right of the keyboard).

Deleting blank rows by sorting

An easy way to delete blank rows is to sort the data so that banks 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.

Deleting blank rows within a data set using Go to Special

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 blanks 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 to display the Go To dialog box and then 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.
  3. Select Blanks in the Go to Special dialog box and click OK. Excel will select all of the blank cells within the range.
  4. Right-click one of the selected blank cells and select Delete. A dialog box appears.
  5. Select Entire Row and click OK to confirm the delete.

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.

Deleting blank rows using filtering

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

  1. Select the range of cells that includes all of the data in the data set.
  2. Click the Data tab in the Ribbon.
  3. Select Filter in the Sort & Filter group. Alternatively, you can press Ctrl + Shift + L.
  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.
  8. Click the Home tab in the Ribbon and select Delete in the Cells group. A drop-down menu appears.
  9. Select Delete Sheet Rows.
  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 will be 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.

Related

How to Use Flash Fill in Excel to Clean or Extract Data (Beginner’s Guide)

How to Quickly Fill in Missing Values from the Cell Above in Excel

10 Great Excel Navigation Shortcuts

Recommended Microsoft Excel training

Microsoft Excel: Intermediate / Advanced

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

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

VIEW MORE COURSES >

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

To request this page in an alternate format, contact us.

Copyright 2020 Avantix® Learning 

You may also like

How to Convert Cm to Inches in Excel (or Inches to Cm)

How to Convert Cm to Inches in Excel (or Inches to Cm)

You can convert cm to inches (or inches to cm) using formulas with operators or functions in Excel. This is useful since the regional settings on a computer affect the default measurement system used in Excel and other programs.

How to Hide Comments or Notes in Excel Workbooks

How to Hide Comments or Notes in Excel Workbooks

You can hide comments and notes in Excel workbooks. In addition to adding comments in Excel, there is a little known function called the N function that you can use to enter notes directly within a formula. The N function has been around for a long time so you can use it in older versions of Excel.

How to Move a Pivot Table in Excel

How to Move a Pivot Table in Excel

Moving a pivot table is not as simple as moving other objects in an Excel worksheet or workbook. You will typically need to use the Move PivotTable command in the Ribbon to move a pivot table to a different area on a worksheet or to a different sheet in the same workbook.

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 (Great Strategies, Tricks and Shortcuts for All Versions)
Article Name
How to Delete Blank Rows in Excel (Great Strategies, Tricks and Shortcuts for All Versions)
Description
You can delete blank rows in Microsoft Excel worksheets manually or you can use several tricks to delete multiple blank rows quickly. First, it's essential that Excel users know how  to select rows quickly using shortcuts or by using Excel commands such as Go to Special. You can also remove blank rows by filtering or sorting. In this article, we'll focus on methods that work with all versions of Excel.
Author
Publisher Name
Avantix Learning

Pin It on Pinterest

Share This