Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Apply a Formula to an Entire Column in Excel

Fortunately, the heart and soul of an Excel spreadsheet are its formulas, and typically, you apply these formulas in a range of cells located in columns or rows. Excel offers numerous ways to do this. In this article, I will explore all the nitty-gritty of how to apply a formula to an entire column with 7 easy ways in Excel. Hence, read through it and save your time.


Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


7 Suitable Methods to Apply a Formula to an Entire Column in Excel

In this article, I will discuss 7 quick and suitable methods to apply formulas to an entire column in Excel. For the purpose of demonstration, I have used the following sample dataset in Excel that contains the Original Prices, Sales Prices, and Discount Rates of some Products. Let’s see the methods below.

Sample Dataset for How to Apply a Formula to an Entire Column in Excel

Here, I have used Microsoft Excel 365 version; you may use any other version according to your convenience.


1. Use Fill Handle Tool to Apply a Formula to an Entire Column in Excel

Let’s start with the simplest and most obvious way to insert a formula into an entire column; that is to say, I will use the Fill Handle tool to copy the formulas into the cells below. So, let’s see it in action.

📌  Steps:

  • In the first place, go to the E5 cell and type in the expression given below to calculate the Difference in Sales.

=C5-D5

How to Apply a Formula to an Entire Column in Excel Using Fill Handle Tool

  • After that, press Enter and utilize the Fill Handle tool to apply the formula to the Entire column.

Read More: How to Create a Subtraction Formula in Excel (7 Quick Ways)


2. Apply Keyboard Shortcut for Applying a Formula to a Column

Now, I know what you’re thinking. Are there any shortcut keys? Lucky you! There are keyboard shortcuts for inserting formulas for the entire column, and our next method describes just that. However, follow the steps below to complete the task.

📌  Steps:

  • First and foremost, select the E5:E9 cells and insert the following formula into the Formula Bar.

=C5-D5

Apply Keyboard Shortcut for Applying a Formula to a Column in Excel

  • Finally, press the CTRL + Enter keys on your keyboard to get the desired output.

Read More: How to Create a Formula in Excel (5 Ways)


3. Utilize Array Formula in Excel Column

Moreover, if you’re using the latest version of Excel, you can take advantage of the Array Formulas to place formulas into an entire column. Now, read the steps below to understand the process properly.

📌  Steps:

  • Firstly, select the E5:E9 range of cells, and in the Formula Bar, enter the following expression.

=C5:C9-D5:D9

Utilize Array Formula in Excel Column

  • Lastly, hit the Enter key to get the output.

Read More: How to Create Array Formula in Excel (10 Suitable Examples)


Similar Readings


4. Insert a Formula to an Entire Column with Fill Option

For whatever reason, if you prefer not to use the Fill Handle tool, then Excel also has the Fill option in the Ribbon tab to place formulas into a column. Therefore, go through the following steps in order to receive your desired output.

📌  Steps:

  • Initially, insert the following formula into the E5 cell.

=C5-D5

Insert a Formula to an Entire Column with Fill Option

  • Now, select the entire column.
  • In turn, go to the Editing section and select the Down option from the Fill drop-down list.

  • Subsequently, this should return the results shown in the screenshot below.

Read More: How to Create an Excel Formula to Subtract (10 Examples)


5. Double-Click Fill Handle Tool

Furthermore, there is an even easier way that requires just two clicks to copy the formula along the column. In Excel terms, it is sometimes referred to as the double-click trick. Hence, just follow along.

📌  Steps:

  • Initially, move to the E5 cell and enter the following expression, and press Enter.

=C5-D5

Double-Click Fill Handle Tool to Apply a Formula to an Entire Column in Excel

  • Then, hover the cursor at the bottom right corner until the Cross-Hair appears, and Double-Click the Left mouse button.

  • At last, the final result will appear in the picture below.

Read More: How to Apply Formula to Entire Column Without Dragging in Excel


6. Employ Formula to a Column Through Excel VBA

Last but not least, you can insert formulas for entire columns in Excel with the click of a button using the VBA code. It’s simple and easy. However, a VBA code is necessary for this method. Hence, you can just copy the code and run it in your Excel worksheet by following the steps mentioned below.

📌  Steps:

  • Firstly, hold the Alt + F11 keys in Excel, which opens the Microsoft Visual Basic Applications window.
  • Secondly, click the Insert button.
  • Thirdly, click on Module from the menu to create a module.

  • Fourthly, a new window will open. Write the following VBA macro in the Module window.
Sub Apply_Formula_To_Entire_Column()
'ExcelDemy Publications
Range("E5").formula = "=C5-D5"
Range("E5").AutoFill Range("E5:E9")
End Sub

Employ Formula to a Column Through Excel VBA

  • After that, press the F5 key to run the VBA code.
  • Finally, you will get the preferred result.

Read More: How to Create a Formula in Excel for Multiple Cells (9 Methods)


7. Use Copy and Paste Option to Input Formula in Excel Column

As another alternative, you can also use the copy-paste feature to put formulas into a column in Excel. So, let’s begin.

📌  Steps:

  • Initially, write the formula below in cell E5 and hit Enter key.

=C5-D5

Use Copy and Paste Option to Input Formula in Excel Column

  • Then, press CTRL + C to copy the value of the E5 cell.
  • After that, select the other cells of the column according to your dataset.

  • Finally, press CTRL + V to get the final output.


Conclusion

These are all the steps you can follow to apply a formula to an entire column in Excel. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit Exceldemy.com.


Related Articles

Mehedi Hasan

Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo