Easy Ways to Remove Blank Rows in Microsoft Excel Worksheets
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 Microsoft Excel worksheets manually or you can use several tricks to remove multiple blank rows quickly.
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 >
1. Deleting a single blank row
If you want to delete one row, you can delete the row manually:
- Selct a row. Click its heading or select a cell in the row and press Shift + spacebar.
- Right-click the selected row heading. A drop-down menu appears.
- Select Delete.
Instead of right-clicking, you can press Ctrl + – (minus sign at the top right of the keyboard).
2. Deleting multiple blank rows
If you want to select a delete blank rows, you can select them in a few ways:
- 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.
If you want to delete multiple rows, you can select them and delete theme manually:
- Select a row or rows.
- Right-click one of the row headings. A drop-down menu appears.
- Select Delete.
Instead of right-clicking, you can also press Ctrl + – (minus sign at the top right of the keyboard).
3. 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:
- Select the entire range of data (not just the column you want to sort).
- Click the Data tab in the Ribbon.
- Select Sort in the Sort & Filter group. A dialog box appears. Assuming you have a header row, select My data has headers.
- 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.
- Click OK. Blank rows will now appear at the bottom of the data set and can be ignored.
Below is the Sort dialog box:
4. 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:
To find and remove blanks using Go to Special:
- 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).
- 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.
- Select Blanks in the Go to Special dialog box and click OK. Excel will select all of the blank cells within the range.
- Right-click one of the selected blank cells and select Delete. A dialog box appears.
- Select Entire Row and click OK to confirm the delete.
The Delete dialog box appears as follows:
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):
- Select the range of cells that includes all of the data in the data set.
- Click the Data tab in the Ribbon.
- Select Filter in the Sort & Filter group. Alternatively, you can press Ctrl + Shift + L.
- Click the arrow beside the field name with the blank cells in rows you want to delete.
- Turn off or de-select Select All
- Select Blanks. You will likely need to scroll down to select Blanks.
- Select the row headings of the filtered rows.
- Click the Home tab in the Ribbon and select Delete in the Cells group. A drop-down menu appears.
- Select Delete Sheet Rows.
- 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:
You will now be able to sort, filter and create pivot tables with the list.
Did you find this article helpful? If you would like to receive new articles, join our email list.
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 email@example.com if you'd like to arrange custom training on a date that's convenient for you.
Copyright 2021 Avantix® Learning
You may also like
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.
In Microsoft Excel, when you enter data or a formula, it appears in the Formula Bar. If you are writing longer formulas, it can be helpful to expand the Formula Bar.
You can quickly fill blank cells in a range of data in Excel with dashes, zeros or other number or text values. Blank cells can be problematic if you want to use a data set as the source for a pivot table.
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 | firstname.lastname@example.org