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.
4 comments to "Video: How to remove blank rows in excel"
I have sent the excel document for reference.
please tell me if i can send you the excel document for solving my problem.
Hello Zeefah,
Please send us a sample spreadsheet with the initial data and the expected results to support@ablebits.com. Please include a link to this blog post and your comment number. We'll do our best to assist you.
Hello,
I am puzzled in getting a report from one excel sheet based on specific ID.
Prices 75 45 7 70 30 40 65 7 30 115 75 55 7
Meal Meal-1 Meal-2 Meal-3 Meal-4 Meal-5 Meal-6 Meal-7 Meal-8 Meal-9 Meal-10 Meal-11 Meal-12 Meal-13
ID 15-Sep 16-Sep 15-Sep2 16-Sep3 15-Sep4 16-Sep5 15-Sep7 15-Sep8 15-Sep9 15-Sep10 15-Sep11 15-Sep12 15-Sep13
1 117 230 27 27 225 146 127 214 225 232 79 127
2 79 20 27 34 227 104 142 214 214 225 102 127
3 153 79 27 117 93 209 209 227 146 127
4 31 61 27 214 42 209 102 100 39 127
5 3 32 209 106 80 110 151 127
6 227 98 209 143 100 230 74 127
7 225 16 34 29 45 127
8 110 34 69 79 31 127
9 79 121 98 17 61 132
10 88 81 79 64 32 132
11 46 71 27 75 93 153
12 214 223 23 75 125 153
13 51 15 130 107 12 153
14 95 111 11 107 224 153
Result Sheet:
Date: This cell matches ID (This sheet) and Serial (ResultSheet) and finally extract date from ResultSheet.
Left this date if ID does not matches
Meal Served: This cell look at the "Date" and than finally extract "Meal" from ResultSheet
Qty: This cell also look at the "Date" then count IDs (ResultSheet), matches with ID
Unit: This cell also look at the "Meal" and than finally extract "Price" from ResultSheet