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.

Method 1 – Using the Fill Handle Tool

Use the Fill Handle tool to copy the formulas into the cells below.

Steps:

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

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

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

This is the output.

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 and D5 are Sam’s Sales in 2022 and 2021.

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

This is the output.

Method 3 – Applying a Keyboard Shortcut

Create a Formula in Excel without using a function.

Steps:

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

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

• Press CTRL + ENTER.

This is the output.

Method 4 – Utilizing the Fill Option

Use the Fill option on the Ribbon.

Steps:

• In E5, enter the following formula.

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

This is the output.

Method 5 – Copying and Pasting a Formula

Apply formulas to the entire column.

Steps:

Calculate the difference between the 2022 and the 2021 Sales.

• In E5, enter the following formula.

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

In the Paste Special dialog box:

• Select Formulas >> Click OK.

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

This is the output.

Method 6 – Using an Array Formula

Use Array Formulas.

Steps:

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

C5:C14 and D5:D14  are Sales values.

This is the output.

How to use a Formula for an Entire Row in Excel

Steps:

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

• Select C7:L7 and press CTRL + R.

This is the output.

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

Applying a Formula to an Entire Column with Excel VBA

Apply the Formula to the entire column using Excel VBA.

Steps:

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

In the Visual Basic Editor window:

• Go to Insert >> select 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

• Close the VBA window >> click Macros.

In the Macros dialog box:

• Select the copy_and_paste_data macro >> Click Run.

This is the output.

Practice Section

Practice here.

Download Practice Workbook

Download the practice workbook.

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