5 methods to apply a formula to an entire column
- Using the Autofill suggestion
- By dragging the cell’s handle
- Using the shortcut Ctrl + D
- Using Ctrl + Shift + Down Arrow”
- Using Array Formula
Method 1: Using the Autofill suggestion
Google sheets usually give a smart auto-suggestion when it identifies a formula applied.
For instance, say you have a list of items, their original price, and discount percentage. Let’s apply a formula to calculate the discounted price of an item.
This is a super simple method if you want to apply a formula to entire column without dragging.
Step 1: Apply the formula and press Enter
Apply the formula for your calculations in your desired row and press Enter.
Step 2: Click the check mark in the Autofill dialog box
After clicking enter, you’ll see an Autofill dialog box pop up. You can either press Ctrl + Enter or click the checkmark to autofill the formula in all your rows with data.
Google Sheets then auto-fills the formula for all the rows with data.
Method 2: By dragging the fill handle
This is one of the easiest ways to apply a formula to the entire column unless you have a large data set. The reason is that you may need to drag the border selection down through a long column. Regardless, this method should work just fine for most small and medium sized data sets.
Step 1: Apply the formula and select that cell
Apply the formula and select the cell. The selected cell is called the Active Cell which is marked with a blue border.
Step 2: Drag the cell handle till the desired row
The small blue square at the bottom right corner is called the Fill Handle.
Now, click the cell handle and drag through all the cells that you want to apply the formula to in that column.
Method 3: Using Ctrl + D
This is like the previous method. You can use the keyboard shortcut Ctrl + D to apply the formula to the column.
Step 1: Select the range of cells
Highlight the cell with the formula. Then, drag it using the cell handle until you’ve selected all cells in the column to which you want to apply the formula.
Step 2: Press Ctrl + D
Now press Ctrl + D to look at the results.
Method 4: Using the Ctrl + Shift + Down Arrow option
Though easy for a few rows, the above steps will become difficult when there are many rows. This step, combined with the previous method, will apply the formula to the entire column.
Step 1: Highlight the cell with the formula and press Ctrl + Shift + Down Arrow
Highlight the cell with the formula and press Ctrl + Shift + Down Arrow. This will automatically select and highlight your entire column.
Step 2: Click Ctrl + D
Click Ctrl + D which will apply the formula to all the selected cells.
For instance, let’s calculate the price by multiplying the price of each item by its quantity.
You’ll have then successfully applied the formula to all the rows in a column. Columns corresponding to empty rows will display 0 as output.
Method 5: Using Array Formula
Instead of selecting the range of cells, you could enter the range of cells in the formula itself.
Step 1: Apply the formula and determine the range of cells
Apply the required formula and determine the range of cells to which you want to apply this formula. You can insert this range of cells in the formula.
Let’s use the same data we used previously. We’ll apply the formula to the entire column by entering a cell range, which in this case is D2 to D1000.
Step 2: Enter the formula
There is a slight change in your usual calculation formula for inserting the range cells.
For this example, you can see that the formula B2*C2 has changed to B2:B1000*C2:C1000.
This is nothing but including the range for columns B and C from row 2 to row 1000.
- B2 represents the beginning of the range of the first column. (The alphabet represents the column and the number represents the row).
- B1000 represents the end of the range of the first column.
- * is a multiplication operator (Modify it according to your calculation. For instance, if you want to add the two numbers, you can use ‘+’).
- C2 represents the beginning of the range of the second column.
- C1000 represents the end of the range of the second column.
Step 3: Press Ctrl + Shift + Enter
After entering the formula, press Ctrl + Shift + Enter to see the formula turn into an Array formula.
Press Enter to see the changes you’ve applied.
You can use the above methods to apply a formula to a few rows or even the entire column. No more copy-pasting or dragging the cell handle for 14000 rows!