Al Ikram Amit
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.
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$5Here,
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.
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.
- 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.
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.
- 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.
Read More: How to Copy Formula Down with Shortcut in Excel
Similar Readings
- How to Calculate Data Across Worksheets with Formulas in Excel
- Excel VBA to Copy Formula with Relative Reference (A Detailed Analysis)
- How to Copy Formula in Excel without Dragging (10 Ways)
- How to Copy Formula in Excel to Change One Cell Reference Only
- Creating and Copying Formula Using Relative References in Excel
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-G5Here,
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.
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$5Here,
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.
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
- How to Copy VLOOKUP Formula in Excel (7 Easy Methods)
- How to Copy a Formula across Multiple Rows in Excel (5 Ways)
- [Fixed] Excel Not Copying Formulas, Only Values (4 Solutions)
- How to Copy SUM Formula in Excel (6 Easy Methods)
- How to Copy a Formula in Excel Without Changing Cell References
- Making Same Change to Multiple Worksheets (9 Examples)
