How to copy formula in Excel – 5 simple ways

If you want to learn how to copy formula in Excel, we’ve got your back.

Excel is a spreadsheet application developed by Microsoft. It was originally designed for accounting purposes but has since evolved into a robust data analysis and management tool. Creating, copying, and pasting formulas in Excel allows you to automate what would otherwise be tedious, making your experience using the program more convenient.

Using formulas in Excel also allows the user to add and subtract, find the average and median, and much more. Essentially, it is an absolute staple of the Excel program. The best part? Even if you are a beginner to the program, learning how to copy formulas in Excel is an easy task. And this guide, we’ll show you how to do it.

How to copy a formula in Excel

Scenario on hand: We have a dataset with basket items and quantities of one customer. 

What we want to accomplish: Explore how to copy a formula in Excel in the following ways:

1. Copying the formula down a column
2. Copying a formula without changing cell references
3. Copying the formula to a non-adjacent cell
4. Copying a formula to multiple cells
5. Copying a formula with ‘Find & Replace’

All these methods are great for copy-pasting formulas in an Excel sheet. 

Copying the formula down a column

Copying the formula down a column using the “Fill Handle Tool” is one of the easiest ways to copy a formula. 

Our dataset includes the product name, price per unit, and the quantity of items in the basket. We want to use a formula that multiplies the price with the quantity to calculate the total. 

The formula uses a simple multiplication with a = at the beginning and * in between the cell references. 

Step 1: Writing the formula in the first row

The first step is writing the formula in the first row like this:

Step 2: Drag the formula

Next, move your cursor to select this cell. In the bottom right corner, you will see a small square. When you move your cursor to this square, it will shift from a pointer shape to a + sign. 

Click on the square using this + sign and drag it down to fill the entire table with the same formula. 

Release the cursor, and you will see the blank rows auto-filled with the formula:

Step 3: Check the formula

Check the formula in each cell. Since we have not fixed the cell references, Excel automatically changes the cell references in the formula for the rows below:

While this dynamically changing formula is quite useful, there might be times when you have to keep the cell references intact while dragging the formula across the column.

Copying a formula without changing cell references

In this method, you will learn how to copy an Excel formula while keeping one or more cell references intact. 

Step 1: Add the fixed cell 

Let’s assume that our calculated prices did not include tax for this demonstration. We added the tax percentage to a cell in Excel with the cell address H3:

Step 2: Enter the first formula

In the first cell of the Tax column, we entered the formula =E6*H3. Here, E6 is the cell address of the total of 12 orange juices in the customer’s basket, and H3 is the cell address of the tax percentage. 

This formula calculated 10% of the total:

Step 3: Add absolute cell references

Right now, if you drag the formula downwards, you will see no calculation in the cells as the cell reference with the tax percentage will also dynamically shift downwards:

Here are what the formulas look like:

One trick will lock the H3 cell reference in place. 

We add a $ sign before H and 3. By doing this, the first formula will look like this:

Note that adding the $ sign does not impact the calculation. 

The first dollar sign locks the column H in place, while the second dollar sign locks the row in place. 

We drag the formula down, and it calculates the tax accordingly:

Here are the expanded formulas:

Cell reference explanation

Cell references work by locking a cell address in place. So, while other formula elements may change when you drag the formula downwards or sideways, the locked element would not change. 

However, there might be times when you need to lock just the row or just the column. Here is a table that will make things clearer for you:

Copying the formula to a non-adjacent cell

While the Fill Handle Tool is an easy way to copy the formula across a row or column, there might be times when you need to copy your formula to a cell that isn’t adjacent to it. 

This method is also extremely easy. 

Step 1: Copy the cell

First, select the cell with the formula. To copy, you have three options:

• Double-click the cell to reveal the formula. Then, double-click again to select the entire formula and right-click to open the menu. In the context menu, select Copy in order to duplicate the cell formula.
• Use the Copy option, which can be seen in the Home tab.
• Select the formula from the formula bar and press CTRL+C or copy from the right-click menu.

Copying the formula will create a dashed line around the cell, showing that it has been copied. 

However, at this point, if you select any other cell, it will alter the formula. After copying, make sure to press Enter before selecting the cell where you want to copy the formula. 

Step 2: Pasting the formula

Now click on the cell where you want to paste the formula and paste it by using one of these three methods:

• Right-click and press “Paste” from the menu. 
• Pressing CTRL+V
• Use the Paste option, which can be seen in the Home tab.

We used CTRL+V to paste the formula for calculating the price of a dozen orange juices:

Note: If you directly copy and paste the cell without selecting the formula first, it will change the formula if you haven’t entered an absolute cell reference. 

To enter the formula view mode, you can press Ctrl+`, then simply use CTRL+C and CTRL+V to copy and paste. 

Copying a formula to multiple cells

Another clever way to copy a formula directly to multiple cells without using the Fill Handle Tool is using the F2 function.

Step 1: Select all cells

The first step is selecting all the cells where you want to copy the formula. Then, on your keyboard, press F2. 

Step 2: Type the formula

After selecting the cells and pressing F2, type the formula in the cell. 

Step 3: Press CTRL+Enter

The next step is to press CTRL+Enter together. This will apply the formula to every cell. If you haven’t added an absolute cell reference, it will dynamically change the formula for each cell in the column. 

Copying a formula with ‘Find & Replace’

Another way to copy formulas from one place to another is by using Find and Replace. 

Step 1: Open the Find and Replace window

The first step is opening up the Find and Replace window by pressing CTRL+H or clicking Find and Replace from the Home tab:

Step 2: Enter the details

In the Find and Replace window, enter =, and in the replace window, any character. We replaced = with the character /

This will change all the formulas in the sheet to static text. You can then copy and paste the formulas by pressing CTRL+C to copy and CTRL+V to paste:

Step 3: Replace the symbol

After you’re done copy-pasting, replace the symbol with = using the same Find and Replace window:

Wrapping up

That concludes our list of solutions for how to copy a formula in Excel. It really isn’t that difficult. All you need to do is simply follow the steps in one of the above methods, and you’ll be well on your way to learning the program’s basics in no time.

Learn more about Excel and how it works through these guides:

• How to Enter in Excel
• How to remove gridlines in Excel
• How to edit a drop-down list in Excel