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.


How to Insert Formula for Entire Column in Excel: 6 Ways

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.

how to insert formula in excel for entire column

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.

Using Fill Handle Tool

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

Dragging fill handle tool

Finally, the results should look like the image shown below.

how to insert formula in excel for entire column with fill handle tool

Read More: How to Create a Formula in Excel for Multiple Cells


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.

Double-Clicking Fill Handle Tool

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

Double clicking fill handle tool

After completing this step, the formula should be inserted into the entire column.

how to insert formula in excel for entire column by double clicking fill handle


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. Let’s go through the process of creating a Formula in Excel without using a function with a keyboard shortcut.

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

Applying Keyboard Shortcut

  • Now, press the CTRL + ENTER keys on your keyboard.

Press CTRL + ENTER

Thus, Excel copies the formula to the entire column and populates all the cells.

how to insert formula in excel for entire column with keyboard shortcut


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

Utilizing Fill Option

  • In turn, go to the Editing section >> click the Fill drop-down >> select the Down option from the list.

Select down option

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

how to insert formula in excel for entire column utilizing fill option


Method-5: Copying and Pasting Formula

As another alternative, you can also use the copy-paste feature to apply formulas to the entire 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

Copying and Pasting Formula

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

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.

how to insert formula in excel for entire column

Consequently, this applies the formula to all the cells in the column.

how to insert formula in excel for entire column by copying and pasting


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 365, if you’re using an older version of Excel, please make sure your version of Excel is compatible.

Employing Array Formula

Lastly, your output should look like the image given below.

how to insert formula in excel for entire column with Array formula


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

How to Insert Formula in Excel for Entire Row

  • Afterward, select the C7:L7 range and press the CTRL + R keys on your keyboard.

applying formula across rows

Voila! Just like that Excel inserts the formula across the entire row.

how to insert formula in excel for entire row

Read More: How to Apply Formula in Excel for Alternate Rows


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. Let’s follow the instructions below to apply the Formula to the entire column using Excel VBA.

📌 Steps:

  • First, navigate to the Developer tab >> click the Visual Basic button.

Applying Formula to Entire Column with Excel VBA

Now, this opens the Visual Basic Editor in a new window.

  • Second, go to the Insert tab >> select Module.

Inserting 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

VBA Code window

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.

VBA Code Explanation

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

Running macro

Eventually, the results should look like the screenshot given below.

how to insert formula in excel for entire column with VBA code


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.

Practice Section


Download Practice Workbook

You can download the practice workbook from the link below.


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.



<< Go Back to How to Create Excel Formulas | Excel Formulas | 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.
Siam Hasan Khan
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.

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

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

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

  4. That was great man! Keep coming.

  5. 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 !

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo