How to Delete Rows in Excel? 6 Efficient Ways
(Note: This guide on how to Delete Rows in Excel is suitable for all Excel versions including Office 365)
Empty rows in your worksheet portray unprofessional and unorganized skills. One thing we know for sure about empty cells in rows is that it spoils calculations during data analysis.
Empty rows in Excel are formed:
- While copying data from another worksheet or workbook that has not been previously organized,
- While leaving spaces between tables and values,
- Accidentally by typing or printing mistakes, or
- While extracting large data from sample sheets and databases.
Whatever the case, it leaves the sheet unclear and confusing for the observer as most of the values appear scattered across the Worksheet.
How do you get rid of these empty rows? Well, in this article, let’s learn how to delete Rows in Excel in 6 effective ways.
You’ll learn:
Related reads:
HOW TO DELETE A NAMED RANGE IN EXCEL? 3 EASY METHODS
HOW TO DELETE A PIVOT TABLE IN EXCEL? 4 BEST METHODS
How to Remove Tables in Excel? 3 Easy Ways
Ways to Delete Rows in Excel with an Example
Let us now see how to delete rows in Excel with an example. Consider a sample worksheet showing the persons’ list, respective fields, and ID details.
In this sample sheet, you can notice that some rows have unclear information or empty rows, which add no value to the table. Therefore, rows containing wrong or no information can be deleted.
Using the Home Tab
This is quite a proper way to delete rows in Excel. However, it is not the quickest way. This method is the conventional way to delete rows, and it is necessary to know this method when your input devices, like the keyboard and mouse, are not working.
- Select the row you want to delete by clicking on it or locate it using the arrow keys.
- Hover over to the Home tab. Under the ribbon, click on Cells > Delete > Delete Sheet Rows.
- Once done, the selected row will be deleted. Make sure to select only the rows with no data entered to avoid deleting user data.
Suggested reads:
How to Use FLOOR Function in Excel? 2 Easy Ways
How to Spell Check in Excel? A Step-by-Step Guide
How to Insert Bullet Points in Excel? 5 Easy Methods
Using the Mouse Control
This is the easiest and fastest way to delete rows in Excel. What do I mean by that?
Well, in just a few clicks, the job is done.
- To select the entire row, click on the corresponding Row heading number found on the left-most end of the sheet.
- Place the cursor on any of the selected Row Heading numbers and Right-click.
- From the options displayed, choose Delete Rows to delete the entire row.
Using the Keyboard Shortcuts
So far, we have seen the conventional method and the quick method. Now, let’s see how to delete rows in Excel using keyboard shortcuts. It is not only convenient, but it is essential to know the keyboard shortcuts going further with operating Microsoft Excel.
- To select the entire row you want to delete, press Shift + Spacebar.
- Then press [Ctrl + – (minus)] to delete the selected row.
How to Delete Multiple Rows in Excel? (Quick Method)
Multiple empty rows in your Excel Worksheet interfere with the original data while calculating and look unorganized during the report presentation. Multiple empty rows must be deleted from the Worksheet to avoid such instances.
Using Mouse and Keyboard Shortcuts
In this instance, let’s say that you want to delete multiple rows in Excel Worksheet that are adjacent to each other.
- Drag down multiple rows at once and follow the above-mentioned steps to delete multiple rows in Excel Worksheet either by Home tab or Mouse control methods.
- To use the keyboard shortcuts, press Shift + Spacebar and select multiple rows by using the arrow keys while holding the Shift key.
Using the Go To Special Command
While handling large data, selecting empty rows using the keyboard or the mouse is impossible, which will take forever to do the job.
To make this task simple, the Go To Special command is used.
- First, select the entire table that contains empty rows to perform this command.
- Click on Find & Select icon from the Home ribbon.
- Choose the Go To Special option.
- In the Go To Special dialog box, select the option Blanks from the list shown.
- All empty rows in the Worksheet are highlighted once you Click Ok.
- To delete the highlighted empty rows, go to the Cells icon from the Home ribbon and click Cells > Delete > Delete Sheet rows.
You can use this method to delete multiple rows in Excel Worksheet successfully.
Using the Data Filter
Another method to delete multiple rows in Excel is by using the Data Filter option found on the Home ribbon.
- You can manually select the entire sheet by dragging the mouse, or you can use the keyboard shortcuts Ctrl + Space to highlight the rows from the sheet.
- Go to the Data Tab and find the Filter icon to filter your data.
- Select Blanks from the list of data filters displayed.
- Click Ok. The row headings of empty rows are highlighted in blue once you’re done.
- Hit the Delete button to delete rows or choose Delete Sheet Rows from the Cells icon under the Home Tab.
Now, you’ll be able to perform data analysis on this edited Worksheet that contains pure data with all the empty rows removed. You can remove the Filter option once you remove all the empty rows.
Also read:
How to Use Icon Sets in Excel? A Step-by-Step Guide
How to Apply a Formula to an Entire Column in Excel? 6 Ways
How to Use Color Scales in Excel? A Step-by-Step Guide
Frequently Asked Questions
How to quickly delete rows in Excel?
You can quickly delete rows in Excel by clicking on the corresponding Row heading numbers, Right-clicking on the mouse, and clicking Delete Rows.
How do I delete multiple rows in Excel?
Select the rows you want to delete by dragging down using the mouse, or by selecting rows using the keyboard, or clicking on Find and Select > Go to special > Blanks > Delete > Ok.
How to delete rows in Excel without dragging?
Select any row you want to delete. On the keyboard, press Shift + Spacebar to select the entire row and Ctrl + – (minus) delete rows.
Closing Thoughts
In this article, we saw how to delete multiple rows in Excel in 6 easy ways. We also learned numerous ways to delete single and multiple rows in Excel. Follow these steps to improve your Excel knowledge and skills to help you perform tasks without making any mistakes.
If you need more high-quality guides, please check out our free resources center.
Ready to dive deep into Excel? Simon Sez IT has been teaching Excel for over ten years. For a low monthly fee, you can get access to 150+ IT training courses. Click here for advanced courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
