# How to Insert Formula for Entire Column in Excel (6 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

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. Fortunately, Excel offers numerous ways to do this and in this article, we’ll explore all the nitty gritty on how to insert formula for entire column in Excel. In addition, we’ll also learn how to apply formulas to an entire row and with VBA Code.

## 6 Ways to Insert Formula for Entire Column in Excel

To begin with, let us consider the Performance of Sales Reps dataset shown in the B4:D14 cells. Here, the dataset shows the Names, the 2022 Sales, and the 20221 Sales made by each employee respectively. Therefore, let us see each method individually and in detail. Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.

### Method-1: Using Fill Handle Tool

Let’s start with the simplest and most obvious way to insert a formula into an entire column, that is to say, we’ll 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 >> type in the expression given below to calculate the difference in Sales.

`=C5-D5`

Here, the C5 and D5 cells point to the 2022 and 2021 Sales corresponding to Sam. • Next, hover the cursor at the bottom right corner of the E5 cell >> you’ll see a Cross-Hair cursor (Plus Sign) appear >> now, drag the cursor to copy the formula to the cells below. Finally, the results should look like the image shown below. ### Method-2: Double-Clicking Fill Handle Tool

Now, there is an even easier way requiring 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:

• First of all, move to the E5 cell and enter the following expression.

`=C5-D5`

In this formula, the C5 and D5 cells refer to the 2022 Sales and 2021 Sales made by Sam. • Then, hover the cursor at the bottom right corner until the Cross-Hair appears >> Double-Click the Left mouse button. After completing this step, the formula should be inserted into the entire column. ### Method-3: Applying Keyboard Shortcut

Now, I know what you’re thinking. Are there any shortcut keys? Lucky you! There are shortcut keys for inserting formulas for the entire column and our next method describes just that.

📌 Steps:

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

`=C5-D5`

In the above expression, the C5 and D5 cells indicate the 2022 Sales and 2021 Sales values for Sam. • Now, press the CTRL + ENTER keys on your keyboard. Thus, Excel copies the formula to the entire column and populates all the cells. ### Method-4: Utilizing Fill Option

For whatever reasons, 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, let us see the process in detail.

📌 Steps:

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

`=C5-D5` • In turn, go to the Editing section >> click the Fill drop-down >> select the Down option from the list. Subsequently, this should return the results shown in the screenshot below. ### Method-5: Copying and Pasting Formula

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

📌 Steps:

• First, calculate the difference between 2022 and 2021 Sales by inserting the formula into the E5 cell.

`=C5-D5` • Second, press CTRL + C to copy the value of the E5 cell.
• Following this, select the E5:E14 range of cells >> click the Paste drop-down >> choose the Paste Special option. Now, this opens the Paste Special dialog box.

• In turn, select the Formulas option >> hit the OK button.

📃 Note: You can also open the Paste Special wizard by pressing CTRL + ALT + V keys on your keyboard. Consequently, this applies the formula to all the cells in the column. ### Method-6: Employing Array Formula

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, allow me to demonstrate the process in the steps below.

📌 Steps:

• To begin with, select the E5:E14 range of cells >> next, in the Formula Bar, enter the following expression >> finally, hit the ENTER key.

`=C5:C14-D5:D14`

Here, the C5:C14 and D5:D14 range of cells refers to all the 2022 Sales and 2021 Sales values.

📃 Note: The Array formulas are available on Microsoft Excel 365, if you’re using an older version of Excel, please make sure your version of Excel is compatible. Lastly, your output should look like the image given below. ## How to Insert Formula for Entire Row in Excel

What if you want to insert the formulas across the rows? Don’t worry just yet! The steps below will lead you to the answer to this question, so follow along.

📌 Steps:

• At the very beginning, compute the difference between the Sales values by subtracting C6 from C5.

`=C5-C6` • Afterward, select the C7:L7 range and press the CTRL + R keys on your keyboard. Voila! Just like that Excel inserts the formula across the entire row. ## Applying Formula to Entire Column with Excel VBA

You can insert formulas for entire columns in Excel with the click of a button using VBA code. It’s simple and easy, so just these steps.

📌 Steps:

• First, navigate to the Developer tab >> click the Visual Basic button. Now, this opens the Visual Basic Editor in a new window.

• Second, go to the Insert tab >> select Module. For your ease of reference, you can copy the code from here and paste it into the window as shown below.

``````Sub Apply_Formula_To_Entire_Column()

Range("E5").formula = "=C5-D5"
Range("E5").AutoFill Range("E5:E14")

End Sub`````` Code Breakdown:

Here, I will explain the VBA code used for placing formulas into a column.

• In the first portion, give a name to the sub-routine, here it is Apply_Formula_To_Entire_Column().
• Next, use the Range.formula property to enter a formula into the chosen range. In this case, we’ve chosen the E5 cell and used the expression “=C5-D5”.
• Lastly, use the Range.AutoFill method to insert the formula into the E5:E14 range. • Third, close the VBA window >> click the Macros button.

This opens the Macros dialog box.

• Following this, select the copy_and_paste_data macro >> hit the Run button. Eventually, the results should look like the screenshot given below. ## Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself. ## Conclusion

Henceforth, I hope this tutorial provides you with useful knowledge on how to insert formula for entire column in Excel. Now, I’d recommend you apply all these instructions to your dataset by downloading the practice workbook and trying it yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

## Related Articles #### Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

1. Reply Chandrasekhar N May 30, 2018 at 8:14 PM

I thank you for the various tips and short cuts on various topics.

• Reply Best regards

2. Reply Very useful! I knew there was a way to do this, but hadn’t taken the time to learn. Thank you!

• Reply You left out the most useful way. Convert the range to a Table first. Then add the column with the formula. Automatically the formula becomes a column formula and is applied to the entire column.

• Reply Hi Jon,
I am going to add it.
Best regards

• Reply You’re welcome 🙂

3. Reply Sohail Rizki Nov 8, 2018 at 8:54 PM

Instead of Ctrl+D, just enter Ctrl+enter.

4. Reply That was great man! Keep coming.

• Reply 5. Reply A nice option is also to put your datas in a table format and then when you input the first formula, by clicking Enter, it instantly auto fill to the bottom of the table !

• Reply    