As an Excel user, it is very common to work with a large set of data. Excel gives a platform to set your data nicely but sometimes blank rows in nicely set up data give a big reason of headache and it’s quite irritating also. These blank rows spoil the appearance of the dataset. To solve this painful problem, we come up with some useful tricks and techniques to remove blank rows in Excel. Hope you enjoy this article and will use these methods to solve your bitter problems.
Download Practice Workbook
Download this practice workbook
7 Methods to Remove Blank Rows in Excel
To remove blank rows in Excel, we come up with the 7 most efficient and useful methods. As an excel lover, it’s a matter of joy that you can solve a problem in that many ways. To show all the methods effectively, we take a dataset that contains order details such as date, item, price, and state name.
1. Remove Blank Rows Manually in Excel
Our first method is based on using the context menu in Excel. By using this context, the menu can easily remove blank rows not only for contagious cases but also for non-contagious also. You can remove blank rows manually in 3 different ways.
1.1 Context Menu
- For contagious rows, drag across the row header using a mouse or select the first-row head and press Shift and click the last row header. For non-contagious rows, Select the row header and press Ctrl, and click the other rows one after another.
- After selecting the blank rows, right-click on the rows header, and a drop-down menu will occur. Find out the Delete option from it and press it.
1.2 Delete Option
- First, select the blank rows like the first method. Instead of right-clicking on the row header, we can remove blank rows through the Home.
- First go to the Home tab in the ribbon. In the Cells group, you’ll get the Delete option.
- From the Delete option, select ‘Delete Sheet Rows’. That will automatically remove blank rows in Excel.
1.3 Keyboard Shortcut
- Instead of right-clicking on the row header or using the Home tab option in the ribbon, you can remove blank rows using the keyboard shortcut. To do this first select the blank rows and press ‘Ctrl + -‘. It will automatically remove the selected blank rows in Excel.
2. Using ‘Go To Special’ Option
Our second method is based on the ‘Go To Special’ option in the editing section. To remove blank rows manually is a time-consuming process. You have to select the blank row manually one after another. This method minimizes the effort in an effective way.
- In this process, you don’t need to select the blank rows manually. First, go to the Home tab, and in the editing section, select the Find & Select.
- From the Find & Select option, select the Go To Special option.
- A Go To Special box will prompt up, from there just select the Blanks option and click on ‘OK’.
- It will select all the blank rows automatically without any manual effort.
There is a keyboard shortcut available to open the ‘Go To’ Menu. Press ‘Ctrl + G’ that will open the ‘Go To’ window and select the Special button.
Now, use any of the three ways which have shown in the first method to delete blank rows. That will you give the following output.
3. Applying Find Command to Remove Blank Rows
Another effective method is by using the Find command. This method is similar to the ‘Go To Special’ method. The only difference is that here, we use the Find option to select our blank rows in the dataset.
- First, select any column. Then go to the Home tab and select the Find & Select option from the Editing group.
- From this option, click on ‘Find’.
In the Find option, a Find and Replace menu will appear. From there, select Options to alter some modes.
To open the Find menu just press ‘Ctrl + F’.
After clicking on options, several options will appear. Select them according to the following process:
- Leave as blank in the Find what That means we set it to find blank cells.
- Click on Match entire cell contents because it helps to find blanks in the entire dataset.
- As we need to delete blank rows, so we need to select By Rows in the Search drop-down menu.
- Change the ‘Look in’ drop-down menu as Values and click on Find All.
- The Find All button will find out all the blank rows in the dataset. Select all the blank rows by pressing ‘Ctrl +A’.
- That will select all the blank rows in the dataset now you can remove them by using the first method’s delete method and finally got the desired output.
4. Delete Blank Rows Utilizing Filter Option
A filter feature can be defined as a feature that shows relevant data by hiding irrelevant data. We use this process to hide irrelevant blank rows and show our relevant dataset.
- Select the whole dataset to filter.
- Then go to the Data tab in the ribbon and select Filter.
- Now click on the drop-down icon on one of the columns and just select blank and uncheck all other values and click ‘OK’.
- The main reason to do this is that we want to show the blank rows only and delete them and then check on the main values in the filter toggle. We select all the blank rows and delete them using ‘Ctrl+-’ keyboard shortcut.
- Now click on all the relevant values in the filter toggle and click ‘OK’.
- Now, you’ll get all the relevant values after removing blank rows.
- How to Remove #DIV/0! Error in Excel (5 Methods)
- Remove Header and Footer in Excel (6 Methods)
- How to Remove Hyperlink from Excel (7 Methods)
- Remove Comments in Excel (7 Quick Methods)
- How to Remove Outliers in Excel (3 Ways)
5. FILTER Function to Remove Blank Rows
We can easily remove blank rows using the FILTER Function. The FILTER function in Excel can be defined as a feature that is used to filter a range of data. This FILTER function is also in the array function category.
- First, you need to convert the dataset into a table. To do this, select the whole dataset and go to the Insert tab in the ribbon, and select Table from the Tables group.
- When you click on the Table option, a Create Table box will appear. As you select the dataset previously, to confirm it, just click ‘OK’. It will automatically create a table with your selected dataset.
- Now after creating a table, you have to set a table name. To do this, go to the Table Design tab and in the properties section, you’ll get the Table Name. Change the name to your preference, and mind it you have to use this name in the filter function.
- Create new headers in Excel where you want to put your filtered dataset.
- Select the cell H5 below the headers that are created previously and write down the following FILTER function:
- Press Enter to apply the FILTER function. That will remove all the blank rows from the dataset and give a nice and clean dataset.
6. Using Sort Option
Another good way to get all the blank rows is by using sorting. Sorting can be done in a way where all the blank rows will be placed at the bottom of the dataset. Then you either ignore them or delete them with ease.
- First of all, select your entire dataset.
- Then, go to the Data tab, and in the Sort & Filter group, select the Sort.
- Whether you select ascending or descending, In both cases the blank rows will appear at the bottom. Now, you can ignore the blank rows or delete them manually.
Read More: How to Remove Sort in Excel (3 Easy Methods)
7. Use of Power Query to Remove Blank Rows
Our final method is based on using Power Query. Power Query is a powerful tool in Excel that allows importing data from external sources and that data can be prepared for your own requirement. Here, we use a dataset in the Power Query to remove blank rows.
- Open the Power Query through the Data tab in the ribbon. From Get & Transform Data group, select Get Data.
- From the Get Data option, select ‘Launch Power Query Editor’ that will open the Power Query Editor.
- In the Power Query Editor, go to the File option and select New Source from there you can add your data source.
- When you add your dataset, it’ll show you in the following way
- Now, you can see all the blank rows are denoted as null. To remove these blank/null rows, just go to the Home tab in the Power Query Editor and select the Remove Rows.
- From the Remove Rows options, select Remove Blank Rows which will automatically delete all the blank rows.
- You can hide blank rows by filtering the null values. Click on the Filter drop-down and uncheck null. It’ll hide all the blank rows.
- Finally, we’ll have our dataset free of blank rows.
Having blank rows in a dataset is a painful problem to deal with. We have discussed eight different methods to remove blank rows in Excel. All the methods are really useful and fairly easy to use. If you have any questions, feel free to ask in the comment box, and don’t forget to visit the Exceldemy site for further Excel-related knowledge.