Deleting Blank Cells In Excel Download The Demonstration

The row isnt empty, but the Order ID value is. (I deleted the Order ID value in row 7 to make the example more effective.) Figure E This filter matches all records where the Order ID value is blank.

Regardless of how you end up with them, its best to rid a data set of blank rows.

Excel uses blanks to determine ranges, so Excels selection-based features wont work as expected if there are blank rows anywhere in your data set.

In this article, Ill show you five easy ways to delete blank rows.

Before we go any further, I have a warning: when deleting entire rows, be careful.

Deleting an empty row in your data set will also delete out-of-sight data in the same row.

If youre using an earlier version, run a quick search on Power Query Add-in.

Deleting Blank Cells In Excel Download The Demonstration

You can work with your own data or download the demonstration.xls and.xlsx files.

Get Transform Data Excels Get Transform Data feature provides a quick way to retrieve data.

While doing so, there are a number of ways to manipulate the data, and one of those will delete blank rows.

Fortunately, its easy to use, but it does require a Table object; if your data is a normal data range, the feature will convert the range for you--which you might not want.

SEE: 13 handy Excel data entry shortcuts (free PDF) (TechRepublic).

At this point, Excel will fail to find the entire data set because of the empty rows.

Change the default range to A1:H20 ( Figure A ), check the My table has headers option, and click OK.

Excel will launch the Power Query Editor to retrieve the data.

In the Reduce Rows group (Home tab for Power Query), click the Remove Rows option and select Remove Blank Rows ( Figure B ).

Power Query will remove the blank rows but not the rows with blank cells.

Click Close Load in the Close group, and Power Query will copy the modified data set to a new sheet in Excel.

At this point, the data is a Table object ( Figure C ), which you can convert to an ordinary range if you prefer.

Figure C Power Query returns a Table object with no blank rows.

That is probably the behavior that will influence whether or not you choose this feature.

Filter If you want to remove the blank rows from the original data set, you might try the Filter feature, but use caution.

We can illustrate this problem best with a quick example (using an ordinary range, not a Table object): Click the Data tab, and then click Filter in the Sort Filter group.

Use the new Order ID dropdown to choose your filter: uncheck the (Select All) option and then check (Blanks), as shown in Figure D.

If the (Blanks) option isnt available, start over but select the range first.

Because theres no Order ID value for the record in row 7, the filter matches that record.

The row isnt empty, but the Order ID value is.

I deleted the Order ID value in row 7 to make the example more effective.) Figure E This filter matches all records where the Order ID value is blank.

Deleting Blank Cells In Excel For Mac