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.
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.
- In the first place, go to the E5 cell and type in the expression given below to calculate the Difference in Sales.
- After that, press Enter and utilize the Fill Handle tool to apply the formula to the Entire column.
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.
- First and foremost, select the E5:E9 cells and insert the following formula into the Formula Bar.
- 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.
- Firstly, select the E5:E9 range of cells, and in the Formula Bar, enter the following expression.
- Lastly, hit the Enter key to get the output.
- How to Create a Formula in Excel to Change Date by 1 Year (3 Methods)
- Create a Formula in Excel for Multiple Sheets (4 Methods)
- How to Create a Formula to Calculate Percentage in Excel
- Create a Formula in Excel That Will Place the Word Yes (7 Ways)
- How to Create a Custom Formula in Excel (A Step-by-Step Guideline)
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.
- Initially, insert the following formula into the E5 cell.
- 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.
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.
- Initially, move to the E5 cell and enter the following expression, and press Enter.
- 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.
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.
- 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
- After that, press the F5 key to run the VBA code.
- Finally, you will get the preferred result.
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.
- Initially, write the formula below in cell E5 and hit Enter key.
- 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.
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.
- How to Create a Division Formula in Excel (3 Useful Methods)
- Create a Multiplication Formula in Excel (5 Easy Ways)
- How to Create a Conditional Formula in Excel (5 Easy Ways)
- Create a Formula Using Defined Names in Excel (7 Ways)
- How to Create a Complex Formula in Excel (with Easy Steps)
- Create a Nested Formula in Excel (3 Easy Examples)
- How to Create a Formula in Excel without Using a Function (6 Approaches)