Download the practice workbook 👉 HERE and follow along.
Copying formulas in Excel lets you repeat calculations without typing them out again. This makes your work faster and more reliable, especially when dealing with large amounts of data.
How to Copy a Formula Down a Column or Across a Row
If you need to copy a formula in Excel into several cells, here’s how:
Drag the Fill Handle
Here’s how to use the fill handle to copy a formula:
- Click on the cell with the formula.
- Move your cursor to the small square at the bottom-right corner of the cell.
- Drag it down the column or across the row to copy the formula.
Double-Click the Fill Handle
- If you have many rows to fill, just double-click the fill handle.
- Excel will copy the formula down to the last row with data.
Use a Shortcut
- Select the cell with the formula and the cells where you want it copied.
- Press Ctrl + D (Windows) or ⌘ + D (Mac) to fill the formula down.
Featured Course
Excel Essentials for the Real World
Copy a Formula Without Changing Cell References
When you use formulas in Excel, you often want to apply the same calculation to different rows or columns. Excel is designed to help with this by automatically adjusting the cell references when you copy a formula.
For example, let’s say you have a formula in cell C2 that multiplies A2 by B2, like =A2*B2.
When you copy it down to C3, Excel will change the formula to =A3*B3 to match the new row.
This is great when you want to apply the same calculation to a different row or column. But what if you need the formula to stay the same, even when you copy it?
Here’s how to copy paste exact formula in Excel without changing cell reference:
Use Find and Replace
1. Highlight the Cells with Your Formula
- First, select the cells that contain the formula you want to copy.
- Make sure to highlight the entire range if you’re copying more than one cell (in our example F4 to F12)
2. Open the Find and Replace Tool
- Go to the Home tab on the Excel ribbon, then click on Find & Select, and choose Replace.
- You can also press Ctrl + H as a shortcut to open the Find and Replace dialog box.
3. Replace the Equals Sign
- In the Find and Replace box, type the equal sign (=) in the “Find what” field.
- In the “Replace with” field, type a unique text string that doesn’t appear anywhere else in your file. For example, you could use “notinfile”.
- Click Replace All.
This step temporarily turns your formula into text so that Excel won’t adjust the cell references when you copy it.
4. Copy Your Formula to the New Location
- Now, you can safely copy (Ctrl + C) the cells (in our example, the cells F4 to F12).
- Paste (Ctrl + V) them to a new location (in our example, to cells F14 to F22).
- Since the formula has been converted to text, the cell references will not change.
5. Convert the Text Back to a Formula
- Once you’ve pasted the formula in its new location, open the Find and Replace tool again (Ctrl + H).
- This time, replace your unique text string (e.g., “notinfile”) with an equal sign (=).
- Click Replace All.
This converts the text back into a working formula, with the original cell references intact.
Featured Course
Master Excel’s Essential Modern Functions
How to Lock Cell References in Excel
When you copy and paste formulas in Excel, it usually changes the cell references based on where you paste it. This works well most of the time, but sometimes, you need the cell reference to stay the same.
For example, if you’re multiplying numbers by a fixed tax rate, you’ll want to keep that reference unchanged.
Here’s how to lock cell references so they don’t change when you copy your formulas.
Understanding Cell References in Excel
Before we get started, let’s quickly cover the basics of cell references in Excel:
- Relative References: These change when you copy the formula to another cell. For example, B1 becomes B2 when copied down a row.
- Absolute References: These stay the same, no matter where you copy the formula. You create an absolute reference by adding a $ sign before both the column letter and the row number, like $B$1.
- Mixed References: These lock either the row or the column, but not both. For example, $B1 locks the column, while B$1 locks the row.
Check out our detailed guide about cell references in Excel.
Use the $ Sign to Lock Cell References
To keep a cell reference from changing when you copy a formula, you can use the $ sign.
How It Works
- Simply add a $ sign before the column letter and the row number in your formula. For example, if your formula is
=B1*2, change it to=$B$1*2. - Now, when you copy the formula, it will always point to cell B1.
Example
Let’s say you have actual sales in column B, last year’s sales in column C, and budget figures in column D. You want to calculate how this year’s sales compare to last year and to the budget.
Step 1:
- In cell E4, enter the formula to compare actual sales to last year:
=B4/C4-1 - For a detailed guide about how to calculate percentage change in Excel check out this article.
Step 2:
- You want to copy this formula down (for the other apps) and across columns (to compare to the budget).
- Therefore, you need to lock the reference to column B (Actual).
- Change the formula to
=$B4/C4-1
Step 3:
- Now, copy the formula down the column to apply it to other rows.
- The reference to column B will stay the same, while the row number adjusts automatically.
Step 4:
- To compare actual sales to the budget in column F, copy the formulas from column E to column F.
- The reference to column C will stay relative, adjusting as needed, while the reference to column B remains locked.
Quick Tip: Use the F4 Key to Lock References Fast
Instead of typing the $ signs manually, you can use a quick shortcut.
- After clicking on the cell reference in your formula, press F4.
- This will automatically add the $ signs to lock both the column and the row.
- Press F4 again to toggle between locking just the row or just the column.
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master how to copy a formula in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
