Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

# How to Copy Formula Down Entire Column in Excel (5 Easy Ways)

The wide range popularity of Excel has grown over time due to its versatility in the use of formulas and functions. But most the Excel users don’t use formulas for one cell only. All most all of them do copy the formula for the rest of the column. So, in this article, we will discuss the nitty gritty of Excel fill handle under the topic heading of Excel Copy Formula Down Entire Column.

## 5 Simple Methods to Copy Formula Down Entire Column in Excel

Suppose we have a large data on ‘Global Land and Ocean Temperature Anomalies’ where the data range stretches back B4:C141. We have columns of Year, Actual Temp, and Temp. Deviation like the following image. We are going to calculate Temp. Deviation and demonstrate how to copy the formula down the entire column.

### 1. Copy Formula Down Entire Column by Using Fill Handle

A Fill Handle is a feature in Microsoft Excel that enables users to expand formulae or values to fill numerous numbers, dates, or perhaps even text to neighboring cells. The Fill Handle is a tiny black box in the worksheet’s bottom-right corner. It initially appeared in the Excel 2010 edition.

#### 1.1 Dragging Fill Handle

Excel users can utilize the Fill Handle and its features by dragging the Fill Handle in the direction where they prefer to copy the cells.

📌 Steps:

• First, in cell D5, we will enter the following formula to calculate Temp. Deviation
`=C5-\$G\$5`

Here,

C5= Actual Temperature

\$G\$5= Average Temperature of 1901-2000

Temperature Deviation=Actual Temperature – Average Temperature

• Now we drag the fill handle downward to copy the formula for the later cells, see the image below.

#### 1.2 Double-Clicking on Fill Handle

In addition to the previous method, Excel users can also utilize the Fill Handle in different ways without dragging the fill handle. By double-clicking on the Fill Handle, we can copy the formula into the rest of the cells in a column.

📌 Steps:

• Like the previous method, we first enter a formula in cell D5 >> Enter >> double click on the fill handle.

### 2. Copying Formula Down Entire Column by Using Excel Ribbon

In this method, we will demonstrate how to copy the formula down the entire column from Fill and Down Ribbon under Home Tab.

📌 Steps:

• Firstly, enter the necessary formula in the first cell of your column>> make the selection for the rest of the column by dragging the white plus sign downward just like the image below.

• Now follow the steps to make a copy for the rest of the cells in the column: Home>> Editing>>Fill>> Down.
• Or press Ctrl + D.

• Finally, notice in the below image that the rest of the cells contain a similar kind of formula.

### 3. Using Keyboard Shortcut to Copy Formula Down Entire Column

By using another shortcut key other than Ctrl + D, we can copy the formula for the entire column simultaneously.

📌 Steps:

• First, select the whole column by dragging the Excel cursor to select.

• Now, as we have started selecting from D5, if we enter a formula, that formula will be inserted in the D5 cell.
• After entering the formula instead of hitting the Enter button, we will choose Ctrl + Enter.

• Now see the result in the image below.

### 4. Copying Formula Down Entire Column by Using Array Formula

Array formula denotes a wide range of cells in single or multi-dimensional data collection for example C5:C20 is an array.

📌 Steps:

• To make a calculation for the entire column we have to put in the following formula:
`=C5:C20-G5`

Here,

C5:C20= Array of Actual Temperature

G5= Average Temperature of 1901-2000

• After hitting Enter the calculation for the entire column will be done with a single click.
• Now notice in the formula column that the cells below D5 do not have a formula. All the formulas are in the first cell of Column D.

### 5. Copying Formula Down Entire Column by Copying and Pasting the Formula Cell

Lastly, we can copy and paste the formula in the rest of the cells by using the Paste Special option in Excel.

📌 Steps:

• Firstly, like the previous methods, in cell D5, we will enter the following formula to calculate Temp. Deviation
`=C5-\$G\$5`

Here,

C5= Actual Temperature

\$G\$5= Average Temperature of 1901-2000

Temperature Deviation=Actual Temperature – Average Temperature

• Now we will copy the cell D5 with its formula.

• Now we will select the rest of the cells in column D.
• At this moment Right click on the mouse>> select Paste Special.

• In the Paste Special pop-up, we will select Formulas>> click OK.

• Finally, we got the formula being copied for rest of the cells and the completed column will look like the image below.

## Conclusion

Follow these steps and stages on the topic excel copy the formula down the entire column. You are welcome to download and use the workbook for your practice. If you have any questions, concerns, or suggestions, please leave them in the comments section of our blog ExcelDemy.

## Related Articles

#### Al Ikram Amit

Hi there. I am Amit, completed my study from BUET. Currently, I am working as an Excel & VBA Content Developer at ExcelDemy. Now you can see my articles in ExcelDemy blog.

We will be happy to hear your thoughts