Dataset Overview
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 methods.
Method 1 – Manually Remove Blank Lines
This method is straightforward but best suited for small datasets.
Steps
- Select the blank lines by holding down the Ctrl key.
- Right-click and choose Delete from the available options.
- In the Delete prompt, select Shift cells up and click OK.
- Your dataset will now be free of blank lines.
Read More: How to Delete Blank Cells and Shift Data Up in Excel
Method 2 – Applying Keyboard Shortcut
Although manual, this method is faster than the previous one.
Steps
- Select the blank lines.
- Press Ctrl + Minus (–).
- Choose Shift cells up from the Delete prompt and click OK.
- Excel will remove the blank lines.
Method 3 – Using Go To Special Command
Steps
- Select the cell range (e.g., B4:E18).
- Go to Home > Editing > Find & Select > Go To Special.
- In the prompt, choose Blanks and click OK.
- All blank cells within the dataset will be highlighted.
- Right-click on the blank rows or lines and choose Delete.
- Select Shift cells up and click OK.
- Blank lines will be deleted.
Read More: How to Remove Blank Cells from a Range in Excel
Method 4 – Utilizing Find Command
Steps
- Select the cell range (e.g., B4:E18).
- Go to Home, select Editing, click on Find & Select and choose Find.
- In the prompt, leave the Find what box blank.
- Check the Match entire cell contents box.
- Click Find All.
- A list of all blank cells will appear.
- Choose the blank lines from the list and click Close.
- Blank lines will be highlighted.
- Remove them as in previous methods.
Method 5 – Implementing Sort Command
Steps
- Select the cell range B4:E18.
- Go to Home, select Editing, click on Sort & Filter and choose Sort A to Z.
- This will sort the dataset, separating blank lines from non-blank ones.
- Delete the blank lines as described in the previous method to obtain a dataset without blank lines.
Method 6 – Utilizing Filter Command
Steps
- Choose the B4:E18 cell range.
- Go to Data, select Sort & Filter and click on Filter.
- All column headers will now have a filter handle.
- Click any filter handle and select the (Blanks) box.
- Press OK to filter out all blank cells.
- Delete the blank lines following the previous method.
- Click the filter handle again, mark the Select All box, and click OK.
- Your dataset will be free of blank lines.
Read More: How to Remove Blank Cells in Excel
Method 7 – Applying FILTER Function
Steps
- Click on cell B20 and insert:
=FILTER(B5:E18,B5:B18<>"")- Press Enter.
- You’ll get a dataset without blank lines.
Download Practice Workbook
You can download the practice workbook from here:
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 Blank Cells | Excel Cells | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
If you need to remove all rows (a similar approach will work with columns) without any values, but can’t sort or use filter, there is an alternate solution.
– Insert an empty column before the first column.
– Make the value of A1 `=concat(“somestring”; COUNTA($B2:$ZZ2))` (make sure this covers all columns). The string “somestring” is added to prevent you from removing all rows that contain a cell with the value 0.
– Drag the corner of A1 down to fill column A for all rows with at least one value.
– Go to Edit -> Find -> Find.
– Find “somestring0”. This should find the cells in all empty (except for column A) rows.
– Click ‘Find All’. Select all results in the resulting list,
– Go to Edit -> Delete. Select Entire Row and click OK.
– Remove column A.
Now all empty row will be removed from your sheet.
Hello Peter,
Thanks for your feedback and solution. Hopefully it would be useful for the users. That’s a clever approach! Using a concatenated string to identify empty rows while avoiding zeros is effective. After you locate those rows with “somestring0,” deleting them is straightforward. This method provides a great alternative when sorting or filtering isn’t an option.
Regards
ExcelDemy