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.

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:

- Select cell D2 and type in the following formula:

=B2*C2

- Click the Enter button on the formula bar.

This enters the formula without moving the cell selector.

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

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

- Hover the mouse pointer over the fill handle. It changes to a plus sign:

- Double-click the left mouse button.

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.

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

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.

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:

- Select cell D2 and type in the following formula:

=B2*C2

- Press the Enter button on the formula bar to enter the formula.

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

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.

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:

- Select cell D2 and type in the following formula:

=B2*C2

- Click the Enter button on the formula bar to enter the formula.

- Press
**Ctrl + C**to copy the formula in cell D2. On a Mac use**Cmd + C**.

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.

- Select range D3:D6.

- Press
**Ctrl + V**to paste the formula in the selected range.

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

## Method #4: Use a Dynamic Array Formula

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

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:

- Select cell D2 and type in the following formula:

=B2:B6*C2:C6

- Press Enter or click the Enter button on the formula bar.

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.

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.

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

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

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

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:

- Select cell D2 and type in the following formula:

=B2*C2

- Click the Enter button on the formula bar and select range D2:D6.

- Select the
**Down**option on the**Fill**drop-down list in the**Editing**group on the**Home**tab.

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

## Method #6: Use a Keyboard Shortcut

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

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:

- Select cell D2 and type in the following formula

=B2*C2

- Click the Enter button on the formula bar and select range D2:D6.

- Press
**Ctrl + D**.

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.

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:

- Press
**Alt + F11**to open the**Visual Basic Editor**. - Select
**Module**on the**Insert**menu.

- Type the following procedure in the module.

```
Sub EnterFormulaEntireColumn()
Range("D2").Formula = "=B2*C2"
Range("D2").AutoFill Range("D2:D6")
End Sub
```

- Save the procedure and save the workbook as a
**Macro-Enabled Workbook**. - Place the anywhere in the procedure and press
**F5**to run the code. - Press
**Alt + F11**to switch back to the active worksheet.

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