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

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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


7 Suitable Ways to Copy Formula To Entire Column in Excel

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.

Overview


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.

Use of the Fill Command to Copy Formula to Entire Column

  • Now, type the formula in Formula Bar. The formula is,
=C5*10%

Use of the Fill Command to Copy Formula to Entire Column

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

Use of the Fill Command to Copy Formula to Entire Column

  • From your Home Tab, go to,

Home → Editing → Fill → Down

Use of the Fill Command to Copy Formula to Entire Column

  • After following the above instruction, you will get the increasing price of the other fruits.

Use of the Fill Command to Copy Formula to Entire Column


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%

Apply the Autofill Handle to Copy Formula in Excel

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

Apply the Autofill Handle to Copy Formula in Excel

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

Apply the Autofill Handle to Copy Formula in Excel


3. Using the Keyboard Shortcuts to Copy Formula To Entire Column

Here, we’ll learn how to copy formulas in Excel to the entire column by 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%

Using the Keyboard Shortcuts to Copy Formula To Entire Column

  • Now, press Enter on your keyboard and you will get the increasing price of Apple. The increasing price of Apple is $139.10.

Using the Keyboard Shortcuts to Copy Formula To Entire Column

Step 2:

  • After that, select cell D5 to cell D14 and press Ctrl+D on your keyboard.

Using the Keyboard Shortcuts to Copy Formula To Entire Column

  • Then, press Enter on your keyboard and you’ll get your desired output in Column D.

Using the Keyboard Shortcuts to Copy Formula To Entire Column


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%

Insert the Array formula to Copy Formula To Entire Column

  • Now, press Enter on your keyboard and you’ll get your desired output in Column D which is given in the screenshot.

4. Insert the Array formula to Copy Formula To Entire Column


Similar Readings: 


5. Use the Copy-Paste Method to Copy Formula To Entire Column

In this method, 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%

Use the Copy-Paste Method to Copy Formula To Entire Column

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

Use the Copy-Paste Method to Copy Formula To Entire Column

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.

Use the Copy-Paste Method to Copy Formula To Entire Column

  • Finally, press Ctrl+V on your keyboard and you get your desired output that has been given in the screenshot.

Use the Copy-Paste Method to Copy Formula To Entire Column


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.

Apply the Ctrl+Enter Method

  • Now, type a formula in the Formula Bar. The formula in the Formula Bar is,
=C5*30%

Apply the Ctrl+Enter Method

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

Apply the Ctrl+Enter Method to Copy Formula To Entire Column


7. Create A Table to Copy Formula Automatically in Excel

After learning the above Methods, here 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.

Create A Table

  • After selecting the range, press Ctrl+T on your keyboard, and a Create Table box pops up. Press OK to continue.

Create A Table

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

Create A Table

  • After completing the above steps, now press Enter on your keyboard and you’ll get your desired output that has been given in the screenshot.

Create A Table


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


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.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo