There are a few different ways you can remove blank rows in Excel. The simplest of which is to simply highlight the row headers that you wish to delete while holding Ctrl, then right-clicking and selecting Delete.
That’s really all there is to deleting blank rows manually. But, if you’d like a step-by-step guide with screenshots or to learn other methods such as sorting, using filters, and go to special, read on to learn more.
Table of Contents
Why Learn How to Remove Blank Rows in Excel
Blank rows can be a nuisance. They can make your data look unappealing and makes navigating around the data very difficult. It may also mess up some date points when you make charts. However, there are a lot of ways in which you can delete any of the empty rows in Microsoft Excel.
Before we take a look at the ways in which you can remove empty rows in Microsoft Excel, let’s finally settle the debate of which spreadsheet is better. Here are some of the key differences between Excel and Sheets in 2024.
How to Delete Blank Rows in Microsoft Excel Manually
This first way is one of the simplest ways using which you can delete empty rows in Microsoft Excel. However, this way is best suited for small sheets where you want to delete a few rows. This manual method can be cumbersome for larger spreadsheets with many rows.
Here is how to delete blank rows in Microsoft Excel manually:
- Open the worksheet in Excel where you want to delete blank rows.
- Hover your cursor over the row number for the row you want to delete. The cursor will change to a right-pointing arrow.
- Click on the row number of the row you wish to delete to select it. This will be indicated by a border towards the top and bottom of the selected row.
- To select multiple rows, click and hold Ctrl on your keyboard and click on the rows you want to delete.
- Now, right-click on any selected rows to open the drop-down menu.
- Here, click on the Delete button to remove empty rows in Microsoft Excel.
There are multiple spreadsheet programs with great functionality that you can use nowadays. Yet, most people either use Google Sheets or Microsoft Excel. Here is a guide on how to convert Sheets files to Excel.
How to Delete Empty Rows in Microsoft Excel Using Go to Special
Removing blank rows in Microsoft Excel manually is fine as long as you only have a few rows you wish to delete. However, having multiple rows can make it quite tricky. In this case, you cause a command to select blank cells. Here is how to remove blank rows in Microsoft Excel using go to special:
Open the worksheet where you want to delete blank rows.
- In Excel, click the Find & Select button in the top toolbar. In the drop-down menu, click on Go To Special.
- In the window that opens up, click on Blanks, which will select the blanks in the current workbook.
- Press the OK button to select all blank cells.
- With the cells selected, you can follow the steps discussed in the previous method to delete them. Right-click the row number bar and click the Delete option to do this.
Alternatively, you can go to Go To Special menu using the Ctrl + G and then click on Special button towards the bottom of the window. You can delete the cells by pressing the Ctrl + – and then selecting the Entire row option in the delete window.
Although this method is great for deleting blank rows in huge spreadsheets, this method does have one major flaw. If you have a row containing a single blank cell, this method will delete the entire row as well, which may delete rows containing useful information.
How to Get Rid of Empty Rows in Microsoft Excel Using Filters
Another way in which you can find and delete empty rows is by using filters. Here is how to delete unused rows in Microsoft Excel by using filters:
- Open Excel and head to the worksheet where you wish to delete the empty rows.
- Click and drag your cursor across the cell range to select it.
- With the cell range selected, click on Data in the main bar and then click on Filter in the Sort & Filter section. You can use the Ctrl + Shift + L keyboard shortcut to add filters to the selected range. This will add bottom pointing arrows in the cells in the top row.
- Click the arrow icon in the main row to add a filter to a column. We want to add the filter to the Total column in this case.
- Click and deselect all other cells by clicking on the (Select All)Â checkbox. Now, click and select the (Blanks)Â option. Click on OKÂ to save the changes.
- This will show the filtered options in the cell range. Select all blank rows from the row number bar and right-click on any of the selected rows.
- Click on Delete in the drop-down menu.
The war between Microsoft Excel and Google Sheets rages on. Here is a guide on how to convert Excel files to Sheets.
How to Delete All Empty Rows in Microsoft Excel by Sorting
In addition to using filters, this is another way to sort out your data and get all blank rows in your tables. Where filtering the data shows the empty rows, in this method, the empty rows will be moved to the bottom of the data. Here is how to remove blank rows in Microsoft Excel using sorting:
- Open the worksheet where you want to delete blank rows.
- In the spreadsheet, click and drag across the data diagonally to select it.
- With the data selected, click on Data in the top toolbar. Here, click on Sort in the Sort & Filter section. This will open the Sort window in the middle of the screen.
- Here, you can specify the parameters you want to sort your data. In this case, we are going to sort out data by OrderDate.
- After adding the sorting parameters, click on OK to save the changes. The blank brown will move to the bottom of the table.
- To delete the empty rows, click on the first empty row’s row number from the bar. Hold Shift on your keyboard and drag your cursor down until the last empty row.
- With all the empty rows selected, right-click on the highlighted row number and click Delete in the drop-down menu.
This method is great as you can choose a main header cell and check if it’s empty. If any cells in that particular category are found empty, they are sent to the bottom of the table. This is great if you have data for several people. Here you can select the person’s name to the sorted. Any empty cell rows will be sent to the bottom for you to delete or ignore.
Things to Avoid When Deleting Blank Rows in Microsoft Excel
A common method used to remove empty rows in Microsoft Excel is by using the Find & Select feature. However, this method isn’t entirely accurate. Some cells may contain blank spaces, which can be hard to find. When the Find what textbox is left empty, it only searches for the blank cells in the table. Once the matches found are selected, the rows still might contain cells where data is present.
It’s best to use the methods mentioned above in this case, which provide much better results. Like Excel, you may have several empty rows when working in Google Sheets. Feel free to check out our guide on how to remove empty cells in Sheets for some of the ways to delete empty rows.
Frequently Asked Questions
How Do You Delete Infinite Columns and Rows in Microsoft Excel?
There are many ways to delete your workbook’s extra columns/rows. The easiest way is to use your cursor to select the rows or columns you want to delete. With the rows or columns selected, right-click on the column header or row bar and click the Delete button in the drop-down menu.
How Can I Delete Blank Rows in Microsoft Excel Using Go to Special?
Use the Ctrl + G keyboard shortcut to open the Go To menu. Here, click on Special towards the bottom of the window. In the Go To Special window, click to select Blanks. This will select the rows containing blank cells. Now, right-click on any of the selected row numbers and click the Delete button in the drop-down menu.
How Can I Get Rid of Empty Rows Using Filters in Excel?
To eliminate empty rows using filters, click and drag your cursor across the cell range where you want to apply the filter. In Excel’s main bar, click on Data and then click on Filter. To add the filter to the data, click on the arrow button in any of the header cells. Here, deselect all the cells and click to select the (Blanks) option. Click on the OK button to save the changes. Now, click and select all the rows from the row bar towards the left side and right-click on any of the selected rows. Click on Delete to delete the empty rows.
Wrapping Up
That is everything you need to know to remove blank rows in Excel using every available method. Hopefully, you’ll have your spreadsheets looking neat as soon as possible.
If you found this guide helpful, you’ll love our premium templates too. Although they are mostly aimed at Google Sheets users, they are relatively easy to export as Excel files and use them in Excel too. If you find a template you love, make sure you remember that you can use the code SSP to save 50% on any purchases.
Related:
- How to Remove Duplicates in Excel
- How to Use HLOOKUP in Excel
- The Ultimate Guide to Excel Version History
- How to Track Changes in Excel
- Master the Excel Consolidate Features
- A Comprehensive List of the Best Excel Courses
- Every Way You Can Merge Excel Files
- How To Collapse Rows in Excel in 2 Minutes [Easy Guide]
