To copy formulas in Excel is one of the easiest works. Microsoft Excel offers many ways to do the same work and in this article, we’ll get to learn seven suitable ways how we can copy formula to entire column in Excel effectively with appropriate illustrations.
How to Copy Formula To Entire Column in Excel: 7 Suitable Ways
Let’s say, we have a dataset where different types of Fruits and their price in January are given in Column B, and Column C respectively. We need to find the rate of increase of these items. To do this we will use one specific formula and copy the same formula to the entire column. Here’s an overview of the dataset of our today’s task.
1. Use of the Fill Command to Copy Formula To Entire Column
Let, the price of Apple is $1391.00 that has been given in cell C5. Now, we want to calculate the 10% increased price of Apple in Column D. To determine the increasing price of Apple and other fruits, we’ll use the Fill Command. Let’s follow the instructions.
Step 1:
- First, select cell D5.
- Now, type the formula in Formula Bar. The formula is,
=C5*10%
- After typing the formula in the Formula Bar, press Enter on your keyboard and you will get the increasing price of Apple in cell D5 and the increasing price is $139.10.
Step 2:
- Now select cell C5 to cell C14.
- From your Home Tab, go to,
Home → Editing → Fill → Down
- After following the above instruction, you will get the increasing price of the other fruits.
Read More: How to Copy Exact Formula in Excel
2. Apply the Autofill Handle to Copy Formula in Excel
In this method, we’ll learn how to copy the formula to the entire column by using the Autofill Handle. Let’s follow the steps.
Steps:
- In cell D5, type the formula,
=C5*10%
- After typing the formula in the Formula Bar, press Enter on your keyboard and you’ll get the return of the formula in cell D5. The return value of the formula is $131.10.
- Now, place the cursor on the Bottom-right in cell D5, and a Plus-sign(+) pops up. Then Double-Click on your Left-Button on the Plus sign(+) and instantly you’ll get your desired result.
Read More: How to Copy SUM Formula in Excel
3. Using the Keyboard Shortcuts to Copy Formula To Entire Column
Here, we’ll learn how to copy formulas down in Excel using keyboard shortcuts. For this, follow these steps.
Step 1:
- First of all, select cell D5 and type the formula in the Formula bar. The formula is,
=C5*10%
- Now, press Enter on your keyboard and you will get the increasing price of Apple. The increasing price of Apple is $139.10.
Step 2:
- After that, select cell D5 to cell D14 and press Ctrl+D on your keyboard.
- Then, press Enter on your keyboard and you’ll get your desired output in column D.
4. Insert an Array formula to Copy Formula To Entire Column
Let’s say, we are applying the Array formula in our dataset to copy the formula to the entire column. Let’s follow these steps to learn!
Steps:
- First, select cell D5 and type the formula in the Formula Bar. The formula is,
=C5:C14*20%
- Now, press Enter on your keyboard and you’ll get your desired output in column D which is given in the screenshot.
Read More: How to Copy a Formula in Excel with Changing Cell References
5. Use the Copy-Paste Method to Copy Formula To Entire Column
We are using the Copy-Paste method to copy formulas in Excel to the entire column. We want to calculate the increasing 20% price of the fruits that have been given in column C5. This method is discussed below.
Step 1:
- In cell D5, type the formula. The formula is,
=C5*20%
- After typing the formula in the Formula Bar, press Enter and you’ll get the return of the formula is $278.20 in cell D5.
Step 2:
- Now, select cell D5 and press Ctrl+C on your keyboard.
- After that, choose the entire column from cell D5 to cell D14.
- Finally, press Ctrl+V on your keyboard and you get your desired output that has been given in the screenshot.
6. Apply the Ctrl+Enter Method to Copy Formula To Entire Column
In our dataset, the price of fruits for January is given in column C. Let the fruit price be increased by 30% for next month. Now, we’ll calculate the increasing price of the fruits by using Ctrl + Enter Command on our keyboard to copy the formula to the entire Column. Follow the steps below to learn!
Steps:
- First, select cell D5 to cell D14.
- Now, type a formula in the Formula Bar. The formula in the Formula Bar is,
=C5*30%
- After typing the formula in the Formula Bar, press the Ctrl+Enter buttons on your keyboard simultaneously. Then you’ll get your desired result in column D.
7. Create A Table to Copy Formula Automatically in Excel
After learning the above Methods, we’ll learn to copy formulas automatically to the entire column by creating a table. For this, follow the steps below.
Steps:
- First, select the range where we want to create the table.
- After selecting the range, press Ctrl+T on your keyboard, and a Create Table box pops up. Press OK to continue.
- After that, select any blank cell on the selected table where we’ll apply our formula. Let’s say, in cell D9, we want to apply the formula. In that cell, type the formula that has been given below.
=[@[January]]*30%
- After completing the above steps, press Enter on your keyboard and you’ll get your desired output that has been given in the screenshot.
Read More: How to Copy a Formula in Excel with Changing Cell References
Things to Remember
👉 To copy formulas in Excel to the entire column, we are applying keyboard shortcuts like Ctrl+C and Ctrl+V.
👉 Another way is to copy the formula in Excel to the entire column,
Home → Editing → Fill → Down
👉 we can also use Ctrl+D to copy the formula to the entire column.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Today, we learned how to copy formula in Excel to entire column. I hope all of the suitable methods mentioned above to copy formulas to the entire Column will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
