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.


Download Practice Workbook

You can download the practice workbook from the following download button.


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.  

a large data on ‘Global Land and Ocean Temperature Anomalies’


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

enter the a formula

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

Copy Formula Down Entire Column by Using Fill Handle


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.

Copy Formula Down Entire Column by Using Fill Handle

Read More: How to Copy Formula to Entire Column in Excel (7 Ways)


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.

dragging the white plus sign downward

  • 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.

follow the steps to make a copy for the rest of the cells

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

notice in the below image that the rest of the cells contain a similar kind of formula

Read More: How to Copy Formula Down Without Incrementing in Excel


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.

select the whole column by dragging the Excel cursor

  • 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.

instead of hitting the Enter button, we will choose Ctrl + Enter

  • Now see the result in the image below.

the result in the image

Read More: How to Copy Formula Down with Shortcut in Excel


Similar Readings


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

To make a calculation for the entire column we have to put in the a formula

  • 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.

notice in the formula column that the cells below D5 do not have a formula

Read More: How to Copy Array Formula in Excel (3 Easy Methods)


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.

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.

Right click on the mouse and select Paste Special

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

In the Paste Special pop-up, we will select Formulas and click OK

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

formula being copied for rest of the cells

Read More: How to Copy Formula and Paste as Text in Excel (2 Ways)


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

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

Leave a reply

ExcelDemy
Logo