How to Delete Blank Rows in Excel

A few methods, with pros and cons of each

If yourExcel documenthas thousands of rows, manually deleting blank lines can be stressful and virtually impossible. You can useExcel macrosto clean up your data, but it can be difficult if you aren’t familiar with macros.

This tutorial will show you different ways to delete blank rows in any Excel version, from Excel 2003 to Excel 2016 and beyond.

Remove Blank Rows Using the “Sort” Tool

Sorting is a simple and effective way to delete blank lines without macros.

This method is best for deleting blank rows inExcel spreadsheetswith only one column. Say you have the following set of data in Excel and want to get rid of the blank lines:

Note that you want to sort the column with the blank values. If multiple columns have empty values, choose the column you want to sort and selectOK.

The sorting tool will reorder your column alphabetically, moving blank cells to the bottom. Before sorting, the order was “Cat, Dog, Pig, etc..” and now it’s “Bear, Cat, Cow, etc…”.

Select and delete the blank/empty rows. The next step is to restore cells in the column to their original order.

That’ll restore cells in the column to their original order. You can now delete the anchor/helper column.

You can also use this method to delete rows with only one empty cell (or column). But what if you need to blank rows across multiple columns? The instructions in the next section will help you weed out blank or empty rows in a multi-column spreadsheet.

We’ll use the sample data below (withseveral blank rows) to show how to use the Sort tool to delete blank rows in an Excel spreadsheet.

This spreadsheet has multiple blank rows and other rows with empty cells. Sorting the dataset by columns makes it easy to eliminate blank rows. Here’s how to get it done.

The next step is creating a “Sort Level” for subsequent columns.

Delete the anchor column (with numbers) to return your spreadsheet to its initial order.

How to Remove Blank Rows Using Filters

You can also use the Filter function to remove blank rows in an Excel spreadsheet. This method doesn’t involve adding an extra/anchor column to your dataset.

Note:If you get to a point where there are only blank rows showing, then you don’t have to filter for blanks for the remaining rows. You can proceed to remove blank rows in the spreadsheet.

How to Delete Any Blank Row or Cell

Use Excel’s “Go To” command to delete any row or cell with no value(s). This is useful when cleaning your dataset and removing any rows/columns with partial/incomplete data.

Excel will highlight all blank cells or rows in your worksheet.

That’ll delete all rows or columns with a blank cell in your data.

Clean Up Your Excel Spreadsheet

Use the “Sort” tool to remove blank rows in an Excel spreadsheet with few (≤ 10) columns. Using the Filter tool is quicker and better if you work with a larger dataset.

Founder of Help Desk Geek and managing editor. He began blogging in 2007 and quit his job in 2010 to blog full-time. He has over 15 years of industry experience in IT and holds several technical certifications.Read Aseem’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

Copyright © 2008-2024 Help Desk Geek.com, LLC All Rights Reserved