How to Copy Formula Data in Excel Without Breaking References

To copy formula data in Excel, select the cells, press Ctrl+C, choose the destination, and use Home > Paste to select Formulas, Values, or a regular paste. Use Formulas to copy calculations, Values to copy only their displayed results, and the fill handle to extend a formula into nearby cells. Before pasting, check whether the formula’s cell references should change in the new location.

What You’ll Need

A workbook open in Microsoft Excel for Microsoft 365 on Windows.
The source cells containing the formulas or results you want to copy, plus a destination range that is large enough for the copied cells.
A clear idea of whether the destination should contain working formulas, fixed values, or formulas with their formatting.

Save the workbook first if the formulas are important or you are making a large change. Copying and pasting can be undone with Ctrl+Z, but saving a copy gives you a safer fallback if you later make more edits.

Step-by-Step

Decide what “formula data” means for your task. If the destination should recalculate when its input cells change, copy the formulas. If it should preserve the current numbers or text without recalculating, copy the results as values. A normal paste copies formulas, results, and most cell formatting together. For example, you may want working formulas in a new monthly column, but values only when sending a finished report that should not change with the original data.
Check the source formula and the references it uses. Select one formula cell and look in the formula bar above the worksheet. For example, with quantities in A2 and prices in B2, where A2 is 4 and B2 is 6, the formula in C2 could be =A2*B2. The result is 24. If you copy this formula one row down, Excel normally adjusts its relative references to A3 and B3. That is usually right when each row has its own inputs, but not if the formula must keep pointing to a particular cell.
Make any references that must stay fixed absolute. A dollar sign locks a row, a column, or both when a formula is copied. Suppose B2 contains 10, F1 contains 3, and you want each value in column B multiplied by the constant in F1. In C2, enter =B2*$F$1; the result is 30. When copied down to C3, the formula changes to =B3*$F$1, keeping F1 fixed. To lock just the column or just the row, use a mixed reference such as $F1 or F$1. You can place the cursor on a reference in the formula bar and press F4 to cycle through reference styles on Windows; on some laptops, use Fn+F4.
Select and copy the source cells. Click a cell, or drag across the range you need. To select a nonadjacent group, hold Ctrl while selecting additional ranges, though a single rectangular range is simpler to paste reliably. Press Ctrl+C. A moving border appears around the copied selection. If you only need one formula, copy that cell rather than an entire column; this avoids overwriting unrelated data or carrying extra formatting into the destination.
Choose the destination carefully. Click the upper-left cell where the copied range should begin. If you intend to fill an existing block, select a destination range with the same number of rows and columns as the source. Check that the destination does not contain information you need to keep. For a small range, select just the starting cell and let Excel fill the matching-sized area. For a one-cell formula copied to one cell, this is straightforward; for larger blocks, matching the dimensions helps prevent shifted or overwritten data.
Paste formulas when you want calculations to remain active. On the Home tab, open the Paste dropdown and choose Formulas. This copies the formula expressions without bringing across the source formatting. Relative references adjust to the new location, while absolute references remain locked. If you want the formulas and their original formatting, use a regular paste instead: press Ctrl+V or choose Paste from the Home tab. Inspect the results, particularly if formulas refer to nearby cells or use mixed references.
Use the fill handle to extend a formula through adjacent rows or columns. Select the cell with the formula, point to the small square at its lower-right corner, and drag across the cells to fill. Excel adjusts relative references as it fills. You can also select the formula cell and the blank cells below it, then press Ctrl+D to fill down; Ctrl+R fills right. These shortcuts fill from the top or left edge of the selected range, so confirm that the source formula is in the correct starting cell first. Double-clicking the fill handle may fill down alongside a neighboring data column, but the extent depends on the adjacent data, so review the filled range.
Paste values when you need fixed results instead of formulas. Copy the source range, select the destination, open Home > Paste, and choose Values (often shown with a 123 icon). The cells receive the current calculated results, not the formulas that produced them. For instance, copying a cell that displays 24 as values leaves 24 in the destination even if the source inputs later change. If you need the numbers to retain the source number format, look for Values & Number Formatting in the Paste dropdown; the exact set of icons can vary by Excel build.
Verify the copied cells before moving on. Click a pasted cell and inspect the formula bar. If it begins with an equals sign, the cell contains a formula; if it shows a fixed result, you likely pasted values. Check a few cells at the start, middle, and end of a large range. Confirm totals, references, number formats, and blanks, and make sure the paste did not replace headings or other content. If something is wrong, press Ctrl+Z immediately and repeat the paste using the correct option.

On a Mac, use Command+C and Command+V in place of Ctrl+C and Ctrl+V; Command+D fills down in Excel for Mac. The ribbon’s Home > Paste controls offer formula and value options there too, although their layout may look different. Excel for the web has copy, paste, and fill options, but some menus and keyboard behavior vary from the Windows desktop app. If a paste option is not visible, use the Home tab’s Paste dropdown or open the workbook in the desktop app.

Common Mistakes

Using a regular paste when you only wanted results. A normal paste carries formulas, so the destination may change when its references recalculate. Choose Values for a fixed snapshot.
Copying a relative reference that should be fixed. A reference such as A1 moves when copied. If every copied formula must use the same lookup or constant cell, lock the needed row and/or column with dollar signs before copying.
Choosing Values when you need future recalculation. Values look correct at first, but they are no longer linked to the source inputs. Use Formulas or a regular paste if the destination must update.
Pasting into a range of the wrong size. A destination that is too small or starts in the wrong cell can produce an incomplete or misaligned result. Match the dimensions of the copied block and check the upper-left destination before pasting.
Overwriting existing cells without noticing. Pasting replaces the contents of destination cells. Undo immediately if needed, then choose an empty range or insert space before pasting.
Assuming a displayed number is the stored value. A cell may show a rounded number because of its number format while holding a more precise result. Pasting Values preserves the calculated value, not necessarily the exact appearance, unless you also choose an appropriate number-format paste option.

Tips

For a one-off copy, use copy and paste. For a repeated calculation down a table, use the fill handle or select the range and press Ctrl+D.
If you want formulas but not formatting, choose Formulas from the Paste dropdown. If you want the appearance and the calculations, use a regular paste and review the destination formats.
Press Esc to cancel a copy selection before starting another task. If the moving border remains, Excel is still in copy mode; it does not mean the formula has been pasted already.
When copying a formula between worksheets, check whether its references point to the intended sheet. A copied formula may continue to refer to the original sheet, depending on how the formula was written.
Use a small test paste when a formula contains several references, named ranges, or links to other sheets. A single example can reveal whether references shift as expected before you fill hundreds of rows.
Keep the source and destination ranges easy to identify. Clear headings and separate working areas make it less likely that a paste will replace inputs, labels, or formulas that support another calculation.

Troubleshooting

The copied formula shows an unexpected result. Select the destination cell and inspect the formula bar. Compare each reference with the source formula. If a reference moved when it should not have, undo the paste, lock the relevant row or column with dollar signs, and copy again. If it points to the right cells, check the inputs and the number format; a rounded display can make a correct result look different.

The destination contains numbers but no formulas. You probably chose Values. If you need a live calculation, undo and paste with Formulas or use a regular paste. If undo is no longer available, copy the original formula again from the source.

Formulas appear as text, or Excel shows the formula instead of its result. Check whether the destination cell is formatted as Text, or whether the formula begins with an apostrophe. Change the cell format to General, then re-enter the formula by selecting the cell, pressing F2, and pressing Enter. Also check whether Show Formulas is enabled; press Ctrl+` (the grave accent key, often near the number 1) to toggle it on Windows. On a Mac, the shortcut can differ by keyboard layout, so use the Formulas tab’s Show Formulas control if necessary.

The paste is blocked or incomplete. Confirm that the workbook or worksheet is not protected against editing and that the destination is not part of a merged-cell layout that conflicts with the source range. Unmerge or choose a compatible destination if appropriate. For a filtered list, be cautious: copying and pasting into filtered ranges can affect hidden rows in ways that are not obvious. Test on a small range or temporarily remove the filter if it is safe to do so.

The fill handle is missing. Excel may have the fill-handle setting turned off. In Windows, open File > Options > Advanced and check Enable fill handle and cell drag-and-drop. If you cannot or do not want to change that setting, select the formula cell and the cells to fill, then use Ctrl+D or Ctrl+R.

Frequently Asked Questions

How do I copy a formula without changing its references?
Make the references absolute before copying by adding dollar signs to the row and column that must stay fixed, such as $F$1. If you want every reference to remain exactly as written, you can also copy the formula from the formula bar and paste it into the destination, then check the result.

How do I copy only the result of a formula?
Copy the formula cell, select the destination, and choose Home > Paste > Values. The destination receives the current result as a fixed value and will not recalculate from the original inputs.

Will copying a formula also copy its formatting?
A regular paste generally copies both the formula and formatting. Choose Formulas to bring over the calculation without source formatting, or Values to paste only the result. Paste options may differ slightly between desktop Excel and Excel for the web.

Why did my formula change when I pasted it?
Excel normally adjusts relative references according to the formula’s new position. That behavior is useful when filling row-by-row calculations, but can change results if a reference was meant to stay fixed. Use absolute or mixed references where needed, then check the pasted formula.

Can I copy formulas to another worksheet?
Yes. Copy the cells, open the destination worksheet, select its upper-left destination cell, and paste. Review formulas that refer to other sheets, because a formula may keep its original sheet reference. Use Values instead if you want to transfer results without their links to source cells.

Can I copy a formula to many rows at once?
Yes. Copy the formula cell and select the destination range, then use Ctrl+D when the formula is at the top of the selection. Alternatively, drag the fill handle down. Check the first and last filled formulas to make sure relative and absolute references behaved as intended.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.