To paste a formula in Excel, select the cell that contains it, press Ctrl+C, select the destination cell, and press Ctrl+V. Excel usually adjusts relative cell references to suit the new location. If you want to paste formulas without copying formatting, use Home > Paste > Formulas. The steps below are for Excel for Microsoft 365 on Windows; Mac and web differences are noted where they matter.
What You’ll Need
- An Excel workbook with a formula already entered, or a clear idea of the formula you want to enter.
- A source cell or range to copy, plus the destination cell or range. If the formula refers to other cells, check that those cells contain the intended values.
- Permission to edit the worksheet. If the sheet is protected, you may need the password or permission from the workbook owner before you can paste.
Save the workbook if you have made important changes. Copying and pasting a formula does not normally change the source cell, but saving gives you a recovery point if a later edit affects more cells than intended.
Step-by-Step
-
Check the formula in the source cell. Click the cell containing the formula and look in the formula bar above the worksheet. Confirm that the displayed result makes sense and that the formula refers to the intended cells. For example, with quantities in B2:B20 and unit prices in C2:C20, a formula in D2 can multiply the values in the same row. If B2 contains 4 and C2 contains 12, the formula
=B2*C2returns 48. Check that arithmetic before copying so you do not spread an existing mistake. -
Copy the source cell. Select the cell, then press Ctrl+C on Windows. A moving dashed border usually appears around the copied cell. On a Mac, press Command+C. You can also right-click the selected cell and choose Copy, or use Home > Copy. Do not cut the cell: copying leaves the original formula in place, while cutting moves its contents.
-
Select the destination. Click the cell where the formula should go. If the destination is far away, use the Name Box to the left of the formula bar: type a cell address such as D3 and press Enter. Make sure you have selected the top-left cell if you are pasting into a larger area. Pasting into a cell that already contains data replaces that cell’s contents, so check the destination before continuing.
-
Paste the formula. Press Ctrl+V on Windows or Command+V on a Mac. Alternatively, use Home > Paste. The pasted formula should calculate and show a result in the destination cell. If you paste a formula from D2 into D3, the references normally shift by one row. In the example layout where B2:B20 contains quantities and C2:C20 contains prices, if B3 is 3 and C3 is 12, the adjusted formula in D3 calculates 36. That is expected: Excel adapts relative references to the new position.
-
Paste formulas without the source formatting, if needed. Copy the source cell, select the destination, then choose Home > Paste > Formulas from the Paste menu. This transfers the formula but leaves the destination’s existing number format, fill color, borders, and other formatting in place. The menu may show a Formulas icon rather than the word, depending on the ribbon layout. You can also right-click the destination and choose the formulas option under Paste Options. On Mac, use the Home tab’s Paste menu and choose Formulas; the exact icon arrangement can vary by Excel release.
-
Fill a formula through a range. To copy a formula down several rows, copy the formula cell, select the destination range, and paste. Excel puts a corresponding formula in each cell and adjusts relative references row by row. For a contiguous column, another option is to select the formula cell and the empty cells beneath it, then choose Home > Fill > Down. The Windows shortcut Ctrl+D fills down. For a row, select the formula cell and cells to its right, then choose Home > Fill > Right; Ctrl+R is the Windows shortcut. On Mac, the ribbon commands are available; keyboard shortcuts can differ by keyboard and Excel version.
-
Check the pasted result and references. Click the destination and inspect the formula bar. Verify that the formula points to the intended row, column, or fixed cell, and compare a few calculated results with a manual check. If the output looks like a formula rather than a calculated value, or if it returns an unexpected number, use the troubleshooting section below before filling more cells.
You can also paste a formula by typing it directly into the destination cell and pressing Enter, but copying is useful when you want to reuse a formula that already works. If you paste a block of cells, Excel copies each cell’s contents and, with ordinary paste, its formatting as well. Use the formulas-only option when you want to preserve the destination’s appearance.
Common Mistakes
-
Copying the wrong cell. A worksheet can contain nearby labels, values, and formulas that look similar. Check the selected cell address in the Name Box and the formula bar before copying.
-
Assuming every reference stays fixed. References such as B2 are relative, so they change when a formula is pasted elsewhere. A reference with dollar signs, such as $B$2, is absolute and stays fixed. Mixed references, such as $B2 or B$2, lock only the column or row. Use the reference type that matches your calculation before copying the formula across a range.
-
Pasting into the wrong-sized selection. If you copy several cells, select a destination area with a compatible shape. A mismatch can produce an error message or place data in an unintended pattern. When copying a single cell, you can usually paste into one cell or use a selected range to repeat it, but review the result rather than assuming Excel filled it as intended.
-
Overwriting existing information. Pasting replaces the contents of the destination cells. If a range contains notes, formulas, or values that must be kept, select an empty destination or make a copy of the worksheet first.
-
Using ordinary Paste when only the formula is wanted. Standard paste can bring the source cell’s number format, fill, borders, and other formatting along with the formula. Choose Formulas from the Paste menu to keep the destination’s formatting.
Tips
-
Use the fill handle for a quick series. Select a cell with a formula and point to the small square at its lower-right corner. Drag it down or across to fill adjacent cells. When the source data is in a neighboring continuous column, double-clicking the fill handle can fill down to the end of that data. Check the final row, because blank cells in the neighboring column can affect where Excel stops.
-
Use absolute references for constants. If a formula should always refer to one tax rate, exchange rate, or other fixed input, place that input in a dedicated cell and lock the appropriate reference with dollar signs before filling. This makes the formula easier to update than typing the same constant into many formulas.
-
Choose Paste Special deliberately. The Paste menu offers other options, including Values, which pastes calculated results rather than formulas. Values is not the same as Formulas. If you need the calculation to update when its inputs change, choose Formulas; if you need a fixed result, Values may be appropriate.
-
Review a small sample before filling hundreds of rows. Paste into the next row first, inspect the adjusted references, and confirm the result. Once the pattern is correct, fill the remaining range. This catches reference problems early and is faster than correcting a whole column afterward.
-
Keep an original copy when a large range is involved. If you are about to replace many cells, duplicate the worksheet or save a separate workbook copy. Excel’s Undo command can reverse many paste actions, but it is safer not to depend on undo after continuing with other edits.
Troubleshooting
-
The cell shows the formula characters instead of a result. The cell may be formatted as Text, or the formula may have been entered with an apostrophe before the equals sign. Change the cell format to General using Home > Number Format > General. Then edit the cell and press Enter so Excel evaluates the formula. If many cells are affected, select them, change the format, and re-enter the formulas.
-
The result changes unexpectedly after pasting. Click the pasted cell and compare its references with the source formula. Relative references move with the formula. If a specific reference must stay on the same cell, edit it to use an absolute reference before copying, or correct the pasted formula and refill the range. Check one row at a time until the pattern is right.
-
The formula returns #REF!. This error means a reference is invalid, often because a referenced row or column was deleted or the formula was copied in a way that shifted a reference outside the intended range. Inspect the formula bar and replace the broken reference with the correct cell or range. Do not fill the formula further until the reference is repaired.
-
The formula returns #VALUE! or another unexpected error. Check whether the referenced cells contain numbers in the expected format rather than text, spaces, or error values. Also confirm that the formula’s operators and references suit the data. If only one pasted row fails, compare it with a working row to find the different input or reference.
-
Nothing pastes or Excel reports that the action cannot be completed. Make sure you copied a cell, not just selected it, and that the destination is editable. A protected sheet, a merged-cell layout, or an incompatible multi-cell selection may prevent the paste. Try one destination cell first, or ask the workbook owner to unlock the relevant cells. In Excel for the web, browser clipboard permissions can also affect pasting; use the Home tab’s Paste command if the keyboard shortcut does not work.
Frequently Asked Questions
How do I paste a formula without changing the formatting?
Copy the formula cell, select the destination, and choose Home > Paste > Formulas. This pastes the formula while retaining the destination’s formatting. Avoid the ordinary Paste command if the source formatting should not come along.
Will Excel change the cell references when I paste a formula?
Usually, yes. Relative references adjust based on the distance between the source and destination. References with dollar signs stay fixed in the locked row, column, or both. Check the formula bar after pasting to confirm the references are correct.
How do I paste the same formula into many cells?
Copy the formula cell, select the destination range, and paste. For a contiguous range below the formula, you can also select the full range and use Home > Fill > Down. Review the formulas at the top and bottom of the range to make sure they adjusted as intended.
Can I paste a formula from one workbook into another?
Yes. Open both workbooks, copy the formula cell in the source workbook, select a destination cell in the other workbook, and paste. If the formula refers to cells in the source workbook, Excel may keep an external workbook reference. Check whether that link is wanted before relying on the result.
Why did pasting a formula copy a color or border too?
That is the normal behavior of ordinary Paste, which can copy both contents and formatting. Use the Formulas option in the Paste menu to transfer the calculation without the source cell’s formatting.
Can I paste a formula into a filtered list?
You can, but be careful: selecting a visible range in a filtered worksheet may not behave like selecting every row in an unfiltered range. Check which cells are selected before pasting, and verify the results in both visible and hidden rows if the formula must apply to all records.
Related
- How to Insert a Line in a Word Header Step by Step
- How to Insert a Line in a Word Table: Step by Step
- How to Insert a Line Between Columns in Microsoft Word
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Concatenate Formula Examples for Text and CellsWith a first name in A2 and a last name in B2, join them with a space using…
- Remove Filter From an Excel Table: Steps and OptionsTo remove the filter arrows from an Excel table in Microsoft 365 for Windows, click a cell in…
- Excel Sum a Range of Cells: Formulas, Tips, and FixesTo add a range of cells in Excel, use the SUM function: if the numbers are in A1…
- Excel Formula: Capitalize Only the First Letter in TextTo capitalize the first character in a cell and make the remaining characters lowercase, use =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))), assuming the…
- Excel SPLIT Function: Use TEXTSPLIT to Separate TextExcel does not have a function named SPLIT. In Microsoft 365 or Excel 2024, use TEXTSPLIT to divide…
- Excel LEN Formula Examples: Count Text in CellsIn Excel for Microsoft 365, use =LEN(A2) to count the characters in cell A2, including spaces and punctuation.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.