In this tutorial, you will learn how to apply a formula to an entire column in google sheets.
Often when working in Google Sheets, you’ll find you need to perform the same calculation on an entire column of data. Retyping the formula manually for each row would be impossible if you have more than a few rows of data, and even using the auto-fill feature to drag down across cells can be cumbersome. Fortunately, there are a few different methods in Google Sheets that can make the process of applying a formula to an entire column much simpler and faster. Read on to learn more.
Suggested Autofill
Quick Navigation
Probably the easiest option is the suggested autofill method. If the feature is turned on, this will work in most cases, but it has some limitations, and because this feature is an automatic suggestion, it’s not possible to activate it manually if for some reason it doesn’t trigger when you want it to.
Here’s how to use suggested autofill:
Step 1
Ensure the feature is turned on by opening the Tools menu, hovering over Autocomplete, and make sure there’s a checkbox beside the Enable Autocomplete option
Step 2
Now, type your formula in the first cell of the column you want to apply it to and hit enter. The autofill suggestion box should appear. If it does, the suggested formulas will display ghosted in the column. Click on the checkmark in the popup to apply the formula to the entire column
Step 3
Note that the formula will only autofill down as far as there are filled cells in adjacent or contiguous columns in the sheet, so ensure you’ve filled at least one of these columns down as far as you want to apply the formula
Keyboard Shortcuts
Sometimes the autofill suggestions fail to work for a number of reasons. When this happens, it’s best to use the keyboard shortcut method instead of attempting to make the autofill work. Here’s how:
Step 1
If you haven’t already, type your formula in the first cell of the column you want to apply it to
Step 2
Select the column you want to apply the formula to, including the first cell with the formula. The quickest way to do this is typically to select a blank cell in the column then hit CTRL + SPACE on Windows or Mac to select the data range of that column. Again, Google Sheets will look at the length of adjacent and contiguous columns to determine how far down to select.
Step 3
If there are headers or other data above the formula you want to fill down, unselect those cells by holding down SHIFT and clicking on each cell to remove it from the selection
Step 4
Hit CTRL + d on Windows or CMD + d on Mac to fill the formula down to the end of the selection
Summary
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to apply a formula to an entire column in google sheets. Want more? Check out all the Google Sheets Tutorials.