If your Excel document has thousands of rows, manually deleting blank lines can be stressful and virtually impossible. You can use Excel macros to 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.

Table of Contents

    Remove Blank Rows Using the “Sort” Tool

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

    Delete Blank Row in a Single Column

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

    Excel spreadsheet with blank spaces/rows
    1. Right-click the column header and select Insert to add a new column. This blank column will serve as an anchor/helper column to sort out blank rows in the original column.
    "Insert" option in Excel context menu
    1. Number the new column consecutively (1, 2, 3, 4, 5, etc.) from the first cell. Here’s what the sheet should look like before we sort the animal name column:
    Excel column with consecutive numbers (1-11)
    1. Select both columns, open the Data tab on the ribbon, and select Sort.
    "Sort" tool in Excel's "Data" tab
    1. Open the Sort by drop-down menu and choose the column with empty/blank rows (“Column B” in our sample data). Select OK to proceed.
    Excel's "Sort" window

    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 select OK.

    Creating sort rules in Excel's "Sort by" column

    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…”.

    Excel columns with randomized numbers, animals, and empty rows

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

    1. Select both columns and choose Sort in the “Data” tab on the ribbon.
    Sort tool on Microsoft Excel ribbon
    1. Sort the data by the anchor/helper column and select OK. For our sample data, we’ll Sort by Column A.
    Microsoft Excel Sort window

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

    Columns in an Excel spreadsheet with randomized numbers and animals

    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.

    Delete Blank Row in Multiple Columns

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

    Excel spreadsheet showing data under the following headers: Name, Adress, Phone, and DOB

    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.

    1. First, add an anchor column to the left of the first column. Right-click the first column header and select Insert.
    "Insert" option in Excel right-click menu
    1. Number cells in the anchor column (leaving out the header) consecutively.
    Numbers (1-11) in an Excel spreadsheet column
    1. Select all rows and columns (Ctrl + A), open the “Data” tab and select Sort.
    Excel spreadsheet showing data under the following headers: Name, Adress, Phone, and DOB. The "Sort" tool in the "Data" tab is also highlighted.
    1. Open the Sort by drop-down menu and select your dataset’s first column (not the anchor column. Check the My data has headers box if your worksheet has a fixed header.
    Sort window in Excel highlighting the "My data has headers" checkbox and "Sort by" options

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

    1. Select the Add Level button in the top corner of the “Sort” dialog box.
    "Add Level" button in Excel Sort tool
    1. Open the Then by drop-down menu for the newly-added level and select the subsequent column.
    Sorting data by columns in Excel
    1. Create new “Sort Levels” for all columns in your worksheet and add them to the “Then by” drop-down menu.
    Sorting data by columns in Excel
    1. Select OK to proceed.
    Sorting data by columns in Microsoft Excel
    1. Excel will move all blank rows to the bottom of the spreadsheet. Select the rows, right-click, and select Delete.
    Deleting blank rows in an Excel spreadsheet
    1. Now, you want to restore the rows to their initial order. Select all columns (Ctrl + A) and Sort in the “Data” tab.
    Sorting data in an Excel spreadsheet
    1. Open the Sort by drop-down menu, choose the anchor column (Column A), and select OK.
    Sort tool in Microsoft Excel

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

    Column A in an Excel spreadsheet

    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.

    1. Select the columns you want to inspect for blanks and select Filter in the “Data” tab.
    Filter tool in Microsoft Excel
    1. A drop-down arrow will appear next to each title in the header row. Select the drop-down icon next to the first column header/title. Uncheck Select All, select only the Blanks checkbox, and select OK.
    Applying Blanks filtering to an Excel spreadsheet
    1. Do the same for each column in the worksheet—open the title drop-down and select only the Blanks checkbox.
    Applying Blanks filtering to an Excel spreadsheet

    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.

    1. Excel will highlight the (row) number for empty rows in blue. Select those rows, right-click, and choose Delete Row on the context menu.
    Highlight showing empty rows in Microsoft Excel
    1. After deleting the empty rows, select the Filter button in the “Data” tab. That’ll remove the filter and restore your spreadsheet without the blank rows.
    Filter tool in Excel

    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.

    1. Select all the data on the sheet and press the F5 key to open the Go To window. Select the Special button to proceed.
    "Special" button in Excel's Go To window
    1. Select Blanks from the options and select OK.
    "Go To Special" windows in Excel

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

    1. Select the Delete drop-down icon on the “Home” tab and choose Delete Sheet Rows.
    "Delete Sheet Rows" option in Excel Home tab

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

    Excel spreadsheet with data in the following columns: Name, Address, Phone, and DOB

    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.