Fill_NA

Function fills missing values in selected columns using the previous (or next) entry.

Properties

Keywords

missing values, fill missing values, empty cells, fill missing cells, fill_NA

See Also

Replace_NA, Drop_NA, Select_NA

Video-tutorial

How to Fill Empty Cells by Last (or Next) Value in Excel Spreadsheet

Sometimes it happens that if the values in column repeat, they are not directly written and cells are left empty. In this way, you can manually prepare datasets faster and they are also smaller in size. Such cases are common with categorical variables. However, we need to have these cells filled in for subsequent analysis. The Fill_NA function is used for this purpose. An example of its use is shown in the following figure.

In the following text we will use a sample dataset. This dataset contains four variables and includes several empty cells.

If you want to fill them according to the last known values or the next ones, use the Fill_NA function. This function can be found in the ribbon toolbar tab Variables, under the Missing button.

The function allows to set two parameters in the properties panel - Direction and Variables to Fill. The Direction parameter defines the value to be used when filling empty cells within columns. If set to Down, empty cells will be populated in column according to the last known value. If the property is set to Up, the values will be populated according to the next available value.

In the list under the Variables to Fill property you can define table variables (columns) in which you want to fill empty cells. In the following example we fill missing values in three variables - ID, CAT_A and Num. The last known values in each column are used for filling (Direction = Down).

Conversely, the following example shows the result in which we filled out the missing values in the same columns, but used are the following available values (Direction = Up).

Using Fill_NA is very intuitive and simple. In addition to the Fill_NA function, you can also use the Drop_NA, Select_NA and Replace_NA functions to work with missing values.