There are a lot of tricks you can use in Google Sheets to speed up your productivity.
Learning how to use Google Sheets properly will save you a lot of time as you work with spreadsheets.
One of the most fundamental things you need to learn to be efficient when working with Google Sheets is copying a formula down a column.
This is something that can be done in a matter of seconds. If you have a spreadsheet that is thousands of rows long, and you had to manually copy and paste a formula in each row, it would take forever.
There is a much faster way to do this.
In this tutorial, I will show you how to copy a formula down an entire column in Google Sheets.
Table of Contents
Copy a Formula Down with Fill Handle
The most efficient way to apply the same formula down an entire column in your spreadsheet is to use the fill handle to autofill your column with the formula.
The fill handle is the small blue square that appears in the bottom right corner of your cell that has a formula. If you double-click on this, it will fill the entire column with the formula.
Here are the steps to do this:
1. First enter your first formula in a new column next to your data. In this example, I have placed a basic SUM formula in column C
2. Next, click on the cell that contains your formula. You should see a small blue square in the bottom right corner. Double-click on this square
3. You should see your formula copy down the entire column
One important note about using this method is that the formula will only autofill down as far as the data adjacent to it.
Since my formula is in column C, if there is a cell in column B without any data in it, the formula will stop auto-filling at that spot.
Click and Drag Fill Handle to Copy Formula
If you have cells with no data in them, the previous auto-fill method won’t work because the formula will stop at the first blank cell.
But you can still use the fill handle to copy your formula down. Instead of double-clicking, you will have to click and drag down your column to copy and paste the formula down.
To do this:
- Click on the cell that contains your formula
- Click and hold on the fill handle (small blue square)
- Drag down your column for each row that you want to paste the formula to
The click and drag method is generally fine for small datasets, but if you have a lot of rows it will take too much time to drag down an entire column.
Inserting a Formula on an Entire Colum with ARRAYFORMULA
Another efficient method of applying a formula to an entire column is to use the ARRAYFORMULA function.
With this method, all you need to do is set your formula up on your entire data range and wrap it in ARRAYFORMULA.
This will automatically apply the formula to an entire column.
Here is an example of how this is done:
The formula used in this example is:
=ARRAYFORMULA(A2:A32+B2:B32)
After entering the formula in the first cell, it automatically copies down the entire range
One important thing to know about using this method is that with ARRAYFORMULA you cannot delete rows.
The formula exists only in the first cell, whereas in the previous method the formula was copied into each cell on every row down the column.
With the ARRAYFORMULA method, only the very first cell contains the formula for the entire column.
Closing Thoughts
I have covered several great methods for copying your formula down in Google Sheets.
Each method has its pros and cons over the others. You should take the time to learn all three methods.
If you have a dataset with no blank cells, the auto-fill fill handle method is likely sufficient for you.
If you have a small data set, clicking and dragging would be fine.
The ARRAYFORMULA method takes longer to master, but it is certainly the best method if you have large datasets.
More Google Sheets Tutorials:
How to Do Basic Math Formulas
Convert Formulas
How to Automatically Increment Numbers