Google Sheets | ARRAYFORMULA | IF | To Fill Blank Cells | Example | Spreadsheet | Tutorial

Google Sheets ARRAYFORMULA function, in combination with IF, assists to fill all the blank cells in a range, with a specified value, in one step. Why use the two functions? IF alone cannot fill all the blank cells of the range. The reason is IF accepts only a single cell address, and not a range, as its argument.

-------------------------------------
How to Use ARRAYFORMULA in Google Sheets?

Use an array as an argument in a non-array function:

-------------------------------------
How to Use IF in Google Sheets?

IF helps to evaluate a logical expression and assign data:

-------------------------------------
How to Filter Data in a Pivot Table in Google Sheets?

Filter data in a pivot table to display only specific data:

-------------------------------------
How to Create a Pivot Table in Google Sheets?

Create a pivot table for calculation and in-depth data analysis:

-------------------------------------
Which one to Use? A Pivot Table, or COUNTBLANK Function, to Count Blank Cells in Google Sheets:

Gives steps to use a pivot table or COUNTBLANK to count blank cells:

-------------------------------------
How to Use a Pivot Table to Sum by Rows or Columns without using Formula in Google Sheets?

Sum numbers by rows and or columns easily, using a pivot table:

-------------------------------------
How to Create a Pivot Table to Get Maximum Value without Using Formula in Google Sheets?

A pivot table assists to get the maximum value in a data range without using a formula:

-------------------------------------

Syntax of ARRAYFORMULA Function

=ARRAYFORMULA(array_formula)

Start the formula with an equal-to symbol

ARRAYFORMULA is the name of the function.

array_formula, can be, for example, another function.

For more info on the syntax of ARRAYFORMULA, please refer to the tutorial How to Use ARRAYFORMULA in Google Sheets, whose link is given above.

Example 1

=ARRAYFORMULA(LEN(A2:A7))

The LEN function nested inside ARRAYFORMULA returns the length or the number of characters in the text in cells A2 to A7 in one step. Without the ARRAYFORMULA, LEN returns only the length of the text in cell A2.

Example 2

This example nests IF inside ARRAYFORMULA to fill blank cells in a range with three dashes.

=ARRAYFORMULA(IF(A2:E15="","---",A2:E15))

IF function checks whether the range A2 to E15 has any blank cells. If there are one or more blank cells, then it will be filled with three dashes and returns the same. Otherwise, IF returns the array element in the cell in the range A2 to E15.

Take a look at this video tutorial, which gives the steps to use the Google Sheets ARRAYFORMULA function to fill blank cells, with examples.