How to Copy a Formula Down the Column in Excel(7 Methods)

Copying a formula is one of the most frequent tasks that we do in Excel. There are multiple ways to copy a formula in Excel down the column. Today, in this article, you will 7 different methods to copy a formula in Excel down the column with ease.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


7 Methods to Copy a Formula Down the Column in Excel

1. Drag the Fill Handle Icon to Copy a Formula Down the Column in Excel

The easiest way to copy a formula down the column in Excel is to use the Fill Handle icon.

All you need to do is,

❶ First, insert a formula in the first cell of a column.

❷ Hover around the mouse curse on the right-bottom corner of the first cell of the column.

A small plus icon called Fill Handle will appear.

❸ Just drag down the icon as much as you want to copy a formula.

Drag the Fill Handle Icon to Copy a Formula in Excel Down the Column

After that, you will see that the formula has been copied to all the cells down the column.

One thing about using the Fill Handle icon is that it not only copies a formula but also formattings. If you want to avoid that, click on the drop-down icon at the bottom of the column that appears after dragging the Fill Handle icon. Then select Fill Without Formatting to avoid copying the cell formattings.

Drag the Fill Handle Icon to Copy a Formula in Excel Down the Column without copying cell format

Read More: Shortcut to Copy Formula Down in Excel (7 Ways)


2. Double-Click on the Fill Handle Icon to Copy a Formula Down the Entire Column in Excel

This is the fastest way to copy a formula in Excel down the entire column.

All you need to do is,

❶ Insert a formula in the top cell of a column.

❷ Place your mouse cursor to the right-bottom corner of the top cell.

A plus-like icon will appear.

❸ Just double-click on it.

This will copy a formula down the entire column.

Double-Click on the Fill Handle Icon to Copy a Formula Down the Entire Column in Excel

📓 Note: This method copies down a formula in a column until it finds a blank cell.

Related Content: How to Copy Formula to Entire Column in Excel (7 Ways)


3. Use the Fill Down Option to Copy Formula Down the Column in Excel

Using this method, you can select a certain range, and then using the Fill Down option you can copy formula only within the selected range.

Anyways, here’s how:

❶ Insert a formula at the top cell of a column.

❷ Then select a range of cells including the formula on the top cell.

❸ After that go to the Home tab.

❹ From the Editing group, go to Fill > Down.

Use the Fill Down Option to Copy Formula in Excel  Down the Column

This will instantly copy a formula down the entire column of the selection area.

Read More: How to Copy Formula in Excel (6 Quick Methods)


4. Use a Keyboard Shortcut Key to Copy a Formula Down the Column in Excel

This is the fastest way to copy a formula all the way down a selected range.

To use the keyboard shortcut key,

❶ Insert a formula in the top cell of a column.

❷ Select a range of cells including the top cell having the formula.

❸ From the keyboard press and hold the CTRL key and then press the D key.

Use a Keyboard Shortcut Key to Copy a Formula in Excel Down the Column

This will instantly copy the formula all the way down the selected range of the column.

Read More: How to Copy Formula in Excel without Dragging (10 Ways)


Similar Readings:


5. Convert Your Dataset into Excel Table to Copy a Formula Automatically Down the Column

If you convert your dataset into Excel Table and then insert a formula in a column, it will automatically fill up all the cells down the column.

Here’s how:

❶ Select the whole dataset first.

❷ Press CTRL + T to convert it into an Excel Table.

Convert Your Dataset into Excel Table to Copy a Formula Automatically Down the Column

A dialog box called Create Table will appear.

❸ Just hit the OK button.

❹ Now insert a formula at the top cell of a column.

❺ Now hit the ENTER button.

You will notice that Excel automatically copies all the cells down the column instantly.

Related Content: How to Copy Formula to Another Sheet in Excel (4 Ways)


6. Use Copy-Paste Method to Copy a Formula Down the Column in Excel

To use the Copy-Paste method,

❶ Insert a formula at the top of a column first.

❷ Then Copy the formula using CTRL + C.

Use Copy-Paste Method to Copy a Formula in Excel Down the Column

❸ After that, select the rest of the cells of the column.

❹ Now press CTRL + V to paste the formula.

Thus the cell address of the formula will change automatically and apply to all of the cells of the selected area.

Related Content: Copy and Paste Formulas from One Workbook to Another in Excel


7. Use Array Formula to Copy a Formula Down the Column in Excel

If you insert an array formula in the top cell of a column, it will automatically be copied to the other cells.

To use this trick,

❶ First select a range of the cells where you want to apply the formula.

❷ Then insert a formula using the Formula Bar.

❸ After that, press CTRL + SHIFT + ENTER to insert the array formula.

Use Array Formula to Copy a Formula Down the Column in Excel

After that, you will see that Excel automatically copies the array formula in the selected cells down the column.

If you are using Microsoft Office 365, you don’t need to follow the above steps.

Just Insert a formula in the top cell of a column and then hit the ENTER button.

Related Content: How to Copy Exact Formula in Excel (13 Methods)


Things to Remember

  • CTRL + D is the shortcut key to copy formulas in Excel down the column.
  • Press CTRL + T to turn a dataset into an Excel Table.
  • You can press CTRL + SHIFT + ENTER to insert an array formula in Excel 2019 and the previous versions of it.

Conclusion

To sum up, we have discussed 7 methods to copy a formula in Excel down the column. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo