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

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from the link below.

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

**Read More: ****How to Apply Same Formula to Multiple Cells in Excel (7 Ways)**

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

**Read More: ****How to Use Point and Click Method in Excel (3 Examples)**

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

**Read More: ****How to Apply Formula in Excel for Alternate Rows (5 Easy ways)**

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

**Read More: ****Excel VBA: Insert Formula with Relative Reference (All Possible Ways)**

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

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

You are welcome, Chandrasekhar! Glad to hear that our tips help you.

Best regards

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

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.

Hi Jon,

I am going to add it.

Thanks for your tips.

Best regards

Glad to know that it was helpful.

You’re welcome 🙂

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

That was great man! Keep coming.

Thanks for your feedback, Mdu!

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 !

Thanks for your input 🙂