How to Repeat Formula in Excel for Whole Column (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, we apply a formula in a cell and then repeat the same formula to the entire column in Microsoft Excel. However repeating the same formula again and again for every cell can be a hectic and time-consuming task. When you have a large dataset, sometimes it becomes quite impossible to repeat the formula manually. Are you having trouble repeating the formula for the whole column in Excel? This tutorial will help you to learn how to repeat formula in Excel for whole column 5 easy ways. Let’s get started!


How to Repeat Formula in Excel for Whole Column: 5 Easy Ways

In order to repeat the formula for the whole column in Excel, we have taken a dataset like the following figure where we have the names of 6 students and their marks in Math and English. We will add one student’s marks using the Excel formula and then repeat the formula for the whole column using the Fill Handle, Fill Down option, Keyboard Shortcut, Copy Pasting method, and Entering formula to multiple cells method.

Suitable Ways to Repeat Formula in Excel for Whole Column


1. Use of Fill Handle

Now we will repeat the formula for the whole column in Excel using the Fill Handle. This is the most common and easiest method in this respect. In order to use this method, follow the steps below.

Steps:

  • First, select cell E5 and type the following formula:
=C5+D5
  • Now, press Enter.
  • As a result, you will see an output like the image below which it will display the total marks of John.

Suitable Ways to Repeat Formula in Excel for Whole Column

  • Second, select cell E5 and drag the Fill Handle to the entire column Total Marks.
  • Finally, you will see an output like the below one where it will display all students’ total marks.

Note: We can also do this operation by double-clicking the Fill Handle instead of dragging it to the entire column.

Read More: How to Repeat Formula for Each Row in Excel


2. Using Fill Down Option

We can also use the Fill Down option to repeat formula for the whole column in Excel. This is also an easy and efficient way to do the job. In order to use this method, follow the steps below.

Steps:

  • First, select cell E5 and type the following formula:
=C5+D5
  • Second, press Enter.
  • As a result, you will see an output like the image below which will display the total marks of John.

Suitable Ways to Repeat Formula in Excel for Whole Column

  • After that, select all the cells of column Total Marks including cell E5.
  • Furthermore, go to the Home tab.

Suitable Ways to Repeat Formula in Excel for Whole Column

  • Next, click on the Fill drop-down and select the Down option.

Suitable Ways to Repeat Formula in Excel for Whole Column

  • As a result, you will see an output like the below one where it will display all students’ total marks.

Read More: How to Repeat Multiple Rows in Excel


3. Use of Keyboard Shortcut

We can use the Keyboard Shortcut method to repeat the formula for the whole column in Excel. If you are someone who likes to use keyboard shortcuts instead of a mouse, this method can be helpful for you. In order to apply this method, follow the steps below.

Steps:

  • First, select cell E5 and type the following formula:
=C5+D5
  • Second, press Enter.
  • As a result, you will see an output like the image below which will display the total marks of John.

Suitable Ways to Repeat Formula in Excel for Whole Column

  • After that, select all the cells of column Total Marks including cell E5.

Suitable Ways to Repeat Formula in Excel for Whole Column

  • Subsequently, press Ctrl+D.
  • Hence, you will see an output like the below one which will display all students’ total marks.

Read More: How to Repeat Rows in Excel Based on Cell Value


4. Applying Copy Pasting Method

The copy-pasting method is another way to repeat the formula for the whole column in Excel. This is also a user-friendly way to perform the task. In order to apply this method, follow the steps below.

Steps:

  • First, select cell E5 and type the following formula:
=C5+D5
  • Second, press Enter.
  • As a result, you will see an output like the image below which will display the total marks of John.

Suitable Ways to Repeat Formula in Excel for Whole Column

  • After that, copy the cell E5 by pressing Ctrl+C as shown below.

Suitable Ways to Repeat Formula in Excel for Whole Column

  • Furthermore, select the cells of the column except for cell E5 where you want to apply this formula like the below one.

Suitable Ways to Repeat Formula in Excel for Whole Column

  • Hence, paste the formula by pressing Ctrl + V and you will see an output like the one below which will display all students’ total marks.

Read More: How to Repeat Cell Values in Excel


5. Entering Formula to Multiple Cells

Entering formulas into multiple cells is another option to repeat formulas for the whole column in Excel. This is also an easy way to perform this task. In order to apply this method, follow the steps below.

Steps:

  • To begin with, select all the cells of the column Total Marks and type the following formula in one of the cells as shown below:
=C5+D5

Suitable Ways to Repeat Formula in Excel for Whole Column

  • Secondly, press Ctrl + Enter.
  • Hence, you will see an output like the below one which will display all students’ total marks.


Things to Remember

  • Using Fill Handle is the most common and easiest way to repeat formulas for the whole column in Excel.
  • If you have a very large dataset, then the keyboard shortcut method will be the most convenient method.
  • You can also apply the Fill Down option, Copy Pasting method, or Entering formula method for small datasets.

Download Practice Workbook

You can download the Excel workbook from here.


Conclusion

Hence, follow the above-described methods. Thus, you can easily learn how to repeat formula in Excel for whole column. Hope this will be helpful.

Don’t forget to drop your comments, suggestions, or queries in the comment section below.


Related Articles


<< Go Back to Repeat in Excel | Excel Cell Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Asaduzzaman
Md. Asaduzzaman

Hello! I am Md. Asaduzzaman. Currently, I am working as an Excel and VBA Content Developer and I will be posting my articles related to this here. I graduated from Bangladesh University of Science and Technology(BUET) in 2022. I completed my BSc in Naval Architecture and Marine Engineering. I like to solve real-life problems in Microsoft Excel and share the solutions through articles. I post here regularly. Hope you find the articles helpful.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo