How to Apply Formula to Entire Column in Excel?

Excel is essentially a calculation engine. Just like a calculator, it takes questions in the form of formulas, for example, =8+4, and returns answers.

Formulas are essential to manipulating data and extracting useful information from your Excel workbooks. Formulas make Excel roar to life as it were.

Although formulas are powerful tools they need to be used in an efficient manner for maximum benefit.

Suppose you have a dataset that requires the same formula in an entire column. Laboriously entering the formula in one cell at a time wastes time and effort.

This tutorial shows you 7 time-saving techniques for applying a formula at once to an entire column in Excel.

Method #1: Double-click the Fill Handle

The following is a dataset showing the quantities of various items bought and their prices. 

Dataset where the formula needs to be used

We want to apply a formula in column D to calculate the total cost of each line of items bought.

We will enter a formula in cell D2 and double-click the fill handle to copy the formula down the column.

Here are the steps to do this:

  1. Select cell D2 and type in the following formula:
=B2*C2
Formula entered in cell D2
  1. Click the Enter button on the formula bar. 
Enter the formula

This enters the formula without moving the cell selector.

formula entered in cell D2

Notice the little green square in the bottom right corner of cell D2:

green square in cell selection

That little green square is called the Fill Handle

Note: If you do not see the fill handle enable it by doing the following then continue with step 3:

  • Click the File tab to open the Backstage window.
  • Select Options in the left sidebar.
  • Select Advanced in the left sidebar of the Excel Options dialog box that appears. Then select Enable Fill handle and cell drag-and-drop and click OK.
Enable fill handle
  1. Hover the mouse pointer over the fill handle. It changes to a plus sign:
Place the cursor on fill handle
  1. Double-click the left mouse button.
Formula applied to the entire column

The formula is copied down the column and total prices for the various items are returned. 

The cell references in the formula are relative and are therefore adjusted accordingly as the formula is copied down the column. You can ascertain this by clicking Show Formulas in the Formula Auditing group on the Formulas tab.

Click on Show formula
Cells show formula instead of the result

Note: If the cell references in the formula were absolute, meaning they had two dollar signs, for example, =$B$2*$C$2, the formula would be copied down the column as is.

One problem with the technique of double-clicking the fill handle is that if there are blank rows in the dataset, the formula is not copied all the way down the column but only up to the first blank cell in the column as shown below:

Fill handle stops before blank cell

If your dataset has blank rows you can first delete them before double-clicking the fill handle or use the following method of dragging down the fill handle.

Method #2: Drag Down the Fill Handle

The following is a dataset showing the quantities of various items bought and their prices. Row 4 of the dataset is blank.

Dataset where the formula needs to be used

We want to apply a formula in column D to calculate the total cost of each line of items bought.

We will enter a formula in cell D2 and drag down the fill handle to copy the formula down the column.

Below are the steps to do this:

  1. Select cell D2 and type in the following formula:
=B2*C2
Formula entered in cell D2
  1. Press the Enter button on the formula bar to enter the formula.
Enter the formula
  1. Hover the mouse pointer over the fill handle. It changes to a plus sign. Press and hold down the left mouse button and drag the fill handle down to cell D6, then release the button. 
Formula applied to the entire column

Notice the zero (0) in cell D4. You can delete it. 

One drawback of this technique is that it can become cumbersome if you are working with a large dataset.

Method #3: Use Copy and Paste

The following is a dataset showing the quantities of various items bought and their prices. 

Dataset where the formula needs to be used

We want to apply a formula in column D to calculate the total cost of each line of items bought.

We will enter a formula in cell D2, copy it, and paste it into the rest of the cells in the column.

We use the following steps:

  1. Select cell D2 and type in the following formula:
=B2*C2
Enter the formula
  1. Click the Enter button on the formula bar to enter the formula.
Formula entered in cell D2
  1. Press Ctrl + C to copy the formula in cell D2. On a Mac use Cmd + C.
Copy the cell with the formula

Notice the marching ants border around cell D2. This indicates that the formula in the cell is copied to the clipboard and is available for pasting. 

  1. Select range D3:D6.
Select the remaining cells
  1. Press Ctrl + V to paste the formula in the selected range.
Paste the copied cells

The formula is copied into the range and the total price of each line of the items bought is returned. 

Notice that cell D2 still has the marching ants border. Press the Escape key to remove it and clear the clipboard. 

Note: If the cell containing the formula has formatting that you do not want to be copied down the column, you can use the Paste Special feature as follows:

  • Select and copy the cell containing the formula.
  • Select the range into which you want the formula to be copied.
  • Press Ctrl + Alt + V to open the Paste Special dialog box. Select the Formulas option and click OK.
Select formula in Paste Special

Method #4: Use a Dynamic Array Formula

The following is a dataset showing the quantities of various items bought and their prices. 

Dataset where the formula needs to be used

We want to apply a formula in column D to calculate the total cost of each line of items bought. We will enter a dynamic array formula in cell D2. We will press Enter to automatically propagate the formula in the column.

Note: Dynamic arrays are only available in Office 365 and Office 2022 or later.

We use the following steps:

  1. Select cell D2 and type in the following formula:
=B2:B6*C2:C6
Enter the array formula
  1. Press Enter or click the Enter button on the formula bar.
Enter the formula

The formula is propagated to the rest of the cells and the total price of each line of the items bought is returned. 

Important information about dynamic array formulas

Notice the blue border around range D2:D6 in our example dataset. 

blue border around range

When we entered the dynamic array formula, the results spilled into the range. Excel visually defines a spill range with a solid blue border. 

All cells in the spill range are disabled except the cell containing the original formula. This means that none of the values in the spill range except cell D2 can be edited, deleted, or moved in any way. 

The spill range must be blank for dynamic arrays to work. Any non-empty cell in the path of the spill range will cause a #SPILL! error.

For example, the following dataset has a value in cell D4.

result cells already has a cell filled

If we attempt to enter a dynamic array formula in cell D2 we get a #SPILL! error:

SPILL error by formula

Delete the value in cell D4 and the formula propagates without an error:

Formula applied to the entire column

Method #5: Use the Fill Down Command on the Home Tab

The following is a dataset showing the quantities of various items bought and their prices. 

Dataset where the formula needs to be used

We want to apply a formula in column D to calculate the total cost of each line of items bought. We will enter a formula in cell D2 and use the Fill Down command on the Home tab to copy the formula down the column.

We use the following steps:

  1. Select cell D2 and type in the following formula:
=B2*C2
Enter the formula
  1. Click the Enter button on the formula bar and select range D2:D6.
Select the entire range where you want the formula
  1. Select the Down option on the Fill drop-down list in the Editing group on the Home tab.
Select the Fill Down option

The formula is copied down the column and the total price of each line of items bought is returned.

Formula applied to the entire column

Method #6: Use a Keyboard Shortcut

The following is a dataset showing the quantities of various items bought and their prices. 

Dataset where the formula needs to be used

We want to apply a formula in column D to calculate the total cost of each line of items bought. We will enter a formula in cell D2 and use the keyboard shortcut Ctrl + D to copy the formula down the column.

We proceed as follows:

  1. Select cell D2 and type in the following formula

=B2*C2

Formula entered in cell D2
  1. Click the Enter button on the formula bar and select range D2:D6.
Select the entire range
  1. Press Ctrl + D.
Keyboard shortcut to enter formula in all selected cells

The formula is copied down the column and the total price of each line of items bought is returned.

Method #7: Use Excel VBA

The following is a dataset showing the quantities of various items bought and their prices. 

Dataset where the formula needs to be used

We want to apply a formula in column D to calculate the total cost of each line of items bought. We will create and use a VBA procedure to copy the formula down the column.

We use the following steps:

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. Select Module on the Insert menu.
Insert a module
  1. Type the following procedure in the module.
Sub EnterFormulaEntireColumn()
Range("D2").Formula = "=B2*C2"
Range("D2").AutoFill Range("D2:D6")
End Sub
  1. Save the procedure and save the workbook as a Macro-Enabled Workbook.
  2. Place the anywhere in the procedure and press F5 to run the code.
  3. Press Alt + F11 to switch back to the active worksheet.
Run the macro

The procedure has worked and copied the formula down the column.

Note that cell references are hard-coded in the procedure and we will need to change the cell references if we want to apply the procedure to another dataset.  

In this tutorial, we have looked at 7 different techniques for applying a formula to an entire column in Excel. You can use the techniques that work best for you. 

Other Excel articles you may also like: