- 15 min read
- Google Sheets
How To Apply a Formula To an Entire Column in Google Sheets
Written by Maria Del Olmo
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.
2. Add ARRAYFORMULA Function
Press Ctrl + Shift + Enter (Windows) or Cmd + Shift + Enter (Mac) to automatically wrap the ARRAYFORMULA function around your formula.
3. Press ‘Enter’
Press ‘Enter’ to see the result. The formula was instantly applied down to the column’s last row.
When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.
READ MOREApply 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.
2. Grab & Drag Fill Handle
Grab the cell’s fill handle and drag it down to the last row.
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.
2. Select Column
Select the column containing the cell with the formula.
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.
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 MOREUse 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’.
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.
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.
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.
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: