Copy Formulas in Excel Without Creating External Links

To copy a formula without linking it to another workbook, copy the formula text from the formula bar and enter it in the destination workbook, then make sure any workbook name or file path in the formula is removed. If you are copying within the same workbook, use Paste Special > Formulas instead. These methods keep the calculation as a formula; they do not turn it into a fixed value.

Excel uses the word link for several different things. A formula can refer to another cell in the same workbook, refer to a cell in a different workbook, or sit behind a clickable hyperlink. This guide focuses on copying formulas without creating or retaining an unwanted link to a different workbook. It also explains how to keep references pointed at the intended cells.

What You’ll Need

Microsoft Excel for Microsoft 365 on Windows, or access to Excel for Mac or Excel for the web if that is where you work.
The source workbook and the destination workbook. If the formula uses data from another sheet, have the relevant sheet available in the destination too.
Permission to edit the destination workbook. Save a backup first if you will be changing many formulas or removing existing workbook links.

Step-by-Step

Identify what kind of link you want to avoid. Click the source cell and inspect the formula bar. A reference such as A2 is a cell reference; it is not an external workbook link. A formula that includes a workbook name in square brackets, such as [Budget.xlsx], or a file path is an external reference. A clickable web or email address is a hyperlink, which is separate from a formula. Knowing which one you have helps you choose the right method.
Save a copy and check the destination layout. Before making changes, save the destination workbook under a backup name or use File > Save As. Check that it contains the cells and sheets the formula is supposed to use. A formula can be free of external links and still return an error if its expected local data is missing.
For a formula without an external reference, copy the formula text. In the source workbook, select the formula cell. Click in the formula bar, select the complete formula, and press Ctrl+C on Windows. Click the destination cell in the other workbook, click its formula bar, press Ctrl+V, and press Enter. This enters the formula as a formula in the destination workbook rather than carrying over the source cell as an object. On Mac, use Command+C and Command+V. In Excel for the web, the formula bar is available, though copying between browser tabs may depend on browser clipboard permissions.
Check how cell references should behave. Copying formula text preserves the cell addresses written in it; it does not automatically shift them to match the new position as a normal fill or copy operation within a worksheet would. For example, with 4 in A2 and 25 in B2, a formula entered in C2 that multiplies those two cells is =A2*B2, and its result is 100. If you enter that same formula in C3, it still refers to row 2 unless you change the references. Adjust row or column references before pressing Enter when the destination should use different cells.
Remove any external workbook part before entering the formula. If the formula bar shows a workbook name or path, copying that text as-is will preserve the external link. Edit the copied text so it refers to a sheet or cell in the destination workbook instead. For instance, if the source formula points to a sheet named Rates in another workbook and the destination has its own Rates sheet with the intended data, change the reference to the local Rates sheet. Keep the sheet name and cell address, and retain single quotation marks around a sheet name if Excel needs them, such as when the name contains spaces. Confirm the destination sheet and data really correspond before removing the source workbook name.
Use Paste Special for formulas copied within one workbook. Select the source cells, press Ctrl+C, select the destination cells, and choose Home > Paste > Formulas. This pastes formulas without copying formatting. It does not remove an external workbook reference already written inside a formula, so inspect formulas that already contain a workbook name. On Mac, use Command+C and the Home ribbon’s Paste options; ribbon labels can vary by Excel release. Excel for the web also offers formula-only paste options in its paste menu.
Check the result in the destination workbook. Select the pasted cell and inspect the formula bar. Confirm that it contains the expected local references and no unwanted workbook name or path. Check the displayed result and, if practical, change one input cell to make sure the result recalculates. If the formula uses other sheets, verify those sheet references point to the destination workbook’s sheets.

When copying a formula into a different workbook, the formula bar method is useful because it lets you control the formula text. It is not a magic link remover: an explicit external reference remains external until you edit it. Also, ordinary copy and paste between workbooks may keep references to the source workbook, even if the destination has sheets with matching names.

Common Mistakes

Using Paste Special > Values when you still need a working formula. Values keeps the current displayed answer but removes the calculation. Use Formulas if the calculation must remain active.
Assuming formula-only paste removes external links. It controls what is pasted, not what a formula refers to. A formula that already points to another workbook can still do so after being pasted.
Removing the workbook name without checking the sheet. A local sheet with a different name, layout, or data will not be an equivalent replacement. Check the destination data first to avoid incorrect answers.
Forgetting that relative references may need adjusting. A copied formula can calculate correctly but use the wrong row or column. Compare its references with the intended destination cells.
Breaking links to try to preserve formulas. In Excel, breaking a workbook link generally replaces linked formulas with their current values. It is not a safe way to keep those formulas working independently.

Tips

Copy a small sample first and verify it before applying the method to a large range.
If formulas depend on several source sheets, copy the required sheets into the destination workbook and check how their references resolve. Copying all related sheets together can be more reliable than rebuilding each reference by hand.
Use the formula bar to inspect a selected cell, and use Ctrl+` on Windows to toggle formula display on a worksheet when you want to review many formulas. On many Mac keyboards, the equivalent is Control+`; keyboard layouts can differ.
Keep formulas and formatting as separate decisions. Paste Special > Formulas is useful when destination formatting should remain unchanged; use the regular paste options if you also need formats.
In Excel for Microsoft 365 on Windows, check Data > Workbook Links for recognized external workbook links. In some older versions, the command is called Data > Edit Links. The available link-management tools differ by Excel version, and this panel is for locating or managing links, not for preserving formulas when you break them.

Troubleshooting

The destination formula still names the source workbook. Select the cell and inspect the full formula bar text. Edit the formula to use an equivalent local sheet or cell reference, then press Enter. If the source workbook is closed, Excel may show a full file path, so remove the external workbook and path portion carefully. Do not use a broad Find and Replace across the workbook unless you understand every affected formula: similar workbook names or different sheet references could be changed unintentionally.

The formula displays #REF! or another error. #REF! usually means a referenced cell or sheet is missing or invalid. Restore the missing sheet or data, or edit the formula to point to the correct local location. #NAME? can mean a sheet name was entered incorrectly or is missing quotation marks. #VALUE! may indicate the formula is receiving an unexpected type of data. Compare the copied formula with the source and check each referenced cell.

The result is wrong but there is no link. Check whether the destination formula still points to the source row or column. Formula-bar copying preserves the address text, so adjust it if the destination is in a different position. Also check whether the source formula used absolute references, which contain dollar signs, to intentionally keep a row or column fixed. Do not remove those markers unless you want the reference to move.

Excel keeps showing a link warning or the Workbook Links panel lists a source. The workbook may contain other external references, not just the formula you copied. Review the listed sources and inspect relevant formulas, defined names, charts, or other linked content. Removing or breaking a link can affect more than the selected cell. Make a backup and confirm the consequences before using a link-breaking command; breaking a link can replace formulas with fixed values.

The formula pastes as visible text instead of calculating. Check whether the destination cell is formatted as Text or the pasted entry begins with an apostrophe. Change the cell format to General, then re-enter the formula in the formula bar and press Enter. Changing the format alone may not convert text already entered into a formula.

Frequently Asked Questions

Does Paste Special > Formulas remove external links?
No. It pastes formulas rather than values or formatting, but any external workbook reference already inside a formula can remain. Edit the reference or replace it with a verified local reference.

How do I copy a formula without changing its cell references?
Copy the formula text through the formula bar and enter it at the destination. Its written cell addresses stay the same, so check that those are the cells you want. If you want references to shift with the destination, adjust them deliberately or use normal copying within the same worksheet.

Can I keep a formula and remove its workbook link?
Yes, if the formula’s required data is available locally. Edit the formula so it points to the matching local cells or sheets. If the linked data is not in the destination workbook, the formula cannot produce the same result without that data.

Will breaking a workbook link keep my formulas?
Usually not. Excel’s break-link action replaces linked formulas with their current values. Keep a backup and do not use it when your goal is to retain editable calculations.

Does this work the same way in Excel for Mac or the web?
The formula-bar approach is similar, but Mac uses Command+C and Command+V instead of Ctrl+C and Ctrl+V. Excel for the web supports formula editing and paste options, though browser clipboard restrictions or differences in link-management features may affect the exact workflow.

Are cell references the same as hyperlinks?
No. A cell reference tells a formula where to get data, while a hyperlink opens a location such as a web page or file. Copying a formula without external workbook links does not automatically remove a hyperlink attached to a cell.

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.