Video: How to remove blank rows in excel
How many times have you realized that hundreds of records are left out from your calculations because of blank rows? Let's check a popular misconception about finding them and consider three ways you can quickly remove empty rows in this video.
Delete blank rows in Excel: video transcript
Blank rows can easily interfere with the results of your calculations. This video will show you several ways you can delete empty rows in Excel and consider one way you should avoid.
A tricky tip for Excel: delete empty rows and lose data
There is a popular suggestion to use F5 and "Go To Special" dialog to select blanks and then simply delete entire rows. What they don't tell you is that this way you'll delete all rows that contain even just one blank cell, so you risk losing a lot of data.
Let's look at three other approaches that you can use without corrupting your table.
Delete all blank rows in Excel with the help of a formula
If all your columns are equally important and you want to delete completely empty rows, you can use a a formula in a helper column. Add the column to the right of your table and enter
=COUNTBLANK, add a reference to the row it's in, from the first to the last cell. The formula will count the number of empty cells in this row. Copy it down to the end of your table:
- Press Ctrl + Shift + End to see the last cell in your range
- go to your new column and select it with the help of Ctrl + Shift + Up keys
- paste the formula
Now we can separate the blanks from the rest of our data. Go to Data tab and click the filter button, use our new column header, unselect all, and look for the biggest number, which should be the number of cells in your rows. I have five cells here, so these are the rows I need.
Pick the first cell by pressing Ctrl + Home and then select all these rows with the help of the shortcut Ctrl + Shift + End.
Now you can either right-click and choose to "Delete rows", or use the shortcut Ctrl + minus to remove blank rows.
Filter out blanks and delete empty rows in Excel
If you want to find records that miss just the main details, such as the IDs, you can filter the range by your key column. Use the same shortcuts to select the table and make sure you don't leave anything out: Ctrl Shift End, and click the filter button.
- Apply it to your key column
- Click "Select all" to clear the check-boxes, scroll to the end of the list and select "Blanks"
- Click Ok and you'll get all rows without the key records
To delete empty rows, select them using the same shortcut: Ctrl + Shift + End. Now you can right-click and choose to delete entire rows, or use the shortcut Ctrl -. Now you can clear the filter from the same tab.
A special add-in to remove empty rows in Excel
Of course if you have Ultimate Suite, you get a much simpler and quicker way to delete blank rows: you'll find it under Ablebits Data tab. Look for the Clean group and click Empty rows. Confirm that's what you want to do and get your table ready.
You can get ultimate Suite with a discount by using the coupon code Preview.
Now you can be sure your tables are clean and ready for work. If you still have questions about removing empty rows in Excel, please post them as comments, we'll do our best to help you.
