How to use a Formula for an Entire Column in Excel – 6 Methods

Consider the Performance of Sales Reps dataset. It showcases Names, the 2022 Sales, and the 2021 Sales of each employee.

how to insert formula in excel for entire column

 


Method 1 – Using the Fill Handle Tool

Steps:

  • Go to E5 >> Enter the formula below to calculate the difference in Sales.

=C5-D5

C5 and D5 are Sam’s Sales in 2022 and 2021.

Using Fill Handle Tool

  • Hover the cursor at the bottom right corner of E5 >> A Plus Sign is displayed >> Drag down the cursor to copy the formula to the cells below.

Dragging fill handle tool

This is the output.

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 the Fill Handle Tool

Steps:

  • In E5, enter the following formula.

=C5-D5

C5 and D5 are Sam’s Sales in 2022 and 2021.

Double-Clicking Fill Handle Tool

  • Hover the cursor at the bottom right corner of E5 >> A Plus Sign is displayed >> Left-Click.

Double clicking fill handle tool

This is the output.

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


Method 3 – Applying a Keyboard Shortcut

Steps:

  • Select E5:E14>> Enter the following formula in the Formula Bar.

=C5-D5

C5 and D5 are Sam’s Sales in 2022 and 2021.

Applying Keyboard Shortcut

  • Press CTRL + ENTER.

Press CTRL + ENTER

This is the output.

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


Method 4 – Utilizing the Fill Option

Steps:

  • In E5, enter the following formula.

=C5-D5

Utilizing Fill Option

  • Go to Editing >> click Fill >> select Down.

Select down option

This is the output.

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


Method 5 – Copying and Pasting a Formula

Steps:

Calculate the difference between the 2022 and the 2021 Sales.

  • In E5, enter the following formula.

=C5-D5

Copying and Pasting Formula

  • Press CTRL + C to copy the value in E5.
  • Select E5:E14 >> click Paste >> choose Paste Special.

Paste Special option

In the Paste Special dialog box:

  • Select Formulas >> Click OK.

Note: You can also open the Paste Special wizard by pressing CTRL + ALT + V .

how to insert formula in excel for entire column

This is the output.

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


Method 6 – Using an Array Formula

Steps:

  • Select E5:E14 >> Enter the formula in the Formula Bar >>Press ENTER.

=C5:C14-D5:D14

C5:C14 and D5:D14  are Sales values.

 

Employing Array Formula

This is the output.

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


How to use a Formula for an Entire Row in Excel

Steps:

  • Calculate the difference between the Sales values by subtracting C6 from C5.

=C5-C6

How to Insert Formula in Excel for Entire Row

  • Select C7:L7 and press CTRL + R.

applying formula across rows

This is the output.

how to insert formula in excel for entire row

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


Applying a Formula to an Entire Column with Excel VBA

Steps:

  • Go to the Developer tab >> click Visual Basic.

Applying Formula to Entire Column with Excel VBA

In the Visual Basic Editor window:

  • Go to Insert >> select Module.

Inserting Module

Enter the code in the window.

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

End Sub

VBA Code window

Code Breakdown:

  • The sub-routine is named, here Apply_Formula_To_Entire_Column().
  • The Range.formula property is used to enter a formula in the chosen range. Here, E5 using “=C5-D5”.
  • The Range.AutoFill method is used to enter the formula in E5:E14.

VBA Code Explanation

  • Close the VBA window >> click Macros.

In the Macros dialog box:

  • Select the copy_and_paste_data macro >> Click Run.

Running macro

This is the output.

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


Practice Section

Practice here.

Practice Section


Download Practice Workbook

Download the practice workbook.



<< Go Back to How to Create Excel Formulas | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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