In this article, I will introduce you to 7 easy ways to remove blank lines in Excel. If your Excel dataset contains unnecessary blank lines or empty rows, you may need to remove those. Because it not only creates confusion but also may become the reason for errors in calculation. So, we require those to be removed from our worksheet. Here, we will have the following effect when we remove the blank lines.
How to Remove Blank Lines in Excel: 7 Ways
In this article, we are going to show 7 handy ways to remove blank lines in Excel. Let’s say in our dataset we have data of different members of a club. Our dataset contains several blank lines. We will delete these empty rows by using 7 different techniques.
1. Manually Remove Blank Lines
This is the easiest method to remove blank lines in Excel. However, it is convenient only for small datasets. For large datasets, it becomes an inefficient method.
Steps:
- Secondly, right-click and from the available options choose Delete.
- After that, from the Delete prompt, first, choose Shift cells up and then press OK.
- As a result, we will have a dataset free of blank lines.
Read More: How to Delete Blank Cells and Shift Data Up in Excel
2. Applying Keyboard Shortcut to Remove Blank Lines
This method too is a manual one. However, it is faster than the previous method. This also becomes obsolete as the datasets become larger and larger.
Steps:
- At the beginning, choose the blank lines and press Ctrl+Minus(-).
- After that, select Shift cells up from the Delete prompt and press OK after that.
- Consequently, Excel will remove the blank lines from the dataset.
3. Using Go To Special Command to Remove Blank Lines
Here, we will use the Go To Special command. We will look for blanks in the Go To Special dialogue box.
Steps:
- Firstly, select the B4:E18 cell range.
- Then, go to Home >> Editing >> Find & Select >> Go To Special.
- As a result, a prompt will be on the screen.
- After that, from the prompt, first, choose Blanks.
- Then, press OK.
- As a result, all the blank spaces within the dataset will be highlighted.
- Thereafter, choose the blank rows or blank lines and right-click.
- Then, from the available options choose Delete.
- Now, choose the Shift cells up.
- Then, click OK.
- As a result, all the blank lines will be deleted.
Read More: How to Remove Blank Cells from a Range in Excel
4. Utilizing Find Command to Remove Blank Lines
In this instance, we will use the Find command to delete the blank lines. Here too, we will have to select the blank lines that we will delete.
Steps:
- To begin with, select the B4:E18 cell range.
- Then, select Home >> Editing >> Find & Select >> Find.
- Consequently, a prompt will be on the screen.
- In the prompt, first, keep the Find what box blank.
- Secondly, mark the Match entire cell contents box.
- Finally, click Find All.
- As a result, the list of all the blank cells will be on the screen.
- From the list, choose the blank lines and then click on Close.
- Consequently, the blank lines will be highlighted on the dataset.
- Finally, remove the blank lines as the previous methods.
5. Implementing Sort Command to Remove Blank Lines
In this method illustration, we will sort our data by using the Sort command and then delete the blank rows.
Steps:
- First, choose the B4:E18 cell range.
- Thereafter, go to Home >> Editing >> Sort & Filter >> Sort A to Z.
- As a result, it will sort the dataset separating the blank lines from the non-blank ones.
- Finally, delete the blank lines as we did in the previous method to get a blank line-free dataset.
6. Utilizing Filter Command
In this example, we will use the Filter command to get rid of the blank lines from our dataset.
Steps:
- Firstly, choose the B4:E18 cell range.
- After that, choose Data >> Sort & Filter >> Filter.
- As a result, all the column headers will have a filter handle.
- Now, choose any of the filter handles first.
- Afterward, mark the (Blanks) box.
- Finally, press OK.
- As a result, all the blank cells will be filtered.
- From the filtered data, choose the blank lines and delete them by following the Previous Method.
- After that, click on the filter handle again.
- Then, mark the Select All box and click on OK.
- Consequently, we will have a dataset free of blank lines.
Read More: How to Remove Blank Cells in Excel
7. Applying FILTER Function
In this final method, we will use the FILTER function to remove blank lines from our dataset. This is the quickest and most efficient method to do so.
Steps:
- First, click on the B20 cell and type,
=FILTER(B5:E18,B5:B18<>"")- Then, press Enter.
- As a result, we will get a blank line-free dataset.
Download Practice Workbook
Conclusion
You can remove blank lines in Excel by using any of the described methods. If you face any confusion about any of these methods, please leave a comment.
Related Articles
- How to Delete Blank Cells and Shift Data Left in Excel
- How to Ignore Blank Cells in Range in Excel
- How to Leave Cell Blank If There Is No Data in Excel
- How to Remove Unused Cells in Excel
<< Go Back to Remove Blank Cells | Blank Cells | Excel Cells | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
