# 11. Excel How to copy a formula to a column ?

There are 2 ways to copy a formula to a column:

- Copying the formula using the mouse
- Using the
**Series**dialog box

## 1^{st} method: Copy the formula using the mouse

Consider the following worksheet extract:

In cell D2, we have written the simple formula :

=B2*C2

To copy this formula to column D :

- Position the mouse pointer at the bottom right corner of cell D2, the pointer appears as a +.
- Click without releasing the mouse button and move the pointer to the last cell to be filled and then release.

The cells in column D are filled with the correct formulas.

This method is effective but is less practical if the sheet contains a considerable number of rows as you have to click and scroll for a long time to get to the last cell.

## Method 2: Using the Series dialog box

Let's take the same example and assume that the sheet contains thousands of rows. Proceed as follows to copy the formula to these thousands of rows:

- Click on cell D2
- Click and hold the
**Shift**button on the keyboard - Click on the last cell in column D and release the
**Shift**button. This selects the range containing all the cells where we want to copy the formula - On the
**Home**tab, in the**Editing**group, click on the**Fill**button, then on**Series...**The**Series**dialog box appears. - In this dialog box
- Select
**Columns**under**Series in**. - Select
**Autofill**under**Type**. - Validate with the
**OK**button.

- Select

The selected range is filled with the correct formulas.

NB. This method also works for copying several formulas.

*Copying several formulas*

Consider the following worksheet extract:

In cell D2, the formula is :

=B2*C2

In cell E2, the formula is to calculate the 5% discount when the amount is greater than 1000 :

=SI(D2>1000;D2*0,05;0)

In cell F2, the formula is :

=D2-E2

To copy these 3 formulas each to the corresponding column:

- Click on cell D2
- Click and hold the
**Shift**button on the keyboard - Click on the last cell in column F and release the
**Shift**button. This selects the range containing all the cells where we want to copy the formulas - On the
**Home**tab, in the**Editing**group, click on the**Fill**button, then on**Series...**The**Series**dialog box appears. - In this dialog box
- Select
**Columns**under**Series in**. - Select
**Autofill**under**Type**. - Validate with the
**OK**button.

- Select