If you have hundreds or thousands of 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. Read on to learn more.
Table of Contents
How To Copy a Formula to an Entire Column in Google Sheets
Here’s how to insert a formula in Google Sheets for an entire column:
- Select the cell that already has the formula.
- Place the cursor at the bottom-right part of the selection (the one that looks like a small thick blue circle). The cursor changes to a plus icon.
- Left-click and drag to cover all the cells in which you want to copy down the formula.
- Unpress the mouse button.
Using Absolute Reference Cells to Apply a Formula
Here’s a video I made about using reference cells to apply formulas across ranges in your sheet. This works for applying formulas in a whole column, a whole row, or across a wider range of cells.
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:
- Click the column header for the column you want to apply the formula to.
- Type the formula you wish to use into the FX bar and press “Enter.”
- Press Ctrl+D on your keyboard, or Ctrl+Enter also works.
Google Sheets Can Apply a Formula To an 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 for Google Sheets on how to copy the formula down by double-clicking the fill handle:
- Select the cell that already has the formula (cell C2 in this example).
- Place the cursor at the bottom-right part of the selection (the one that looks like a small thick blue square). You will notice that the cursor changes to a plus (+) icon.
- Double-click using the left-mouse key (or keypad).
The above steps would instantly fill the column with the same formula until the last filled cell.
Caution: While this method of applying a formula to the entire column works great, it has one limitation. It will only fill till the last contiguous filled cell. In case you have empty rows (an empty adjacent cell), using the autofill will stop right before it. One way to avoid this is to delete any empty row (or fill any empty cell) in the dataset.
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 for how to drag the formula down in Google Sheets to the entire column (also works for rows):
- Select the cell that already has the formula (cell C2 in this example).
- Place the cursor at the bottom-right part of the selection (the one that looks like a small thick blue square). You will notice that the cursor changes to a plus icon.
- Left-click and drag to cover all the cells in which you want to copy down the formula.
- Leave the mouse/keypad button.
While this method is more difficult to use if you have a large dataset, knowing how to drag formulas in Google Sheets can be handy, as you can use this method with smaller ones, too (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:
- Click on the cell with the formula.
- Hover the cursor over the blue circle at the bottom right of the cell until it turns into a cross.
- Click and drag the circle to the end of the row.
The keyboard shortcut to apply to the entire row is Ctrl+R.
How To Apply a Formula to an 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 dollar sign ($)Â 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 for Windows or Cmd + DÂ for Mac 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 have Google Sheets copy the formula to the entire column automatically. You just have to click the tick to apply it to the recommended cells.
Here’s how to create a formula in Google Sheets for an entire column using autofill:
- Enter your formula in the first cell of the column.
- Click enter and wait for the autofill prompt to appear.
- Click on the tick to confirm autofill.
Apply Formula to Entire Columns Using Array Formulas
Another quick and effective method for Google Sheets to copy formulas down is by using dynamic array formulas in Google Sheets.
This is one of the methods for how to apply formulas to entire columns in Google Sheets without dragging. It can be an effective method if you have to copy down formulas regularly.
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 with the array formula function 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 of the 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 will happen. You can, however, delete the entire array by selecting cell C2 and hitting the “Delete” key.
Frequently Asked Questions
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 on Windows 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:
- Click the column header for the desired column.
- Type the formula you want to use in the FX bar and press “Enter.”
- Press Ctrl+D on your Windows 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 tutorial on how to apply a formula to an entire column in Google Sheets useful! You can also check out a similar guide for calculated fields in Google Sheets or any of the related content below.
Related:
- 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?
7 thoughts on “How To Apply a Formula to an Entire Column in Google Sheets”
Very helpful for a spreadshidiot like myself.
i have a question. Because here we are only using number.
But what is we want to use UPPER on a list already present ? I’m wondering if there is a way to just do “in the column/row i want to use this formula in every cell?”
Hi, Command D does not work for this on a mac. It opens to save a bookmark! Any other suggestions
Try Option + D
It would be useful if you indicated whether arrayformula uses less memory or processes faster than the other approaches, which repeat the formula in every cell.
Hi Robert. That would depend on the function(s) being applied with the ARRAYFORMULA and how large the range is. Usually, an ARRAYFORMULA will process faster.
I’m looking to run an =FREQUENCY function that is pulling data that is numeric and alpha. The function is working for the numeric cells but not anything that has letters.