Deleting blank rows in an Excel data set isn't difficult, but Excel 2016 is the only version that offers predictable results.
Blank rows are easy to acquire--whether you're importing data from a foreign source or introducing them yourself during the input process. Regardless of how you end up with them, it's best to rid a data set of blank rows. Excel uses blanks to determine ranges, so Excel's selection-based features won't work as expected if there are blank rows anywhere in your data set. In this article, I'll show you five easy ways to delete blank rows.
Before we go any further, I have a warning: when deleting entire rows, be careful. There might be data off screen that you don't see. Deleting an empty row in your data set will also delete out-of-sight data in the same row.
I'm using Excel 2016 (desktop) on a Windows 10 64-bit system. The Get & Transform Data tip is available only in Excel 2016. If you're using an earlier version, run a quick search on Power Query Add-in. The other tips will work in earlier versions of Excel. None of these tips work in the browser version. You can work with your own data or download the demonstration .xls and .xlsx files.
1. Get & Transform Data
Excel's Get & Transform Data feature provides a quick way to retrieve data. While doing so, there are a number of ways to manipulate the data, and one of those will delete blank rows. Fortunately, it's easy to use, but it does require a Table object; if your data is a normal data range, the feature will convert the range for you--which you might not want.
SEE: 13 handy Excel data entry shortcuts (free PDF) (TechRepublic)
To begin, click anywhere inside the data set, and click the Data tab. Then, do the following:
- In the Get & Transform Data group, click From Table/Range. At this point, Excel will fail to find the entire data set because of the empty rows.
- Change the default range to A1:H20 (Figure A), check the My table has headers option, and click OK.
- Excel will launch the Power Query Editor to retrieve the data.
- In the Reduce Rows group (Home tab for Power Query), click the Remove Rows option and select Remove Blank Rows (Figure B). Power Query will remove the blank rows but not the rows with blank cells.
- Click Close & Load in the Close group, and Power Query will copy the modified data set to a new sheet in Excel. At this point, the data is a Table object (Figure C), which you can convert to an ordinary range if you prefer.
Adjust the range.
Remove the blank rows.
Power Query returns a Table object with no blank rows.
Although you can convert the Table object into a normal range, you haven't changed the original data set. That is probably the behavior that will influence whether or not you choose this feature.
If you want to remove the blank rows from the original data set, you might try the Filter feature, but use caution. You must choose the filter column wisely. We can illustrate this problem best with a quick example (using an ordinary range, not a Table object):
- Click the Data tab, and then click Filter in the Sort & Filter group.
- Use the new Order ID dropdown to choose your filter: uncheck the (Select All) option and then check (Blanks), as shown in Figure D. If the (Blanks) option isn't available, start over but select the range first.
Filter for blanks.
Here's where the problem lies (Figure E). Because there's no Order ID value for the record in row 7, the filter matches that record. The row isn't empty, but the Order ID value is. (I deleted the Order ID value in row 7 to make the example more effective.)
This filter matches all records where the Order ID value is blank.
If the filter returns all empty rows, it's a simple matter to select the filtered set and press Ctrl+- (the minus key). When Excel prompts you to delete the entire rows, click OK. On the other hand, if you want to delete only the blank rows and keep the incomplete records, you can select a non-contiguous set of rows by holding down the Ctrl key while clicking row headers (see Figure E). Then, press Ctrl+-. This method requires a bit of hoop-jumping, but it works with the original data.
This next suggestion is super simple, but it has consequences--you will lose the data's original order. Simply sort the data set alphabetically. Let's try that now:
- Select A2:H20.
- Click Sort in the Sort & Filter group (on the Data tab) to sort the blank records to the bottom. As you can see in Figure F, the results aren't exactly what you might expect.
Blanks in Order ID sort to the bottom but so does the record with no Order ID value.
If you can live with the sorted results, this method is perhaps the simplest. If you need to return the original order and you have a column that will return the data to its original order--leaving the blank rows at the bottom out of the sort--sorting might work for you. It's still a lot of work and complicated by the incomplete row problem (now row 19) that you see in Figure F.
4. Go To
You can use Excel's Go To feature to select all the blank cells in a selected range. If the results are empty rows, you can then press Ctrl+- to delete those rows. Our example is simple, but let's see what happens:
- Select A2:H40.
- Press F5 and then click Special in the resulting dialog.
- Select Blanks (Figure G) and click OK. The results are shown in Figure H--a non-contiguous selection that includes two blank records and three additional blank cells.
Select all the blanks in the selected range.
The blanks selection might include blank cells mixed in with the empty rows.
While holding down the Ctrl key, you can click the blank cells to remove them from the non-contiguous selection and then press Ctrl+- to delete only the empty rows. Again, it's a lot of work, especially if you're working with a large data set. However, you are working with the original data set and maintaining its order.
SEE: Tap into the power of data validation in Excel (free PDF) (TechRepublic)
By now, you probably realize that you could quickly create a non-contiguous selection and delete it--but doing so works best when the data set is small. Hold down the Ctrl key and click each empty row's header cell. Once you've added all of the empty rows to the selection, press Ctr+-. You can use this selection technique with a large number of records, but it'll be tiresome.
With five methods, you might expect at least one to get the job done as expected with the original data, but that's just not the case. Next month, I'll show you a macro that removes blank rows from the selected range.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at email@example.com.
- How to combine and analyze data from multiple data sets using Excel Power Pivot (TechRepublic)
- How to number headings in a Word 2016 document (TechRepublic)
- Office Q&A: How to stop Excel's paste task from overwriting destination cell's format (TechRepublic)
- 5 ways to insert a checkmark into Office documents (TechRepublic)
- How to control spacing and alignment in a numbered list in Microsoft Word (TechRepublic)