How to Properly Delete Blank Rows in Excel
Data rarely comes perfectly clean, without errors, and well organized. Often, you will have to take steps to ensure that your data is in the best working order, One of the biggest issues is having blank rows in Excel that interrupt your data. Of course, if your data set is very small, you can simply scroll and delete each individual row. However, this is extremely time-consuming for large tables. Check out this tutorial if you need to Remove Every Other Row or Fill Blank Rows with Zeros.
Here is some data with blank rows in Excel. Follows the steps below to eliminate these spaces.
There are spaces that are visible in row 4,5,19 and 32. This can be removed quickly just by eyeballing it. However, try the steps below:
I. Removing Blank Rows with Find & Select
1. Click Find & Select
2. Click to Go to Special
3. Choose Blanks
4. Click OK and then all the blank rows/cells will be highlighted
5. Choose the Delete under Cells section on the Home Tab
6. Click Delete Sheet Rows
When you select Blanks, all the blanks rows will be highlighted
You can now delete these highlighted rows by selecting Delete in the Home tab under the Cells section. This action will delete all the blank cells that were highlighted.
II. Delete Blank Rows with Filter
An old-school method of deleting blank rows is to simply add a filter and then delete the rows. Follow the instructions below:
1. Highlight the entire data you want to clean. If you have a large data set you can press CTRL + SHIFT + DOWN to highlight all of the rows and CTRL + SHIFT + RIGHT to cover all the columns.
2. Click the Data menu from the top navigation.
3. Click the filter icon and deselect Blanks. Now you can copy and paste the values over to another tab.
4. Alternatively, you can just select blanks in the filter..
5. Once you have select blanks, you will be left with empty rows, look at the row number highlighted in blue to know where your data stops.
Once you select only blanks you will be left, with nothing in the rows. You can see these cells highlighted in blue. You can just delete these.
