The basic way: copy the cell, paste it where you need it
To copy a formula in Excel, click the cell that holds the formula you want to repeat. Then press Ctrl+C on Windows or Command+C on Mac. Move to the cell or cells where you want the formula to go, and press Ctrl+V (or Command+V). Excel automatically adjusts the cell references in the formula for each new location.
For example, if you have a formula in cell B2 that says =A2*10, and you copy it down to B3, Excel changes it to =A3*10. This automatic adjustment is usually what you want — it saves you from typing the same formula over and over. But it can also break your work if you are not paying attention to which references should stay fixed and which should move.
Key Takeaways
- Copy a formula with Ctrl+C (Windows) or Command+C (Mac), then paste it with Ctrl+V or Command+V, and Excel shifts the cell references automatically.
- Use dollar signs ($) in front of a column letter or row number to lock that part of the reference so it does not change when you copy the formula.
- Paste Special (Ctrl+Shift+V on Windows, Command+Shift+V on Mac) lets you paste only the formula, only the values, or only the formatting — useful when you want to break the link between cells.
- Dragging the fill handle (the small square at the bottom right of a selected cell) copies the formula down or across without opening the clipboard.
- If a formula stops working after you paste it, check whether you accidentally locked a reference that needed to move, or unlocked one that needed to stay put.
When Excel changes references and when it does not
Excel has two types of cell references: relative and absolute. A relative reference like A1 changes when you copy the formula. An absolute reference like $A$1 stays the same no matter where you paste. A mixed reference like $A1 locks the column but lets the row change, or A$1 locks the row but lets the column change.
Most of the time you want relative references — that is why copying and pasting works smoothly for straightforward tasks like multiplying a column of numbers by the same amount. But if you are building a formula that refers to a single cell that should never move (like a tax rate in cell D1 that applies to all rows), you need to lock it with dollar signs. Type =$D$1 instead of =D1, and that reference will not shift when you copy the formula down.
A common mistake is forgetting to lock a reference that should be locked. If you copy a formula that says =A2/$D1 down to row 3, it becomes =A3/$D2 — the tax rate reference moved when it should not have. The fix is to go back to the original formula and change it to =A2/$D$1 before copying.
Using Paste Special to paste only what you need
Sometimes you want to copy a formula but paste only the result (the number it calculates), not the formula itself. Or you want to paste the formula but keep the original formatting. That is where Paste Special comes in. Press Ctrl+Shift+V on Windows or Command+Shift+V on Mac after you copy a cell.
A dialog box opens with options. Click Values to paste only the numbers the formula produces, breaking the link to the original cell. Click Formulas to paste only the formula without any formatting. Click Formats to copy the colors, fonts, and borders but not the data. You can also choose Formulas & Number Formats to get the formula and the way it displays (like currency or percentage) without the cell colors.
This is useful when you want to send a spreadsheet to someone else and do not want them to see or accidentally change your formulas — paste as values first. It is also useful when you have built a complex formula and want to use its result in a different calculation without creating a chain of dependencies that breaks if you edit the original.
Copying a formula across rows and down columns
You can copy a formula in three ways: using the keyboard, using the fill handle, or selecting a range and pasting. The keyboard method (Ctrl+C, then select cells, then Ctrl+V) works everywhere and is fastest if you are already typing. The fill handle is the small square at the bottom right corner of a selected cell — click and drag it down to copy the formula to multiple rows below, or drag it right to copy across columns. This method is quick for small ranges but gets awkward if you need to copy down 500 rows.
For large ranges, select the cell with the formula, then select from that cell down to where you want the formula to end. Press Ctrl+D on Windows or Command+D on Mac to fill down. This copies the formula from the top cell to all the cells below it in one step. The same shortcut works left to right if you select horizontally instead.
Fixing formulas that break after copying
If you copy a formula and the results look wrong, the most common cause is a reference that moved when it should not have, or stayed put when it should have moved. Open the cell with the wrong result and look at the formula bar at the top of the screen. Check whether the cell references point to the rows and columns you expected.
Another common problem is copying a formula to a cell that is not the right type of data. If you copy a formula that adds numbers down to a cell that contains text, you will get an error. Make sure the cells you are pasting into have the same kind of data as the cells the formula was designed for.
If you copy a formula that refers to another sheet (like =Sheet2!A1) and the reference breaks, it usually means you deleted or renamed the other sheet. Check the sheet tabs at the bottom of the screen to see what sheets exist, and edit the formula to point to the correct sheet name.
Copying formulas between different spreadsheets
You can copy a formula from one Excel file to another by opening both files, copying the cell in the first file, and pasting it in the second. The formula will work as long as the cells it refers to exist in the second file. If the formula refers to cells in a different sheet, make sure that sheet exists in the new file too, or the formula will show an error.
If you are copying a formula that refers to a named range (a cell or group of cells you have given a custom name), the named range has to exist in the new file for the formula to work. You can copy named ranges between files by going to the Formulas tab, clicking Define Name, and creating the same named range in the new file before you paste the formula.
Frequently Asked Questions
Why did my formula change when I copied it?
Excel automatically adjusts cell references when you copy a formula — this is called a relative reference. If a reference should not have changed, add dollar signs to lock it: change A1 to $A$1. If you want all references to stay the same, use Paste Special and choose Values instead of Formulas.
Can I copy a formula without changing the cell references?
Yes. Use dollar signs to lock the references you want to keep: $A$1 locks both the column and row, $A1 locks only the column, and A$1 locks only the row. Alternatively, copy the cell, use Paste Special, and choose Values to paste only the result without any formula.
What does the fill handle do?
The fill handle is the small square at the bottom right of a selected cell. Click and drag it down to copy the formula to rows below, or drag it right to copy across columns. This is faster than copy-paste for small ranges, but for large ranges, select the range and press Ctrl+D (Windows) or Command+D (Mac) to fill down in one step.
How do I paste only the numbers, not the formula?
Copy the cell with the formula, then press Ctrl+Shift+V (Windows) or Command+Shift+V (Mac) to open Paste Special. Click Values and then OK. This pastes only the number the formula calculates, breaking the link to the original cell.
What if I copy a formula to the wrong cells by mistake?
Press Ctrl+Z (Windows) or Command+Z (Mac) when ready to undo. If you have already done other work, you can select the cells with the wrong formula and press Delete to clear them, then copy and paste to the correct location.