Drop_NA

Function drop rows containing missing values in selected (or any) columns.

Properties

Keywords

missing values, drop rows with missing values, remove empty cells, drop_na

See Also

Select_NA, Pack, Filter

Video-tutorial

How to Remove Rows with Empty Cells in Excel Tables

In practice, it often happens that our dataset contains empty cells. This problem needs to be addressed before analyzing the data. For this purpose, the Reshape.XL add-in includes several functions in the Variables - Missing group. First, we will introduce the Drop_NA function. This function serves to delete table rows that contain missing values in selected columns. An example is shown in the following figure.

In this case we are working with a dataset that contains two numeric and two string variables. All variables contain several empty cells.

To delete records from the table, use the Drop_NA function in the ribbon toolbar tab Variables, under the Missing button.

The Drop_NA function contains only very simple settings - check-box list of dataset variables (Variables to Search).

Using these check-boxes you can determine the columns in which missing values will be searched. In the first example, we only searched for missing values in the first column - ID. As a result, the fourth row was removed from the input table.

Conversely, in the next example, we searched for empty cells in all table columns - all check-boxes in the properties panel were set to TRUE. The same result will also be obtained if you do not click on any check-box. With this setting, empty cells are also searched in all available columns.

Drop_NA function is very easy to use. In addition, you can also use the Select_NA, Fill_NA and Replace_NA functions to process missing cells. If you want to delete rows where values are missing, you can use the Pack function. More complex filters for missing values you can also prepare using the Filter function.