How to Copy Formula in Excel (6 Quick Methods)

While dealing with MS Excel, we often have to deal with an Excel formula, copy it to different cells, columns or rows. There are several methods to copy a formula in MS Excel. This article will show the six most instrumental methods that you can use to copy a formula in Excel.

Download Practice Workbook

You can download the practice book from the link below.

6 Quick Methods to Copy Formula in Excel

1. Copy a Formula Down a Column in Excel

Let’s assume that we have a list of some people and their weights in the Kilogram unit. We have to convert the weights into pounds (lbs.). We know that 1 kg equals 2.205 lbs. Our target is to input a formula in the first output cell (D5) of the Weight (lbs.) column of the data table and copy the formula down over the cells below Cell D5 till the end of the data set.

Steps:

• Type the formula =C5*2.205 in Cell D5 >> Press Enter.

We’ve converted the weight in kg to lbs. unit. We can see the formula in Cell D5 from the formula bar in the following screenshot.

• Now, drift the mouse cursor on the lower right corner of Cell D5 on the little square-like shape which is called Fill Handle in MS Excel.
• Hold and drag the Fill Handle down the column over the cells till Cell D10 (the last cell of our data set).

So, we have copied the formula in Cell D5 correctly down the column. To check it, we can double-click on any random cell within the cell range D5:D10 and see if the copying is done properly.

We can also copy a formula this way dragging the fill handle to the right, left, or upward direction by maintaining the formats for proper cell references.

Read More: How to Copy a Formula in Excel with Changing Cell References

2. Fill in a Formula for the Entire Column in Excel

When we have a large number of cells in the column of our data set, it won’t be wise to try copying the formula by dragging the Fill Handle over, say, 10000 cells!
In such a case, we can copy the formula in the following ways.

2.1 Convert the Data Set into a Table 

It’s a great idea to convert the entire data set into a table if you have a large data set and want to copy a formula to an entire column.
Turning a data set into an Excel Table is easy. Just follow the steps below.

Steps:

• Select any of the cells in your data set >> press Ctrl+T. The following Create table option will appear.
• Check if the data set is selected properly >> press OK.

• Now, type the formula in the first cell of the table, Cell D5, and press Enter.

You will see the formula being copied in all the cells in that entire column automatically.

You may not like to keep your data set in table format. In that case, all you have to do is:

• Select any cell of the table >> Go to the Context menu.
• Then choose Table from the drop-down >> select Convert to Range.

You will get your data table in normal form in this way.

2.2 Double Clicking on the Fill Handle Icon

You can also copy the formula to the entire column by double-clicking the Fill Handle icon. But if you have any discontinuity down the column, you may have to repeat the double-clicking each time.

Steps:

• Drift the cursor on the Fill Handle >> Double-click the right button of the mouse.

As we see in the screenshot below, the copying is done until we reach the blank Row 9 which has created a discontinuity in the data set.

• You have to copy the formula again from any of the cell range D5:D8 and paste it to Cell D10.
• Then repeat double-clicking as described above.

We have successfully copied the formula now. But we’ll recommend the first method which uses the idea of converting the data set into a table.

Similar Readings

• VBA to Copy Formula from Cell Above in Excel (10 Methods)
• How to Copy Formula to Another Sheet in Excel (4 Ways)
• Excel VBA to Copy Formula with Relative Reference (A Detailed Analysis)
• Creating and Copying Formula Using Relative References in Excel
• How to Copy VLOOKUP Formula in Excel (7 Easy Methods)

3. Copy a Formula into Multiple Cells

Assuming, we have a range of multiple cells C5:D11. We want to enter and copy a formula in the cells in this range of cells except for D10 and D11.

Steps:

• Press Ctrl and hold the key >> Select the cells where you want to copy the formula.

• Press F2 and switch on editing mode >> Type the formula.

• Then instead of pressing Enter, press Ctrl+Enter.

The formula is copied in the selected cells as we can see in the following screenshot.

Read More: How to Copy a Formula across Multiple Rows in Excel (5 Ways)

4. Copy a Formula without Formatting

Assuming that we have a data set and there is already a formula in Cell D5. For some reason, we have formatted the cell. We want to copy the formula down the column, but not the formatting.

Steps:

• Copy the formula by Selecting Cell D5 and pressing Ctrl+C.
• Select Cell D6 >> Go to Context menu >> Select Formulas (F).

• Then using the first method in this article, you can copy the formula in the rest of the cells, at the same time not copying the formatting.

Read More: 3 Quick Ways to Copy Down Excel Formula without Incrementing

5. Copy Formula (Not Changing Cell References)

Let’s consider the following data set.

• First of all, type the formula =C6*D4 in Cell D6:

If we copy the formula down the column, we will not get a proper result because we have used Relative Cell Reference here. So,

• We have to lock Cell D4 by selecting the D4 in the formula and pressing F4.

It’s called Absolute Cell Reference which means wherever you copy the formula, the cell reference D4 will not change.

• Now, copy the formula down the column.

The formula will be copied accordingly without changing the cell reference D4.

Read More: Copy Formula in Excel by Changing Only One Cell Reference

6. Shortcuts to Copy a Formula in Excel

In this section, we’ll show two shortcut methods that can save us time.
We have the following data set and we have to copy the formula in some of the cells.

Copy a Formula to Rightward Cell:

• Select Cell C5 which is on the right side of Cell B5 that contains a formula.
• Then, press Ctrl+R.

As a result, the formula in Cell B5 will be copied to Cell C5.

Copy a Formula to Downward Cells:

• Select the cells C5:C9.
• Now, press Ctrl+D.

Thus we have copied the formula downward accordingly.

Read More: Shortcut to Copy Formula Down in Excel

Copy Formula in Excel: Knowledge Hub

• Copy Exact Formula in Excel
• Copy a Formula in Excel with Changing Cell References
• Copy a Formula Without Changing Cell References
• Copy Formula to Change One Cell Reference Only
• Copy Formula without Dragging
• Copy Formula and Paste as Text
• VBA to Copy Formula from Cell Above
• Copy Formula to Entire Column
• Copy Formula Down Entire Column
• Copy Formula in Excel Down the Column
• Copy Formula Down with Shortcut
• Copy Formula Down Without Incrementing
• Copy a Formula across Multiple Rows
• Copy Formula to Another Sheet
• Copy Sheet to Another Workbook with Excel Formulas
• Copy and Paste Formulas from One Workbook to Another
• Copy SUM Formula in Excel
• Copy VLOOKUP Formula
• Copy Array Formula
• Excel VBA to Copy Formula with Relative Reference
• Fix Excel Not Copying Formulas, Only Values

Conclusion

Hope you will find all these methods instrumental. Moreover, the workbook is there for you to download and practice yourself.  If you have any questions, comments, or any kind of feedback, please let me know in the comment box.

Related Articles

• How to Copy Formula to Entire Column in Excel (7 Ways)
• Copy Formula and Paste as Text in Excel (2 Ways)
• How to Copy Formula in Excel without Dragging (10 Ways)
• Copy and Paste Formulas from One Workbook to Another in Excel
• How to Copy Excel Sheet with Formulas to Another Workbook (5 Ways)
• How to Copy Exact Formula in Excel (13 Methods)
• Making Same Change to Multiple Worksheets (9 Examples)