How to copy formula in Excel - 5 simple ways

You can trust PC Guide: Our team of experts use a combination of independent consumer research, in-depth testing where appropriate - which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

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:

Copying the formula down a column
Copying a formula without changing cell references
Copying the formula to a non-adjacent cell
Copying a formula to multiple cells
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:

Screenshot of a Microsoft Excel spreadsheet showing a list of items with corresponding prices and quantities, with a copied cell formula result highlighted in red.

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.

A screenshot of a microsoft Excel worksheet with a title that reads "dataset to explore how to copy formula in Excel," featuring a table with columns for products, price per unit, quantities, and total cost

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

A screenshot of a Microsoft Excel worksheet with a data set titled "dataset to explore how to copy formula in Excel," featuring columns for products, prices, quantity, and totals, with some cells displaying calculations

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:

A screenshot of a Microsoft Excel spreadsheet with a dataset that includes items such as oranges, apple juice, and snacks, along with price per unit and quantity columns. It utilizes copy formula functionality in Excel to

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:

An Excel spreadsheet is shown with a table containing a dataset which includes columns for products, price per unit, quantity, total without tax, and total with tax. A cell is highlighted in yellow indicating '

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:

A screenshot of a Microsoft Excel spreadsheet with a dataset listing products, prices, quantities, total without taxes, and tax amount. The cell for the total with taxes for orange juice is selected and highlighted in

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:

A screenshot of a microsoft excel spreadsheet with a dataset including products and prices, showing an example of how to copy formulas across cells. The formula for calculating tax is visible, demonstrating how to copy formula excel

Here are what the formulas look like:

A screenshot of a Microsoft Excel spreadsheet with a dataset and formula calculations for quantity, total without taxes, and tax values, including how to copy formulas in Excel.

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:

A screenshot of a Microsoft Excel spreadsheet containing a simple dataset and formulas, with annotations highlighting the copy formula in the formula bar and a cell containing a formula for a tax calculation.

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:

A screenshot of a Microsoft Excel spreadsheet displaying a dataset titled "Dataset to Explore How to Copy Formulas in Excel" with columns for product, price per unit, quantity, total without taxes, and tax

Here are the expanded formulas:

A screenshot of a Microsoft Excel spreadsheet showcasing a list of quantities and prices with formulas to copy and calculate the total cost, including a tax calculation using absolute cell referencing.

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:

Cell reference	Changes to:
Absolute column and row: $B$3	Remains the same: $B$3
Relative column and absolute row: B$3	Column changes: C$3
Absolute column and relative row: $B3	$B4

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.

A screenshot of a Microsoft Excel spreadsheet showing a dataset of products with their prices and quantities, along with formulas and copy formula functionality calculating totals and taxes.

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:

A screenshot of a Microsoft Excel spreadsheet showing a dataset with the copy formula being entered to calculate the total price with tax for a list of products. A cell is highlighted in red, and an arrow points

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.

A screenshot of a Microsoft Excel spreadsheet with a dataset intended to demonstrate how to copy formulas in Excel, including columns for products, price per quantity, quantity without taxes, tax, and total price with taxes

Step 2: Type the formula

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

A screenshot of a Microsoft Excel spreadsheet with a table containing a list of products, prices per unit, quantity, total without tax, and tax calculations. The cell with a formula to calculate the total with

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.

Spreadsheet in Microsoft Excel with a dataset illustrating how to copy formula in Excel, calculating unit prices, tax, and total with tax for various grocery items.

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:

An excel spreadsheet is open with a dataset titled "dataset to explore how to copy formulas in Excel". The data includes items, prices, and quantities, with calculated tax and total columns.

Step 2: Enter the details

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

A screenshot of Microsoft Excel with an open 'find and replace' dialog box, a worksheet displaying a list of grocery items with their prices and taxes, including copy formula examples in Excel highlighted cells indicating a

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:

A screenshot of a Microsoft Excel spreadsheet with a sample dataset to demonstrate how to copy formula excel, featuring columns for product, unit price, quantity, total without taxes, tax, and total price.

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 copy formula in Excel – 5 simple ways

Table of Contents