How to Delete Unused Rows in Excel (8 Easy Ways)

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.

Dataset-Delete Unused Rows in Excel


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.

Context menu-Delete Unused Rows in Excel

🔄 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.

Context menu

🔄 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.

Result-Delete Unused Rows in Excel

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).

Go to special-Delete Unused Rows in Excel

Step 2: The Go To Special dialog box appears. In the dialog box, Select Blanks then Click OK.

Blanks

🔄 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.

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.

Result-Delete Unused Rows in Excel

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).

Filter feature-Delete Unused Rows in Excel

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.

Blanks

🔄 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.

Context menu

This leads to a resultant image as shown in the picture below.

Result by Filter feature

Step 4: Again, Click on the Filter icon in any column header > then Checked the Select All option > Click OK.

Select All

Step 5: All the rows except unused ones appear and you have deleted all the Blank rows.

Result

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).

Hide rows-Delete Unused Rows in Excel

Step 2: Now, Press keyboard shortcuts SHIFT+SPACE to select all the respective columns to the rows.

Select rows and columns

Step 3: Right-Click on any selected cell > the Context Menu appears > Select Hide.

Context menu

After Clicking on Hide option, you can see it hides all the unused rows below the dataset as depicted in the picture.

Result by Keyboard shortcutsWe 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:


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).

Sorting-Delete Unused Rows in Excel

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.

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.

Delete

A Delete dialog box pops up. Click on Entire row > after that Click OK.

Entire row

Step 3: Following all the steps delete the unused rows as shown in the below picture.

Result by sorting

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.

Find feature-Delete Unused Rows in Excel

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.

Find and replace window

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.

Select All

Step 4: Right-Click on any blank rows > the Context Menu comes up > Select Delete.

delete

Step 5: The Delete dialog box pops up. In the dialog box, Click on Entire row > then Click OK.

Delete dialog box

🔄 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.

Result by sorting

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]

FILTER function-Delete Unused Rows in Excel

 Step 2: A blank row less dataset appears starting from cell I5 as shown in the picture below.

Result by Filter FunctionAfter formatting and inserting column headers the whole dataset looks like the below screenshot devoid of unused rows.

Formatted result by filter function

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)

Advanced filter-Delete Unused Rows in Excel

Step 2: Select the entire dataset then Go to Data Tab > Select Advanced Filter (from Sort & Filter section).

Advanced filter

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.

Advanced filter dialog box

In a moment, the Advanced Filter deletes all the unused rows and paste the data in our desired location similar to the following picture.

Result by advanced filter

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.

Repercussion-Delete Unused Rows in Excel

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.

Selecting blanks

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.

Resultant values

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.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo