Microsoft Excel is a popular tool usable to perform its data analysis function. That works if the data isn’t messy. An example of a possible Excel data problem would be having several blank rows in the Excel spreadsheet can totally break your analysis. Thankfully, there are several ways to delete blank rows in Microsoft Excel web or offline version.
If you’re dealing with a small data set, you can delete the blank rows manually. However, if you have a large data set, it is cumbersome. Luckily, you can clean up the blank rows inside Excel and there are different ways to achieve that. Here’s how to do so:
Using the Get & Transform Feature on Excel
The Get & Transform feature is one of the fastest ways to retrieve data on Microsoft Excel. You can use it to delete blank rows on your spreadsheet. However, your data needs to appear in a table for this feature to work effectively.
Here’s how it works:
Step 1: Click on the Start button and type Exceland hit Enter to launch the Microsoft Excel.
Step 2: Open the Excel file you want to fix and place your cursor between the right-angled triangle between the row and column of your Excel worksheet. That will highlight all cells in the worksheet.
Step 3: On the Ribbon, select the Data tab.
Step 4: Within the Get & Transform Data group, click From Table/Range.
Step 5: On the Create Table dialog box, manually enter or drag your cursor to the Excel cells to fill in the details of the location of the data on your table.
Step 6: Click on OK and wait for the Power Query Editor to come up.
Step 7: On the Power Query Home tab, click on the Remove Rows drop-down located within the Reduce Rows group.
Step 8: Click on Remove Blank Rows from the options.
Step 9: On the Power Query Home tab, click on Close & Load to save your changes.
Your cleaned file should be loaded to a new worksheet without the blank rows.
Using the Filter Feature
Another option you can consider when removing blank rows from a dataset is the Excel Filter feature. Here’s how it works:
Step 1: Launch the Microsoft Excel file.
Step 2: Select the range of cells containing all the data within your data set. This should include the blank rows as well.
Step 3: On the Ribbon, select the Data tab.
Step 4: Within the Sort & Filter group, click on Filter. Once you do this, you should see arrows beside each field name.
Step 5: Click on the arrow beside a field name that has blank cells in the rows that you will like to delete.
Step 6: De-select the Select All option.
Step 7: Scroll down the list of options and select Blanks.
Step 8: Select all the row headings of your filtered rows. Do this by clicking on the first row then holding down Shift on your keyboard and clicking on the heading of the last row.
Step 9: Right click on any of the headings that you selected to reveal a menu list and click on Delete Row.
Step 10: Click on the Data tab again and select Clear inside the Sort & Filter group to get rid of the filtering.
Using the Sort Feature
One of the easiest ways to delete blank rows on Excel is using the Sort feature. When you use this method, it moves the blank rows to the bottom to easily ignore them. Here’s how to use this method:
Step 1: Launch the Microsoft Excel file.
Step 2: Place your cursor between the right-angled triangle between the row and column of your Excel worksheet. That will highlight all cells in the worksheet.
Step 3: On the Ribbon, select the Data tab.
Step 4: Within the Sort & Filter group, click on Sort. Your action will reveal a dialog box. If there is a header row in your selection, tick the box beside My data has headers.
Step 5: Move your mouse beside the Sort by option and click on the column or field with the blanks that you want to get rid of. Choose the best sorting option that suits your data, such as smallest to largest, A to Z, descending, or ascending. That will display the blanks at the base of your data set.
Step 6: Click on OK.
Using the Go To Feature
Finally, you can also use the Go To Special feature to remove blank rows in Excel. Here’s how it works.
Step 1: Select a column with blank cells (assuming that the other rows in the column are blank).
Step 2: Press Ctrl + G simultaneously on your keyboard to reveal the Go To dialog box.
Step 3: Click on Special to show the Go To Special dialog box.
Step 4: In the Go To Special dialog box, click on Blanks, then click OK. Excel automatically selects every blank cell in the selected range.
Step 5: Right-click on any of the blank cells selected and click on Delete from the list of menu options.
Step 6: On the Delete dialog box, choose Entire Row, then click OK.
Track Changes in Excel
Cleaning the data contained within an Excel file would lead to several changes. To ensure you can maintain a trail of the changes that happen to the document, ensure you enable the track changes feature on Excel.