How to execute formulae on an entire column in Google Sheets

When working with formulas in Google Sheets, you will have to apply the same formula for an entire column. It is because most of the time, the data is arranged vertically. If you have hundreds or thousands of cells in a column, it is difficult to apply a formula to each cell manually. We have different ways to apply a formula to an entire column in Google Sheets. It is by a Double-Click on the “Fill Handle” to Copy Down the Formulae.

Google Sheets does have a fill handle feature that saves time when you need to apply a formula to an entire column. Remember that it only works with columns and not rows. For example, as shown below, you have a dataset with a “formula in cell C2,” and you want to use a similar formula to all the cells in “Column C
till C10.” Follow these steps:

1. Make a selection of the cell that already has the formula.

Point the cursor to the bottom-right part of the selection, then move it to the blue square.

2. Do a left double click.

The steps above will instantly fill the column with the same formula until the last filled cell.

Note.

This method is great for applying a formula to the entire column. However, it has one setback. It only fills until the last contiguous filled cell. To avoid this, delete any empty row or fill any empty cell in the dataset. If you have empty rows, using the autofill will stop right before it.

Copy down the formula by use of the Fill Handle

If you have a small set of data, you can manually drag the ‘fill handle’ to fill the cells and use the same formula for the entire column. Moreover, the method can be effective if there are “blank cells/rows” in the dataset when you can’t apply the double-click method to copy down formulas. The following are the steps to drag and copy the formula to the entire column/row:

1. Make a selection of the cell that already has the formula

2. Move the cursor to the bottom-right part of the selection, one that looks like a small thick blue square. You will note that the cursor changes to a plus icon.

3. Do a Left-click and drag to cover all the cells you want to copy down the formula.

Alternatively,

Apply the formula to the “entire column” using array formula

You can also apply a formula to the entire column using dynamic array formulas in Google Sheets. For example, you have a dataset, as shown below, where there is a formula in cell C2, and you want to apply the same formula to all the cells in Column C; till C10

The following method will fill the formula in the entire column (and you only need to put this formula in cell C2):

=Array Formula(A2:A10-B2:B10)

Because this is an array formula, it can handle an array of ranges and provide output in the “entire column;” that has a similar size as that of the input arguments

Note

One disadvantage of using an array formula when applying a formula to an “entire column” is that you can’t delete a part of the array. Nothing will happen when you try to delete content from any cell except the one in which you have added the formula. However, you can delete the entire array by making a selection of cell C2 and pressing the Delete key.

Leave a Comment