Don’t forget to share this post

When working with spreadsheet data, you often need to apply formulas to entire columns of data. Fortunately, there are at least five ways to do this in Google Sheets: using the ARRAYFORMULA function, dragging the fill handle, double-clicking the fill handle, using a keyboard shortcut, and accepting the suggested Autofill. These methods can be used to apply formulas or built-in functions to columns, rows, or entire tables.

In this guide, you will learn three different ways of applying a formula to an entire column in Google Sheets. First, you will learn how to use the ARRAYFORMULA function to apply a formula to an entire column when you press ‘Enter’. Second, you will learn how to copy the formula to a whole column by grabbing and dragging the fill handle. Finally, you will learn three more methods you can use to apply a formula to an entire column without dragging: using a keyboard shortcut, accepting the suggested Autofill, and double-clicking the fill handle.

Apply Formula to Entire Column in Google Sheets When You Hit Enter

If you want your formula to be copied down the entire column when you hit enter, you can use the ARRAYFORMULA function.

1. Type Formula

Type the formula you want in the column’s first empty cell. Below, I want to use a formula to join the first and last names to get the full name.

How To Apply Formula To Entire Column in Google Sheets Add Formula
How To Apply Formula To Entire Column in Google Sheets - Add Formula

2. Add ARRAYFORMULA Function

Press Ctrl + Shift + Enter (Windows) or Cmd + Shift + Enter (Mac) to automatically wrap the ARRAYFORMULA function around your formula.

How To Apply Formula To Entire Column in Google Sheets Add ARRAYFORMULA Function
How To Apply Formula To Entire Column in Google Sheets - Add ARRAYFORMULA Function

3. Press ‘Enter’

Press ‘Enter’ to see the result. The formula was instantly applied down to the column’s last row.

How To Apply Formula To Entire Column in Google Sheets Press Enter
How To Apply Formula To Entire Column in Google Sheets - Press Enter
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

Apply Formula to Entire Column in Google Sheets by Dragging

Follow the steps below to copy a formula down a column by gabbing and dragging the fill handle.

1. Type Formula

In the first cell, type the formula you want in that column. In the example below, I want to calculate the interest (10%) corresponding to each amount in the existing column. Press ‘Enter’ once the formula is complete.

How To Apply Formula To Entire Column in Google Sheets Type Formula Press Enter
How To Apply Formula To Entire Column in Google Sheets - Type Formula & Press Enter

2. Grab & Drag Fill Handle

Grab the cell’s fill handle and drag it down to the last row.

How To Apply Formula To Entire Column in Google Sheets Grab Drag Fill Handle
How To Apply Formula To Entire Column in Google Sheets - Grab & Drag Fill Handle

Apply Formula to Entire Column in Google Sheets Without Dragging

In addition to using the ARRAYFORMULA function, there are three other ways to apply a formula to an entire column without dragging. You can use a keyboard shortcut, accept the suggested Autofill, or double-click the fill handle.

Use Keyboard Shortcut

If you want the formula to apply to all current and future rows in the column, use the keyboard shortcut, as shown below.

1. Type Formula

Type your formula in the first cell of the column. In the example below, I want to use the TRIM function to clean up the names in the first column, which contain unwanted spaces.

How To Apply Formula To Entire Column in Google Sheets Type Formula
How To Apply Formula To Entire Column in Google Sheets - Type Formula

2. Select Column

Select the column containing the cell with the formula.

How To Apply Formula To Entire Column in Google Sheets Select Column
How To Apply Formula To Entire Column in Google Sheets - Select Column

3. Use Shortcut

Use the keyboard shortcut to apply the formula to the entire column, including any new rows you insert: Ctrl + d (Windows) or Cmd + d (Mac). The formula will be copied to the entire column and applied to new rows as soon as they are added.

How To Apply Formula To Entire Column in Google Sheets Use Keyboard Shortcut
How To Apply Formula To Entire Column in Google Sheets - Use Keyboard Shortcut
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

Use Autofill

Another way to apply a formula to a column without dragging is to accept the suggested Autofill. You will see the suggestion as soon as you press ‘Enter’ after typing your formula.

1. Type Formula & Press ‘Enter’

Type the formula into the first cell in the new column and press ‘Enter’.

How To Apply Formula To Entire Column in Google Sheets Formula Enter
How To Apply Formula To Entire Column in Google Sheets - Formula & Enter

2. Accept Suggested Autofill

As soon as you press ‘Enter’, you should see the Autofill suggestion. Click the tick mark or press ‘Enter’ to accept the suggested Autofill.

How To Apply Formula To Entire Column in Google Sheets Accept Suggested Autofill
How To Apply Formula To Entire Column in Google Sheets - Accept Suggested Autofill

Double-Click Fill Handle

If you don’t like dragging, you can double-click the fill handle to copy the formula down the column automatically. However, this only works if there is an existing column to the left of the formula.

1. Type Formula & Press ‘Enter’

In the new column’s first empty cell, type the formula you want to use and press ‘Enter’. Below, I want to sum the amount and the interest to get the total.

How To Apply Formula To Entire Column in Google Sheets Formula Enter 2
How To Apply Formula To Entire Column in Google Sheets - Formula & Enter

2. Double-Click Fill Handle

Select the cell containing the formula and double-click on the fill handle. The formula will be copied down to the last row.

How To Apply Formula To Entire Column in Google Sheets Double Click Fill Handle
How To Apply Formula To Entire Column in Google Sheets - Double-Click Fill Handle

Conclusion

As you have seen, applying formulas or built-in functions to entire columns in Google Sheets is easy. You now know how to use the ARRAYFORMULA function to apply formulas to whole columns or rows instantly. You also know how to do it using the fill handle, either by grabbing and dragging it or by double-clicking on it.

In most cases, after you write your formula and press ‘Enter’, Google Sheets will automatically show the suggested Autofill to copy the formula down instantly. Finally, you know a keyboard shortcut that will not only apply the formula to the entire column but also to any rows added in the future.

To learn more about working with Google Sheets formulas and data, check out our guides on:

Maria Del Olmo
Originally published Mar 22 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets