Apply Formula to Entire Column (Copy Down) – Excel & Google Sheets

This tutorial demonstrates how to apply a formula to an entire column in Excel and Google Sheets.

 

 

Fill Formula Down Entire Column

Drag Fill Handle

Say you have the formula in cell D2 that sums values from Columns B and C.

 

 

If you want to apply the same formula to the entire column (through Row 8), position the cursor in the bottom right corner of the cell with the formula (D2), and drag it to the end of column (Row 8).

 

 

As a result, the formula is copied to the rest of cells in the Column D.

 

 

Double-Click Fill Handle

Apart from dragging, you can also double-click the fill handle to apply the formula to the entire column.

 

 

In this case, the last row for copying the formula is determined by the number of rows in the adjacent column. Since Column C has 8 rows populated, the formula in Column D is applied through Row 8.

 

 

Note: See how to lock a cell reference in the formula if needed.

Copy-Paste Formula

Another approach for copying the formula is to use the copy and paste functionality.

1. Right-click the cell from which you want to copy the formula (D2), and choose Copy (or use the keyboard shortcut CTRL + C).

 

 

2. On your keyboard, press CTRL + SHIFT + END to select the paste range, right-click the selected area, and choose Paste (or use the keyboard shortcut CTRL + V).

 

 

Note: You can use this way to select the entire column for formula copying since the column with the formula is the last one populate. If there were more columns on the right side, Excel wouldn’t have selected properly, and you would need to select the range manually. The selection here is made based on the number of rows in the left adjacent column (through Row 8).

Note: There is also the CTRL + Down Arrow command, which selects the entire column under the selected cell down to the first populated row. In this case, since you don’t have any cells populated under D2, it will select all available rows in Column D (1,048,576).

As a result, the formula is copied to the rest of Column D.

 

 

Note: If a formula you want to apply is not at the beginning of the column, but somewhere in the middle, you can use CTRL + SPACE to select an entire column and paste the formula.

Apply Formula to Entire Column in Google Sheets

Except for CTRL + SHIFT + END, all other methods to apply a formula to an entire column can be used in Google Sheets as well.