9 Ways to Delete Unused Rows in Excel

Unused rows can be present in a dataset for various reasons. These blank cells are often unwanted as it makes it hard to get a compact view of the dataset. This article will discuss about ways how we can delete unused rows in Excel.

📁 Download Excel Workbook

Download the Excel workbook below

Learn to Delete Unused Rows in Excel with These 9 Suitable Approaches

To demonstrate things easily, we consider a dataset where we have different models of cars from the year 2021-2022 that are of different categories and brands. In this dataset, we have some blank cells in the intermediate cells between different categories. We will use this dataset to remove these unused cell rows in Excel. The dataset is as follows:

The most conventional method of deleting is probably the use of Context Menu. In this approach, we will be selecting the rows that have empty cells and remove the rows altogether using the conventional Context Menu approach. The steps are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select one of the rows containing the blank cells by clicking in row headers present on the left side with numbers.
• Then we press Ctrl and select all the row headers that we need to eliminate, that is all rows that contain blank or unused cells.
• After selecting the row headers that we need to eliminate, we right click and Context Menu will appear.
• From the Context Menu, we select the Delete option.

• Thus, the unused rows will be deleted. The results will be visible in the following image.

📕 Read More: 5 Ways to Remove Blank Rows Using Formula in Excel

This approach is an alternative way of deleting blank rows in Excel. In this method, we will use the Go To Special command to select the blank cells and then eventually delete them. The steps in this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• First, we select the cell range B5:E17.
• Then we click on the Find & Select option and go to the Go To Special Option from the Editing Section under the Home tab.

• After that, we select Blanks and press OK.

• This will select all the unused cells within the specified range of the dataset that we selected earlier.
• Next, we move to Home tab and select Delete Sheet Rows from the Delete section under Cells section of the Ribbon.

• Finally, the unused cells will go away and the resultant output will look like the following image.

This method discusses hiding unused cells rather than deleting them. An Excel sheet has 1048576 rows. We might require rows to be hidden outside our dataset. This approach will do that using keyboard shortcuts. These are the steps we have to follow for that.

⬇️⬇️ STEPS ⬇️⬇️

• In the beginning, we place our cursor on any unused cell outside our dataset.

• After that, we press Ctrl+Down Arrow to select all the rows below the selected cell. This will select rows upto row number 1048576.
• Then we press Shift+space to select the remaining cells of the selected rows.
• After that, we right-click and select Hide option.

• This will hide all the selected rows outside the dataset.

In this approach, we will use Filter feature under Data tab to remove unused rows in Excel. The steps for this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• First, we select the dataset in the cell range B5:E17.
• Then we go to the Filter option under the Sort and Filter section of the Editing option under the Home tab.

• Column Headers will have a drop-down icon now. We click on the icon and deselect all options except Blanks option and press OK.

• The blank or unused cells of the table will show up.

• Now we pick all unused rows by clicking on row headers by pressing Ctrl and then do a right-click to get the Context Menu. From the Context Menu, we select the Delete Row option.

• Next, we select one of the drop-down icons in the header and check Select All option, and press OK.

• The resultant table will be free of all unused cells like the following image.

We can use Advanced Filter Feature as well to get rid of unused rows in Excel. To do this, we need to pick any criteria that will support all the cells except the blank ones. The steps for this approach are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• At first, we have to specify a filter criteria range. To do so, we have to pick a column heading at first. We use the heading Category in cell G5.
• Then on cell G6, we need to specify criteria. We write the following criteria on G6 and press Enter.

• Now, we select the whole range of data in cells B5:E17.
• Then we go to the Data tab and select Advanced from the Sort and Filter section.
• An Advanced Filter box will appear. We select Copy to another location.
• We select the List range by selecting the cells B5:E17. In the criteria range box, we specify the criteria by selecting G5:G6.
• In the Copy to box, we select a cell reference where we want to paste the filtered result. In this case, we select cell I5 and press OK.

• Now we will get a copy of the selected range of cells without the unused cells in a new location. We do the necessary formatting and the output will hence look like the following image.

In this approach, we will use the Sorting Feature to delete unused rows in Excel. The steps in this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• We select the dataset within the cell range B5:D17 and go to the Data tab.
• Then, under the Sort and Filter section, we select the Ascending Sorting (A to Z).

• This will bring down all unused cells at bottom of that table. Now we select all unused cells and under the Home tab, from the Cells section, we select the option named Delete Sheet Rows.

• The resultant output will look like the following image.

In this approach, we will use Find feature that is normally used to find any particular data in Excel. We can find unused blank spaces using this feature as well. The steps for this approach are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• We select the whole dataset within the cell range B5:D17.
• Then we go to the Home tab and select Find option from the Find and Select option in Editing section of the Ribbon.

• After that, Find and Replace box will come. In Find what box, we keep it blank as it is. Then we click on Match entire cell contents and then press Find All.

• All unused blank rows will appear in the bottom portion of Find and Replace box. We press the buttons Ctrl+A in order to select such rows and right-click to select the Delete option from Context Menu.

• A Delete box will appear. We select the Entire row and press OK.

• The resultant output will look like the following image.

Filter function can filter data on the basis of a specified criterion. To remove unused cells, we will pick a criterion that satisfies all the data when we use Filter function. We can use this Filter function to delete unused rows in Excel with the help of the following steps:

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select the cell G6 and write the following formula down.

• After that, we press Enter.
• A dataset free of unused cells will be formed from the cell G6.

• Then by inserting column headers and doing proper formatting, the final results will look like the image below.

We can use Power Query and we can access from Get and Transform Data section of the Ribbon under Data tab to delete unused rows in Excel. The steps for this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• In the beginning, we go to the Data tab and select From Table/Range from the Get & Transform Data section.

• A Create Table pop-up box will appear. We select the range of our dataset B5:E17 and check the option named My Table has headers.

• A Power Query Editor will pop up.
• From the Remove Rows option in the Reduce Rows section, we select the option Remove Blank Rows.

• Then we press Close & Load option.

• A new sheet will open with the dataset free from all unused rows.

📄  Important Notes

🖊️  The Power Query option has been reorganized and now it can be accessed from the Get & Transform Data segment of the Data Menu.

🖊️  We can use the shortcut Ctrl + – to bring the Delete box to delete rows.

🖊️  Some data can vanish if we are not careful about the approach and data type. For instance, Go To Special command deletes the entire row where even one blank cell is present.

📝  Takeaways from This Article

You have taken the following summed-up inputs from the article:

📌  We can use various Sort and Filter commands in order to get rid of unused rows in Excel.

📌  If we want to remove or hide rows outside of our dataset, then we can follow the keyboard shortcut approach that can easily hide other cells in sheets.

📌  We can also use functions such as Filter function to delete unused rows in Excel.

Conclusion

In conclusion, the various approaches explained have hopefully helped you to better understand how we can delete unused rows in Excel. Additionally, if you want to ask any questions regarding the topic, feel free to comment down below in the comment section. For more Excel-related articles and solutions, visit our site ExcelDen.com

Related Article

• 5 Ways to Delete Every nth Row in Excel