How to apply formula to entire column in Google Sheets

You can drag down the fill handle to autofill formulas. However, it is not efficient to use the fill handle when you are working with a large data set. And also, you need to drag down the fill handle every time you add new data (rows) to your dataset. This post will show you how to apply formula to entire column in Google Sheets without dragging. It will help you to automate some tasks using Google Sheets.

Note: Our blog has several posts on creating data entry forms using Google Sheets as the database. You can use the method described in this post to add calculated fields to your Google Sheets.

The Google Sheets ARRAYFORMULA

In this post, we basically focused on Google Sheets ARRAYFORMULA. By definition, the ARRAYFORMULA,

“Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays”

Syntax:

ARRAYFORMULA(array_formula)

array_formula – A range, mathematical expression using one cell range or multiple ranges of the same size, or a function that returns a result greater than one cell.

You can use this ARRAYFORMULA to expand your functions to adjacent cells, in this case to autofill down the column.

Note: Not all the Google Sheets formulas work with ARRAYFORMULA in this way.

How to apply formula to entire column in Google Sheets using ARRAYFORMULA

First, let’s see examples of how arrays expand to the other cells in Google Sheets.

Following are a few examples of arrays that you can insert as a formula in Google Sheets.

An array expanded in along the same row.

={1,2,3,4,5,6}

An array expanded to the below cells

={1;2;3;4;5;6}

An array expanded into adjacent rows and columns

={1,2,3,4;2,3,4,5;3,4,5,6;4,5,6,7}

You can copy and paste the above formulas to your Google Sheets to see how they are expanded to the adjacent cells.

Let’s write a simple formula inside ARRAYFORMULA to apply it to the whole data range

The following formula multiplies the value in cell A2 and Cell B2. You need to drag this equation all the way down until the last row to apply the formula to the whole data range.

=A2*B2

When using the ARRAYFORKULA you can write this formula as shown below. Note the difference; you need to refer to the entire column (data range) instead of a single cell.

=ARRAYFORMULA(A2:A12*B2:B12)

You only need to add this formula in the top cell of the column. It will automatically expand to the entire data range (up to row 12).

Apply the formula to the entire column using ARRAYFORMULA – Automatically calculate as you add new data

In the above example, the formula is applied to the last row of your data set. To apply it to the entire column, you need to remove the end reference of the cell range. For example, the following will apply the formula to the entire column.

=ARRAYFORMULA(A2:A*B2:B)

See the demo video below.

Stop performing ARRAYFORMULA in empty rows

In the above step, we removed the end reference to apply the ARRAYFORMULA to the entire column. However, the does not stop at the last row of your data range. It performs the calculation in empty rows and displays the results.

You can avoid this by running the ARRAYFORMULA only for the row with data. To do this, you need to dynamically identify the rows with data. Then you can contain an IF condition inside the ARRAYFORMULA to run the formula if the data is available.

You can use the following example to stop running the calculation on empty rows.

Using the ISBLANK function to check empty rows

The ISBLANK function checks whether the referenced cell is empty. It returns TRUE if the cell value is empty or a reference to an empty cell, and FALSE if it contains data or a reference to data.

You can modify the above ARRAYFORMULA as below to prevent performing in empty rows.

=ARRAYFORMULA(IF(ISBLANK(A2:A),"",A2:A*B2:B))

Using the LEN function to check empty rows

The LEN function returns the length of a string. If the cell referred cell is empty, the LEN function returns “0”. So we can use the LEN function with ARRAYFORMULA to stop applying it to empty rows.

You can modify the above formula as below to prevent performing in empty rows.

=ARRAYFORMULA(IF(LEN(A2:A)=0,"",A2:A*B2:B))

So, the structure of the formula to apply formula to entire column in Google Sheets can be written as given below.

Add the ARRAYFORMULA in the table header row and generate the heading

You can delete rows in a data table that uses arrayformula as explained above without any issue unless it contains the arrayformula. If you accidentally delete the ARRAYFORMULA, it affects the entire column and removes the calculation.

You can avoid this by adding the arrayformula to the header row of the data table. You can also customize the formula to display the heading of that column in the same cell that you typed the arrayformula.

You can modify the formula as below using the curly braces method I explained earlier.

={"Column Heading";ARRAYFORMULA(IF(LEN(A2:A)=0,,A2:A*B2:B))}

By using a semicolon between array elements you can display the elements in a column. In the above formula, the first element (the heading of the column) is written in the cell that includes the formula. And the second element the ARRYAFORMULa written in the cell below as we wanted.

Wrapping Up

In this post, you learned how to apply formula to entire column in Google Sheets using ARRAYFORMULA. When you wrap the formula with ARRAYFORMULA, it is automatically expanded to the range you specified. With ARRAYFORMULA you only need to type the formula in the first row of your data set. You can also customize this formula to automatically expanded to the new rows as you add more data.