Excel Copy Formulas as Text: 3 Ways

This article explains how to copy formulas from Excel as plain text, so you can paste them into another cell, a document, or an email without Excel recalculating or changing the references. You will learn three reliable methods: using the formula bar, using Find & Replace, and using a simple VBA macro. Each method works in Excel 2016, 2019, 2021, and Microsoft 365.

Why Copy Formulas as Text?

When you copy a formula in Excel and paste it normally, Excel adjusts relative cell references. For example, if you copy =A1+B1 from row 1 to row 2, it becomes =A2+B2. That is usually what you want, but sometimes you need the exact formula text—for documentation, for sharing with a colleague, or for building a template. Copying as text preserves the formula exactly as typed, with no recalculation and no reference shifting.

Method 1: Copy from the Formula Bar

The simplest way to copy a single formula as text:

1. Click the cell containing the formula.
2. Look at the formula bar (the long input box above the column letters). Click inside it, or press F2 to edit the cell.
3. Select the entire formula text with your mouse or press Ctrl+A (Windows) or Cmd+A (Mac) while the cursor is in the formula bar.
4. Press Ctrl+C (or Cmd+C) to copy.
5. Press Esc to exit edit mode without changing the cell.
6. Paste wherever you need—a text editor, email, or another Excel cell (paste as text using Ctrl+Alt+V and choose “Text�).

This method copies the formula exactly as shown in the formula bar, including the leading equals sign and any spaces. It works for one formula at a time.

Method 2: Use Find & Replace to Convert Formulas to Text

If you have a range of formulas and you want to convert them all to text (so they display as text instead of calculating), you can use Find & Replace:

1. Select the range of cells containing formulas.
2. Press Ctrl+H to open Find & Replace.
3. In the “Find whatâ€� box, type = (just the equals sign).
4. In the “Replace withâ€� box, type a character that does not appear in your formulas, such as # or ~.
5. Click “Replace All�. This changes all formulas to text that starts with your placeholder character, but the formulas are no longer calculated.
6. Now press Ctrl+H again, and replace your placeholder character with = (so the text starts with an equals sign again).

Now the cells contain the formula as text, not as a live formula. You can copy these text cells anywhere. This method is useful for creating a snapshot of your formulas for documentation. Be careful: this permanently replaces the formulas with text, so save your workbook first or use a copy.

Method 3: Use a VBA Macro to Export Formulas

For a large worksheet with many formulas, you can use a simple macro to copy all formulas as text into a new column or a text file. Here’s a basic macro that writes the formulas from column A into column B as text:

Sub CopyFormulasAsText()
    Dim rng As Range
    Dim cell As Range
    Set rng = Range("A1:A10")  ' change to your range
    For Each cell In rng
        If cell.HasFormula Then
            cell.Offset(0, 1).Value = cell.Formula
        End If
    Next cell
End Sub

To use this:

1. Press Alt+F11 to open the VBA editor.
2. Go to Insert > Module.
3. Paste the code above.
4. Modify the range to match your data.
5. Close the editor and run the macro (press Alt+F8, select the macro, and click Run).

The macro reads the .Formula property of each cell, which returns the formula as text (in English, with commas as argument separators, regardless of your regional settings). It writes that text to the adjacent column. You can then copy those text cells anywhere.

Common Mistakes and Pitfalls

• Pasting into Excel recalculates: If you paste a formula text into an Excel cell, Excel will treat it as a formula and calculate it. To paste as text, use Paste Special > Values or format the destination cell as Text before pasting.
• Relative references change: When you copy a formula normally (not as text), Excel adjusts relative references. To prevent this, use absolute references (e.g., $A$1) or copy as text.
• Regional differences: In some locales, Excel uses a semicolon ; as the argument separator instead of a comma. The formula bar shows the correct separator for your locale, but the VBA .Formula property always returns the comma-separated English version. If you need the localized version, use .FormulaLocal instead.
• Hidden formulas: If a cell is formatted as text before you enter a formula, Excel will not calculate it; it will store the text as-is. This can be used intentionally to write formulas as text, but it can also cause confusion.

When to Use Each Method

• Formula bar: Best for a single formula, quick copy, or when you need to copy a formula from a cell without altering the original.
• Find & Replace: Best for converting a range of formulas to text permanently, for example, to create a documentation sheet or to send a snapshot of your logic.
• VBA macro: Best for large datasets or when you need to extract formulas from many cells repeatedly. It also allows you to output to a text file or another worksheet.

Alternatives: Exporting Formulas to a Text File

If you need to save all formulas from a workbook to a text file for version control or auditing, you can use Excel’s built-in “Save As� option with a special format:

1. Go to File > Save As.
2. Choose a location and file name.
3. In the “Save as type� dropdown, select CSV (Comma delimited) or Text (Tab delimited)—but note that this saves only the active sheet and saves calculated values, not formulas.

To export formulas, you need VBA or a third-party tool. A simple VBA loop can write formulas to a text file using the Print statement. This is more advanced but gives you a clean text dump.

Frequently Asked Questions

How do I copy a formula without changing cell references?

To copy a formula exactly without changing references, you can copy the formula text using the formula bar method, or you can use absolute references (e.g., $A$1) before copying. Alternatively, copy the formula as text and paste it into a cell formatted as Text.

Why does my pasted formula show the same result as the original?

If you paste a formula into a new cell and it shows the same result, Excel likely adjusted the references relative to the new location. For example, copying =A1+B1 from row 1 to row 2 gives =A2+B2, which may produce a different result if the new row has different values. If you want the exact same formula, use absolute references or copy as text.

Can I copy formulas as text without using VBA?

Yes, the formula bar method works for a single formula, and the Find & Replace trick works for a range. Both require no VBA. For a whole sheet, you can also use the “Show Formulas� mode (Ctrl+`) and then copy the displayed text, but that copies the entire sheet’s display, which may be messy.

What is the difference between .Formula and .FormulaLocal in VBA?

.Formula returns the formula in English with comma separators (e.g., =SUM(A1,A2)), while .FormulaLocal returns the formula in the user’s language and with the local argument separator (e.g., =SUMME(A1;A2) in German). Use .FormulaLocal if you need the exact text that appears in the formula bar in a non-English Excel.

How do I copy formulas as text to another Excel sheet?

Use the formula bar method to copy the text, then click the destination cell, format it as Text (Home > Number Format > Text), and paste. Alternatively, use a VBA macro to write the .Formula property directly to the destination cell as a string.

Does copying formulas as text affect performance?

No, copying formulas as text is a one-time operation. However, if you have many formulas and use Find & Replace to convert them to text, the workbook will no longer recalculate those cells, which can improve performance if the formulas were heavy, but you lose live calculations.

Related

• How to Control Gall Aphids Without Harming Beneficial Insects
• WoW Temporary Economy Holds: Do They Stop Gold?
• Sync Files Between Computer and USB: Full Guide