How to Apply a Formula to an Entire Column in Google Sheets

Google Sheets has some very useful formulas that can help you automate your work and save a lot of time.

Also, when working with formulas in Google Sheets, it is often necessary to apply the same formula across columns (because most of the time data is arranged vertically).

If you have hundreds or thousands of cells in a column, you cannot manually apply formulas to each cell. There are several ways to apply formulas across columns in Google Sheets.

In this short tutorial, we’ll show you several ways to copy formulas and apply them across Google Sheets columns in just a few steps. If you want to know more, read on.

How to Copy a Formula to an Entire Column in Google Sheets

Here’s how to insert formulas across columns in Google Sheets:

1. Select a cell that already contains a formula
2. Place the cursor in the lower right part of the selection (the part that looks like a small thick blue circle). The cursor will change to a plus icon.
3. Left click and drag to cover all cells where you want to copy the formula.
4. Do not press the mouse button.

Using a Keyboard Shortcut to Apply a Formula To an Entire Column in Google Sheets Without Dragging

The quickest and easiest way to apply formulas across columns is:

1. Click the column header of the column you want to apply the formula to
2. Type the formula you want to use into the FX bar and press Enter
3. Press Ctrl+D on your keyboard Ctrl+Enter also works

 

Google Sheets Can Apply a Formula To an Entire Column by Double-Clicking the Fill Handle

Google Sheets has a fill handle feature that saves time when you need to apply formulas across columns (only works on columns, not rows).

Suppose you have a data set as shown below, with a formula in cell C2, and apply this same formula to all cells in column C (up to C10).

Here are the steps to copy the formula by double-clicking the fill handle in Google Sheets:

1. Select a cell that already contains a formula (cell C2 in this example)

1. Place the cursor in the lower right part of the selection (the part that looks like a small, thick blue square). You will see the cursor change to a plus icon.
   
2. Double-click using the left mouse key (or keypad)

The above procedure will instantly fill the column with the same formula up to the last cell entered.

 

Note: This method of applying formulas across columns works well, but it has one limitation. Fills only up to the last consecutive filled cell. If there is an empty row (empty adjacent cell), the use of autofill will stop just before it. One way around this is to remove empty rows (or fill empty cells) in the dataset.

Google Sheets Can Copy a Formula to an Entire Column Using the Fill Handle

If your data set is small, Google Sheets allows you to use fill handles to drag formulas to fill cells reliably and apply the same formula to entire columns.

This method is useful when you have blank cells/rows in your dataset and cannot copy formulas using the double-click method.

Here are the steps on how to drag a formula across columns in Google Sheets (also works for rows):

1. Select a cell that already contains a formula (cell C2 in this example)
2. Place the cursor in the lower right part of the selection (the part that looks like a small, thick blue square). You will see the cursor change to a plus icon.
3. Left click and drag to cover all cells where you want to copy the formula.
4. Release the mouse/keypad button.

 

If you have a large dataset, this method is a bit tricky to use, but knowing how to drag formulas in Google Sheets can help with smaller datasets (tens or hundreds of rows) data) can be very successfully used this way).

Apply a Formula to an Entire Row in Google Sheets

You can also use the above method to apply formulas to whole rows instead of columns like this:

1. Click the cell containing the formula.
2. Hover your cursor over the blue circle in the lower right corner of the cell until it turns into a cross.
3. Click and drag the circle to the end of the line.

 

The keyboard shortcut that applies to the entire row is Ctrl+R

How to Apply a Formula to Entire Column Without Changing Cell References

To preserve cell references in autofilled formulas, you must use absolute references in the first formula. This involves putting a $ in front of each part of the reference you want to keep for example:

• $A1 holds the formula in column A
• A$1 keeps the formula on row 1
• $A$1 keeps the cell reference as A1

Then you can autofill columns using the Ctrl+D keyboard shortcut for Windows or Cmd+D for Mac, as in the following GIF:

 

How to Apply a Formula to an Entire Column in Google Sheets Using Suggested Autofill

Spreadsheets often suggests AutoFill, which automatically copies formulas across columns in Google Sheets. Simply click the check mark to apply it to the recommended cells.

Here’s how to use AutoFill to create a formula for an entire column in Google Sheets:

1. Enter the formula in the first cell of the column.
2. Click Enter and wait for the autofill prompt to appear
3. Click the checkmark to confirm autofill.

 

Apply Formula to Entire Columns Using Array Formulas

Another quick and effective way to copy formulas in Google Sheets is to use dynamic array formulas in Google Sheets.

This is one way to apply formulas across columns in Google Sheets without dragging and is very effective if you often need to copy formulas.

Suppose you have a data set as shown below, with a formula in cell C2, and apply this same formula to all cells in column C (up to C10).

below is a formula that uses the arrayformula function to fill the entire column up to the specified row (just type this formula into cell C2):

=ArrayFormula(A2:A11-B2:B11)

Since this is an array formula, it can process an array of ranges to give you a whole column of output (which is the same size as the input argument).

One of the drawbacks of using an array formula to apply a formula to an entire column is that you can’t remove part of the array. If you try to remove content from any cell other than the one you added the formula to, nothing happens. However, you can delete the entire array by selecting cell C2 and pressing the Delete key.

Frequently Asked Questions

How Do I Copy a Formula Down in Google Sheets?

To copy a formula in Google Sheets, use the fill handle (the small blue box in the bottom right corner of the selected cells) and drag it over the appropriate cells. Alternatively, you can use the keyboard shortcut Ctrl+D.

What Is the Shortcut for Copying a Formula Down in Google Sheets?

The keyboard shortcut for copying formulas is Ctrl+D or Cmd+D on Mac.

How Do I Copy a Formula All the Way Down Without Dragging in Google Sheets?

To copy the formula down without dragging, you need to do the following:

1. Click the column header of the desired column
2. Enter the formula you want to use for the FX bar and press Enter
3. Press Ctrl+D on your keyboard (Cmd+D on Mac)

How Do You Copy a Formula in Google Sheets Without Changing Cell References?

To copy formulas without changing cell references, you must use absolute references in the first formula. To do so, you must put a $ sign before the part of the cell reference that you don’t want to change. For example, if you want cell A1 to always be an argument in a formula, you should type “$A$1” instead of “A1.

Why Is My Formula Not Copying Down?

If the Ctrl+D keyboard shortcut doesn’t work, try using the fill handle instead. The same is true vice versa.

Wrapping Up

Therefore, there are several methods you can use to apply formulas across columns in Google Sheets.

I hope you found this tutorial on how to apply formulas across columns in Google Sheets helpful. See a similar guide on calculated fields in Google Sheets and related content below.