QueHow

A How-To and wiki Blog

  • Operating System
    • Windows
      • Windows 10
      • Windows 7
    • Linux
  • Software
    • Application Software
    • Microsoft Office
      • Outlook
      • Word
      • PowerPoint
  • Internet
    • Browsers
      • Mozilla Firefox
      • Google Chrome
    • CMS
    • Tips & Tricks
  • Website
    • ecommerce
    • Google
    • Social Networking
    • General Websites
  • Mobile
  • Email
    • Gmail
  • Wiki

How to Delete Blank Rows in Excel 2016?

By Neha T 1 Comment

Blank rows or cells in a table create a lot of confusion for excel built-in functions to identify the data in a correct manner. So, to clean the data in the table we must delete blank rows present in a table that creates a disturbance while analysing the data.

Well, if you are working with a large table in Excel and you keep on adding or removing entries in a table, definitely there will be blank cells or entire blank rows created in the table. And now, you have to delete blank rows but how?

We will be discussing three methods:

  1. Using Go To Special
  2. Using Filter
  3. Using Formula

Steps to Delete Blank Rows in Excel 2016:

Using Go To Special

Note: Do not use this method to delete blank rows as this also deletes the rows which have some column entries remaining.

Step 1: Select the entire table and click Go To Special…. from Find & Select dropdown menu in the Editing group of Home tab.

delete blank row using go to special

Step 2: A Go To Special dialog box will pop out. Here, under the Select section, you have to select Blanks and click OK.

delete blank row using go to special 1

Step 3: The above step would highlight all the blank cells in the table and automatically it highlights the entire blank rows also.

delete blank row using go to special 2

Step 4: Now, to delete the blank rows click the Delete dropdown menu under Cells group of Home tab. It will show some delete options among them click the Delete Sheet Rows.

delete blank row using go to special 3

Step 5: As you can see in the picture below it has also delete those rows which have some column entry remaining. Like, it has deleted the entries of Davis Clawson, Jenny & Annie Rotus which has some column entries remaining. Hence, it is advised not to delete blank rows using this method.
delete blank row using go to special 4

Using Filter

Step 1: Select the entire table from where you want to delete blank rows. Now, to apply a filter for sorting the blank rows, click on Filter in Sort and Filter group of Data tab.

delete blank row using Key column

Step 2: You can see that the filter has been applied column headers of your table.

delete blank row using Key column 1
Click on the anyone of your column header filter. For example, I have clicked on the Sales. Among all the entries in the column select Blanks and deselect the other entries and click OK.

delete blank row using Key column 2

Step 3: Now in the picture below you can observe that it has filtered those rows from the Sales column which has a blank entry i.e. row 2, 5, 7, 9, 13.

delete blank row using Key column 3

To delete the rows, select all the blank rows which has no column entries remaining and right-click on it and click on Delete Row.

delete blank row using Key column 4

Using Formula

It is the best method to delete blank rows from the table in Excel.

Step 1: Take a column just next to the last column of your table and name it. For example, I have named it as Blank Cells.

In the next row of the new column type the formula:

=COUNTA(A2:D2)

This formula counts all the non-empty entries of the selected range in the formula.

delete blank row using formula

Drag down the formula till the last row entry of your table. And you will find 0 in the rows that have all column entries empty.

delete blank row using formula 1

Step 2: Now, apply a filter for column Blank Cells. For that select the Blank Cell column entries and click on the Filter button in the Sort & Filter group of Data tab.

delete blank row using formula 2

Step 3: Now, click on the drop-down arrow of the Blank Cells column and select 0 and uncheck all other entries and click OK.

delete blank row using formula 3

Step 4: Step 3, will filter all the rows having 0 in Blank Cell column. Now, you can easily delete all these rows, select all the filtered blank rows and right click and delete using delete row option.

delete blank row using formula4

This is all about how you can delete blank rows from a large table in excel. You can explore more method for doing the same.

You Might Also Like:

count unique values featured imageHow to Count Unique Values in Excel 2016? How to convert text to number in Excel - Featured ImageHow to Convert Text to Number in MS Excel 2016? compare two columns in Excel Feature imageHow to Compare Two Columns to Find Matches & Differences in Excel 2016? how-to-freeze-row-and-column-in-ms-excel-in-2016-featured-imageHow to Freeze Rows and Columns in MS Excel 2016? Delete Duplicates feature imageHow to Delete Duplicates in Excel 2016? hide columns feature imageHow to Hide and Unhide Rows & Columns in Excel? Merge Cell in Excel feature imageHow to Merge Cells, Columns & Rows in Excel? how-to-delete-macro-in-ms-excel-2016-featured-imageHow to Delete Macro in MS Excel 2016?

Comments

  1. mini royale 2 says

    April 6, 2020 at 10:47 am

    Thank you for these specific tutorials I will apply now!

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Most Viewed

  • How to Install and Use TELNET in Ubuntu
  • How to install Hindi fonts in MS Word
  • How to Change a File Extension in Windows 10?
  • How to create a flipkart account in 3 simple steps
  • How to Change Monitor Refresh Rate in Windows 10?
  • How to install IIS on Windows 10
  • How to Insert Clickable Checkbox in MS Word 2016?
  • How to Set Auto Reply in Outlook 2016?
  • How to Login as Administrator in Windows 10?
  • How to Check RAM Size and System Type in Windows 10?

Recent Additions

  • How to Add Hyperlink to Another Sheet in Excel?
  • How to Add and Remove Watermark in Excel
  • How to use Relative and Absolute Cell Reference in Excel?
  • How to Enable and Disable Scroll Lock in Excel?
  • How to Use INDEX and MATCH Functions in Excel?
  • How to Make Bar Graph in Excel?
  • How to Count Duplicate Values in Excel?
  • How to Wrap Text in Excel?
  • How to Round off Numbers in Excel?
  • How to Create a Bell Curve in Excel?

Related Searches

  • How to Clear Recent Document file list in Microsoft Word
  • How to empty your trash automatically in Ubuntu 14.04
  • How to Close Apps on iPhone?
  • How to convert notepad to PDF
  • How to Create a Bell Curve in Excel?

Copyright © 2023 · QueHow · Contact Us