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.

Copy a Formula Down a Column in Excel

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.

Final Result: Copy a Formula Down a Column in Excel

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 a Formula to 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.

Fill a Formula to the Entire Column in Excel

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

Final Result: Fill a Formula to the Entire Column in Excel

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.

Copy Formula by Double Clicking on the Fill-handle

Steps:

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

Copy Formula by Double Clicking on the Fill-handle

As we see in the screenshot below, the copying is done until we reach the blank Row 9 that 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.

Fill a Formula to the Entire Column in Excel


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.

How to Copy a Formula into Multiple Cells

Steps:

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

How to Copy a Formula into Multiple Cells

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

How to Copy a Formula into Multiple Cells

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

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

How to Copy a Formula into Multiple Cells


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.

Copy a Formula without Formatting

Steps:

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

Copy a Formula without Formatting

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

Copy a Formula without Formatting


5. Copy Formula (Not Changing Cell References)

Let’s consider the following data set.

How to Copy Formula Not Changing Cell References

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

How to Copy Formula Not Changing Cell References

  • Now, copy the formula down the column.

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

How to Copy Formula Not Changing Cell References

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 our time.
We have the following data set and we have to copy the formula in some of the cells.

Shortcuts to Copy a Formula in Excel

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.

Shortcuts to Copy a Formula in Excel

Copy a Formula to Downward Cells:

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

Thus we have copied the formula downward accordingly.

Shortcuts to Copy a Formula in Excel

Read more: Shortcut to Copy Formula Down in Excel


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 Readings

 

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo