How to Apply Formula to Entire Column in Google Sheets

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

And when working with formulas in Goole Sheets, you would often need to apply the same formula for an entire column (since most of the time our data is arranged vertically).

If you have hundreds or thousands on cells in a column, you can’t manually apply a formula to each cell. There are ways you can apply a formula to an entire column in Google Sheets.

In this short tutorial, I will show you a couple of ways to copy down and apply a formula to an entire column in Google Sheets in a few steps.

So let’s get started on our Google Sheets apply formula to entire column guide!

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

The quickest and easiest way to apply a formula to an entire column is to:

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

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

Google Sheets has a fill handle feature that can save you time when you need to apply a formula to an entire column (it only works with columns and not rows).

Suppose you have a dataset as shown below, where there is a formula in cell C2 and you want to apply this same formula to all the cells in Column C (till C10).

Below are the steps to do this:

1. Select the cell that already has the formula (cell C2 in this example)
2. Place the cursor at the bottom-right part of the selection (the one that looks like a small thick blue square). You would notice that the cursor changes to a plus icon.
3. Double-click using the left-mouse key (or key-pad)

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

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

If you have a smaller dataset, Google Sheets can drag a formula using the fill handle to make sure it fills the cells and apply the same formula to the entire column.

This method can be useful when you have some blank cells/rows in the dataset and you can’t use the double-click method to copy down formulas.

Below are the steps to drag and copy the formula to the entire column (also works for rows):

1. Select the cell that already has the formula (cell C2 in this example)
2. Place the cursor at the bottom-right part of the selection (the one that looks like a small thick blue square). You would notice that the cursor changes to a plus icon.
3. Left-click and drag to cover all the cells in which you want to copy down the formula.
4. Leave the mouse/keypad button.

While this method is a bit difficult to use if you have a large dataset, knowing how to drag formulas in Google Sheets can be handy as you can very well use this with smaller ones (tens or a few hundred rows of data).

Apply a Formula to an Entire Row in Google Sheets

You can also use the above method to apply the formula across rows instead of columns, like so:

The keyboard shortcut to apply to the entire row is Ctrl+R

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

To keep the cell references for an auto-filled formula, you need to use absolute references in the initial formula. This involves putting a $ in front of each part of the reference you want to keep. For example:

• $A1 would keep the formula in column A
• A$1 would keep the formula in row 1
• $A$1 would keep the cell reference as A1

Then you could just use the Ctrl+D keyboard shortcut to autofill the column, like in the following GIF:

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

Often, the spreadsheet will suggest an autofill to apply a formula to an entire column in Google Sheets. You just have to click the tick to apply it to the recommended cells

Apply Formula to Entire Column Using Array Formula

Another quick and effective method for Google Sheets to a copy formula down is by using dynamic array formulas in Google Sheets.

This method doesn’t need a lot of dragging and using the mouse and can be quite effective if you have to copy down formulas often.

Suppose you have a dataset as shown below, where there is a formula in cell C2 and you want to apply this same formula to all the cells in Column C (till C10).

Below is the formula that will fill the formula in the entire column down to the specified row (and you only need to put this formula in cell C2):

=ArrayFormula(A2:A11-B2:B11)

Since this is an array formula, it can handle an array of ranges and then give the output in the entire column (which is of the same size as that if ṭhe input arguments).

One drawback of using the array formula to apply the formula to an entire column is that you will not be able to delete a part of the array. If you try to delete content from any cell except the one in which you have added the formula, nothing would happen. You can, however, delete the entire array by selecting cell C2 and hitting the Delete key.

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

How Do I Copy a Formula Down in Google Sheets?

You can copy a formula down in Google Sheets by using the fill handle (the little blue box in the bottom right corner of a selected cell) to drag over the applicable cells. Or you can use the keyboard shortcut Ctrl+D.

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

The keyboard shortcut to copy a formula down is Ctrl+D or Cmd+D on Macs.

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

To copy a formula all the way down without dragging you have to:

1. Click the column header for the desired column
2. Type the formula you want to use in 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?

You have to use absolute references in the initial formula to copy a formula down without changing cell references. To do so, you have to put $ symbols before any part of the cell reference you want to remain the same. For example, if you want cell A1 to always be an argument in the formula, instead of typing A1 you would have to type $A$1.

Why Is My Formula Not Copying Down?

If the Ctrl+D keyboard shortcut isn’t working, try using the fill handle instead and vice-versa.

Wrapping Up

So there are some methods you can use to apply a formula to an entire column in Google Sheets.

Hope you found this Google Sheets apply formula to entire column tutorial useful!


You may also like the following Google Sheets tutorials:

• How to Autofit Column Width in Google Sheets
• How to Copy Values and Not Formulas in Google Sheets
• How to Hide Columns In Google Sheets
• How to Group Rows & Columns In Google Sheets
• Apply Conditional Formatting based on Another Cell Value in Google Sheets
• How to Combine Cells in Google Sheets
• How to Sum a Column in Google Sheets
• How to VLOOKUP from Another Sheet in Google Sheets?