Prantick Bala
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 of 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.https://www.exceldemy.com/how-to-delete-blank-cells-in-excel-and-shift-data-up/
Download Practice Workbook
7 Ways to Remove Blank Lines (Rows) in Excel
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:
- Firstly, choose the blank lines by holding Ctrl.
- 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 in Excel and Shift Data Up
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.
Read More: How to Find Blank Cells in Excel (8 Easy Ways)
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 Apply Conditional Formatting in Excel If Another Cell Is Blank
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 OK.
- 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.
Read More: How to Find and Replace Blank Cells in Excel (4 Methods)
Similar Readings
- Excel VBA: Check If Multiple Cells Are Empty (9 Examples)
- How to Deal with Blank Cells That Are Not Really Blank in Excel (4 Ways)
- Fill Blank Cells with 0 in Excel (3 Methods)
- How to Autofill Blank Cells in Excel with Value Above (5 Easy Ways)
- How to Skip Blank Rows Using Formula in Excel (8 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.
Read More: How to Remove Blanks from List Using Formula in Excel (4 Methods)
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 Blank 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 Set Cell to Blank in Formula in Excel (6 Ways)
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.
Read More: Find If Cell is Blank in Excel (7 Methods)
Read More: How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas
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.
