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
in`=C5*2.205`

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

**Read More:** **How to Copy a Formula Down the Column in Excel(7 Methods)**

**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)**

**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 formulain`=C6*D4`

**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**

**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)**