So, you just removed a lot of irrelevant data from your Excel spreadsheet, but now you’re left with a huge chunk of blank rows. What should you do? Fortunately, this is such a common occurrence that Excel has tons of built-in ways to deal with the problem and delete blank rows as needed.
This isn’t the only reason to get rid of blank rows, either. In fact, you may intentionally add blank rows to your data, only to decide later on that you either don’t need them or you want to rearrange the layout of your dataset. In any case, to ensure that your spreadsheet looks the way you want it to, you’ll need to know how to get rid of these empty rows.
In today’s guide, we’re going to go over some of the most common ways to get rid of multiple blank rows in your Excel spreadsheet. And remember, if you need additional help with Excel, you can always reach out to the trained IT experts at Geeker. Now let’s get started!
Manual Removal
This is by far the most common method, especially if you need to delete random blank rows as you’re working. While it may not be the most efficient way to get through a lot of blank rows at once, manual removal is incredibly easy. Here is what you’ll need to do:
- Identify a blank row in your dataset
- Select the row number on the left of the table
- Go to Home > Delete > Delete Sheet Rows
Alternatively, you can manually delete a row using this method:
- Identify one or more blank rows in your dataset
- Highlight the row or rows
- Right-click on any cell in the selected area
- Select “Delete Rows”
Note: Either of these methods will work for nearly all of the deletion methods in this guide, however, we will continue to use the first set of steps as the primary deletion method.
Go To Special
The “Go To Special” method is only slightly more complex than the manual method, but it is best if you want to delete all the blank rows in a larger dataset. To use this method, simply follow these steps:
- Press CTRL + A to select your entire spreadsheet or highlight the relevant range
- Go to Home > Find & Select > Go To Special (You can also press F5 or CTRL + G to bring up the Go To dialog box and then select “Special…”)
- Choose “Blanks” and click “OK” (this will highlight all blank cells)
- Go to Home > Delete > Delete Sheet Rows (or the alternative outlined in the previous section)
Find Command
The Find command is just a variation of the Go To Special method, but it will deliver the same results. To remove blank rows using the Find command, just follow these steps:
- Select a column header
- Go to Home > Find & Select > Find (or press CTRL +F)
- Choose the Advanced Options
- Leave the “Find What” box blank
- Check the box next to “Match entire cell contents”
- For the Within menu, select “Sheet”
- For the Look In menu, select “Values”
- Click on “Find All”
- Select all of the blank cells using CTRL + A
- Go to Home > Delete > Delete Sheet Rows
As you can see, this method isn’t quite as efficient as Go To Special, but it can still get the job done if you’re dealing with a relatively large chunk of data.
Filter Method
As you’re probably starting to notice, most of these methods involve the same basic deletion process toward the end. The primary issue is finding the rows that you want to delete. The Filter method is yet another way to filter out the cells that contain some kind of data so that you can focus on the rows with only blank cells (and then delete them). You’ll need to follow these steps to use the Filter method:
- Select your entire spreadsheet (CTRL + A) or the range of cells you want to filter
- Go to Data > Filter (or use the filter shortcut CTRL + Shift + L)
- Click on a Filter toggle on one of your column headings
- Uncheck the box next to “Select All”
- Check the box next to “Blanks”
- Select one of the highlighted cells
- Go to Home > Delete > Delete Sheet Rows
- Turn off the filter using the filter shortcut CTRL + Shift + L
If you use the filter shortcut, this is actually one of the fastest methods for getting rid of a lot of random blank rows all at once.
Filter Function
If you prefer using built-in Excel functions to fast-track various tasks, then the Filter function is probably the best way for you to delete blank rows. However, if you just want to delete a few blank rows quickly or you simply don’t like using functions, it’s best to steer clear of this method, as it can take some getting used to. In any case, the formula for the Filter function looks like this:
=FILTER(Array,Include<>””)
To better understand this formula, let’s look at each of the parts. The =FILTER part just tells Excel what kind of function you want to do (in this case, filtering). Array tells Excel what range of cells you want to examine. Finally, Include tells Excel what you want to include in the filtered results.
While all of these parts are important, it’s the last two sets of symbols that help you get rid of blank rows. In Excel function terms, the <> input means “Not equal to,” while two quotation marks back to back mean “Blank.”
The easiest way to demonstrate this method is with an example. Let’s say you’ve got the following dataset and you want to recreate it without any of the blank rows:
| A | B | C | D | E | F | |
| 1 | Pear | $1.00 | ||||
| 2 | Apple | $1.20 | ||||
| 3 | ||||||
| 4 | Coconut | $2.40 | ||||
| 5 | Lemon | $0.90 | ||||
| 6 | ||||||
| 7 | Strawberry | $1.60 |
Click on a cell outside of your range (like D1) and begin typing the following formula:
=FILTER(A1:B7,A1:A7<>””)
Now, your table should look something like this:
| A | B | C | D | E | F | |
| 1 | Pear | $1.00 | Pear | $1.00 | ||
| 2 | Apple | $1.20 | Apple | $1.20 | ||
| 3 | Coconut | $2.40 | ||||
| 4 | Coconut | $2.40 | Lemon | $0.90 | ||
| 5 | Lemon | $0.90 | Strawberry | $1.60 | ||
| 6 | ||||||
| 7 | Strawberry | $1.60 |
As you can see, this method keeps the original dataset and creates a copy of it, minus all of the blank rows in the middle.
Note: This method only works if you’re using Excel Online or Excel for Office 365.
Sort Command
Much like filtering, sorting is another great way to separate cells with and without data. To use the Sort command, just follow these steps:
- Select your entire spreadsheet (CTRL + A) or the range of cells you want to filter
- Go to Data
- Select “Sort” (ascending or descending)
This method will put all of the blank rows at the bottom of the spreadsheet, effectively “deleting” the blank rows from your dataset. In a sense, this method isn’t actually deleting the rows at all. Instead, it’s simply rearranging your data so that blank rows are no longer in between the rows with data. Just keep in mind that this method will not work well if your data needs to remain in a specific order.
Need help from dependable Excel experts? Reach out to Geeker today for on-demand IT and software solutions!