Maruf Islam
Often, while working in Excel, we encounter unused rows or blank rows in our dataset. In this article, we demonstrate how to delete unused rows in Excel using multiple features, keyboard shortcuts as well as FILTER function.
Let’s say we have a dataset where users input Product Sale data. Somehow users leave unused or blank rows while entering data in the dataset as we can see in the below image.
Download Excel Workbook
8 Easy Ways to Delete Unused Rows in Excel
Method 1: Using Delete Option from the Context Menu
If we have a dataset of a handful of rows and it contains a few numbers of unused rows, we can easily use the manual method to delete unused rows. In that case, we use the Context Menu to delete the manually selected rows.
Step 1: Keep pressing the CTRL key then Click on the Blank Rows you want to delete entirely. You can select the entire row by clicking on the row headers.
🔄 CTRL + Mouse Left Click to select multiple blank rows.
Step 2: After selecting multiple unused rows, Right Click on any selected rows. The Context Menu appears. Select Delete.
🔄 Use Mouse Left Click then Select DELETE to delete unused or blank rows.
The above two steps give you a result similar to the image below where all the unused rows get omitted.
Read More: How to Delete Rows in Excel: 7 Methods
Method 2: Using Go To Special Feature to Delete Unused Rows in Excel
Selecting unused rows manually works with a small dataset, what if we have a lengthy dataset with numerous blank rows. In this case, Excel’s Go To Special feature works pretty well.
Step 1: Go to Home Tab > Select Find & Select (In the Editing section) > Click on Go To Special (from the options).
Step 2: The Go To Special dialog box appears. In the dialog box, Select Blanks then Click OK.
🔄 Use CTRL + G keyboard shortcuts to bring out the Go To Special dialog box.
Step 3: Choosing Blanks in the Go To Special dialog box selects all the blank rows in the dataset. Go to Home Tab > Select DELETE (from the Cells section) > Click on Delete Sheet Rows.
🔄 Use alternative ways such as
⏩ Right Click on any selected Blank rows then Choose DELETE from the Context Menu.
⏩ CTRL + -.
to delete selected blank rows from the dataset.
Executing all the steps lead you to a similar picture depicted below.
Read More: How to Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)
Method 3: Using Filter Feature to Delete Unused Rows in Excel
In the Data tab, Excel provides the Filter feature (inside Sort & Filter section) to filter cell entries. We can simply filter out the unused rows using the Filter feature.
Step 1: Select the range then Go to Data Tab > Click on Filter (in Sort & Filter section).
Step 2: The Filter Icon appears in the column headers. Click on any Filter Icon > Deselect all the entries except the Blanks option > Afterwards Click OK.
🔄 Use keyboard shortcuts CTRL+SHIFT+L to bring out the Filter Icons in the column headers.
Step 3: Executing Step 2 leads to a similar depiction as the following picture. Only the Blank rows existing in the dataset appear.
Select all the unused rows using Row Headers (By dragging the Mouse Cursor along the Row Headers) > then Right-Click on any selected row (Context Menu appears) > Select Delete Row.
This leads to a resultant image as shown in the picture below.
Step 4: Again, Click on the Filter icon in any column header > then Checked the Select All option > Click OK.
Step 5: All the rows except unused ones appear and you have deleted all the Blank rows.
Read More: How to Filter and Delete Rows with VBA in Excel (2 Methods)
Method 4: Using Keyboard Shortcuts (Hide Rows from Sheet)
In the previous methods, we demonstrate how we delete unused or blank rows from inside the dataset. It’s obvious that deleting unnecessary blank rows makes the dataset look tidy. But what if we want to delete blank rows outside the range?
Deleting unused rows outside any range doesn’t really work out because we’ll need them eventually. That’s why we can hide rows outside the range to offer a clear view of our dataset.
Step 1: Place the Cursor on any cell outside the range > then Press CTRL+SHIFT+ Down Arrow to select all the rows up to row number 1048576 (last row of an Excel worksheet).
Step 2: Now, Press keyboard shortcuts SHIFT+SPACE to select all the respective columns to the rows.
Step 3: Right-Click on any selected cell > the Context Menu appears > Select Hide.
After Clicking on Hide option, you can see it hides all the unused rows below the dataset as depicted in the picture.
We demonstrate this method to offer users a glimpse of how they can make their dataset to the point and easy to work with.
Read More: Excel Shortcut to Delete Rows (With Bonus Techniques)
Similar Readings:
- How to Delete Multiple Rows in Excel Using Formula (5 Methods)
- Delete Multiple Rows in Excel at Once (5 Methods)
- How to Delete Hidden Rows in Excel VBA (A Detailed Analysis)
- Delete All Rows Below a Certain Row in Excel (6 Ways)
- How to Delete Infinite Rows in Excel (5 Easy Ways)
Method 5: Using Excel Sorting Feature
Excel’s Sorting feature works similar to the Filter feature but doesn’t offer any particular selection type. We can sort entries in a dataset in ascending or descending order. And that pushes the unused rows to the bottom of any dataset.
Step 1: Select the entire dataset then Go to Data Tab > Click on Ascending (A to Z) or Descending (Z to A) Sorting (from Sort & Filter section).
Step 2: Sorting pushes all the unused rows to the bottom of the dataset. Select all the unused rows afterwards Go to Home Tab > Select Delete (inside Cells section) > Click on Delete Sheet Rows.
🔄 Use the alternative way to delete the unused blank rows.
⏩ Right-Click (CTRL+- can also be used) on the selected Blank rows > then the Context Menu appears, Select Delete.
⏩ A Delete dialog box pops up. Click on Entire row > after that Click OK.
Step 3: Following all the steps delete the unused rows as shown in the below picture.
Read More: How to Delete Filtered Rows in Excel (5 Methods)
Method 6: Using Find Feature to Delete Unused Rows in Excel
Similar to Filter or Sort features, the Find feature can select all the blanks in a dataset. We know the Find feature normally finds something and, in this case, we find Blanks.
Step 1: Highlight the entire dataset then Go to Home Tab > Select Find & Select (inside the Editing section) > Click on Find.
Step 2: The Find and Replace dialog box appears. In the Find and Replace dialog box; inside the Find section, Leave the Find What option Blank > Checked the Match entire cell contents > Click on Find All.
Step 3: All the blank rows appear below the Find and Replace window. Press CTRL+A to select all the unused rows inside the dataset as shown in the screenshot below.
Step 4: Right-Click on any blank rows > the Context Menu comes up > Select Delete.
Step 5: The Delete dialog box pops up. In the dialog box, Click on Entire row > then Click OK.
🔄 You can use other alternative ways to Delete rows as described in Method 2.
In the end, executing all the sequential steps deletes all the unused rows as depicted in the following picture.
Read More: How to Use VBA to Delete Empty Rows in Excel
Method 7: Using Filter Function
The FILTER function filters a range of data based on a given criterion and results in matched data. One of the downsides of the FILTER function is only applicable to Microsoft Excel 365.
The syntax of the FILTER function is
FILTER (array, include, [if_empty])In the syntax,
array; takes a range you want to filter.
include; a Boolean array works as a criterion.
[if_empty]; return value whenever there is no value to return [Optional].
We can use the FILTER function to delete the unused rows in a dataset.
Step 1: Paste the following formula in any adjacent blank cell (i.e., I5) then Press ENTER.
=FILTER(B5:G19,E5:E19>10,"NO")Inside the formula,
B5:G19=array
E5:E19>10=include
“NO”=[if_empty]
Step 2: A blank row less dataset appears starting from cell I5 as shown in the picture below.
After formatting and inserting column headers the whole dataset looks like the below screenshot devoid of unused rows.
Read More: How to Filter Data and Delete Rows with Excel VBA (5 Examples)
Method 8: Using Advanced Filter Feature
Similar to the FILTER function, the Advanced Filter feature deletes all the unused rows by pasting the dataset in another location without blank rows.
However, we have to pre-set a criterion to do so.
Step 1: Insert the following criterion in any cell (i.e., H5) afterwards Hit ENTER.
=B5<>""The criterion says it’ll match entries before or after the cell reference B5 (i.e., Order Date 6 Nov, 21)
Step 2: Select the entire dataset then Go to Data Tab > Select Advanced Filter (from Sort & Filter section).
Step 3: The Advanced Filter dialog box pops up. In the dialog box, Click on Copy to another location option. And Perform the below sequence,
1. List range will be automatically selected as B4:G19.
2. Select Criteria range (i.e., H4:H5).
3. Select Copy to the cell I5.
Click OK.
In a moment, the Advanced Filter deletes all the unused rows and paste the data in our desired location similar to the following picture.
Related Content: How to Delete Alternate Rows in Excel (5 Quick Ways)
⧭ Keep in Mind
The above discussion demonstrates methods to delete the entire rows from the dataset. Sometimes, without understanding the data types we perform methods that hamper our raw data. In order to keep the dataset intact we have to perform methods that suit our dataset well.
For example, if we perform the Go To Special method (demonstrated in Method 2) for the below dataset, we’ll encounter some data missing from the dataset.
The above dataset has no entire blank rows. After performing the Go To Special Method, it selects only the blanks as shown in the following picture.
However, if we delete the selected blanks by ways described in Method 2, it deletes the entire rows and leaves us with the following result.
So, keep in mind that we have to choose a method to delete unused rows according to your data type.
Conclusion
In this article, we demonstrate multiple Excel features, Keyboard Shortcuts, and the FILTER function to delete unused rows in Excel. You can use any of the methods according to your data type. Hope the above-described methods of deleting unused rows motivate you to use the features more efficiently. If you have further queries or feedback, please let me know in the comment section. You can check out my other articles on the Exceldemy website.
