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.

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.

---