A Beginners Guide On How To Convert Formula To Text String In Excel

Do you need to convert a formula to text in Excel?

Last updated: August 24, 2026

Excel formula to text: which one do you actually mean?

People search for "excel formula to text" wanting two completely different things, and the wrong method wastes an afternoon. Sort out which one you need before you touch the sheet.

• Meaning 1: keep the answer, lose the formula. The cell shows 47, and you want it to keep showing 47 forever even if the source data changes or gets deleted. This is what people mean by "flatten a formula", "convert formula to value in Excel" or "turn formulas into values". Paste Values is the tool.

• Meaning 2: show the formula itself. You want the cell to display =SUM(B2:B9) as readable characters, for documentation, an audit trail, a training handout or a bug report. This is "excel get formula as text" or "excel formula written as text". FORMULATEXT and the apostrophe prefix are the tools.

Rule: Paste Special, Values gives you a static value, not a text string. A pasted number is still a number. If you need the entry stored as actual text, use the TEXT function or format the cell as Text first.

Read on as we cover the following:

• Which method should you use? A side by side comparison

• The benefits of converting a formula to a text string in Excel

• 4 simple methods for converting formulas to values

• How do you get the Excel formula itself as text?

• How do you convert a value to a text string with the TEXT function?

• Is there an Excel function that converts a formula to a value automatically?

• Troubleshooting: when converting a formula to text goes wrong

• Frequently asked questions

Which method should you use? A side by side comparison

The benefits of converting a formula to a text string in Excel

• To maintain confidentiality, hiding formulas when sharing them with others is recommended. This way, you can keep the formula you used to calculate a specific value private.

• To prevent fluctuations in values caused by functions like =RAND() that will change when the sheet has alterations, you can convert the formulas to values in Excel.

• Copying cells or a group of cells between worksheets is easier if saved as values instead of formulas, simplifying operations for the user.

• You can convert the formulas to values or lock and hide them to prevent users from changing other values.

• If you have several complicated formulas and want to enhance your sheet's performance, you can transform the formulas into static values.

Things to remember before converting formulas to text strings

• Creating a copy of your current workbook before replacing cell formulas with values is recommended. This way, you can refer to the original workbook after replacing any values.

• To avoid errors, ensure that no cells depend on the current cell you are replacing when converting formulas to values.

• Paste Values cannot be undone once the file is saved and closed, so keep the backup until you have checked the numbers.

Read more: How to convert currency in Excel.

4 simple methods for converting formulas to values

This section demonstrates four methods to convert formulas to their results, each with a different approach based on your needs. While some ways may seem easier than others, all are viable options.

The examples below use the RAND function, which produces a different value every time the spreadsheet is modified. That makes it easy to see when a cell has stopped recalculating.

Method 1: Copy and paste values, the fastest way to convert a formula to a value

Copy-and-paste value is one of Excel's most frequently used methods for converting complex formulas to values.

You can use the copy-and-paste values method on a single cell or on a range of cells.

Follow the steps below to learn this basic method:

1. Select all the cells with a formula.

   

2. Right-click the selected cells, then click "Copy."

3. Select the first cell from the second column, right-click, and click "Values" under the Paste Options.

The second column now holds static values. The formulas are gone, and those numbers will no longer change when the sheet recalculates.

To convert the cells in place instead of into a second column, copy the range and then paste values straight back over the same selection.

Method 2: Use the paste values keyboard shortcut

An efficient way to turn formulas into values is to use shortcut keys instead of copying and pasting with the "paste special" option.

Follow the steps below to convert formulas to values using keyboard shortcuts:

1. Select all the cells with formulas and press the CTRL+C keys to copy.

2. Select the cell where you want to paste the converted values.

3. Use the ALT+E+S keys to open the Paste Special option.

   

4. On the Paste Special dialog box, click Values, then OK.

Paste Special replaces the formulas with static values. On Windows the full sequence without ever leaving the keyboard is CTRL+C, then ALT+E+S, then V, then Enter.

Rule: ALT+E+S is the legacy menu sequence and still works in every current version of Excel for Windows. On a Mac, use CTRL+CMD+V for Paste Special.

Method 3: Right-drag and drop to convert formulas to values

An additional method for converting formulas to values in Excel is to select the desired cells and then drag and drop them, which can be very efficient.

Follow the steps below to use the drag-and-drop method:

1. Select the cells with existing formulas you want to convert.

2. Use your cursor on the edge of the selected cells. You will notice that the regular mouse pointer will change into a four-sided pointer.

   

3. Hold the right mouse button, drag the selection to the next column, and release.

4. After releasing the right mouse button, you will see a dialog box. From the dialog box, choose the option "Copy Here as Values Only."

The right-drag method replaces the formulas with their calculated values in one motion, with no clipboard step at all. It is the answer to converting a formula to a value in Excel without Paste Special.

Method 4: Use Power Query to convert formulas to values without touching the original

Instead of copying and pasting cells as values, there is another way to keep the original sheet intact while still converting the cells with formulas.

This Power Query method leaves the formulas exactly where they are and delivers the converted values on a new worksheet.

1. Select the cells with the calculated values.

2. Move to the Data tab and click "From Table/Range."

3. Select the cell range in the Create Table dialog box and click OK.

4. When the Power Query Editor opens, click Close & Load on the Home tab.

After clicking Close & Load, a new worksheet will be added to the current Excel workbook holding the values that the original formulas produced. The source cells keep their formulas.

How do you get the Excel formula itself as text?

This is the half the internet keeps skipping. If you want the cell to read =SUM(B2:B9) rather than the number it produces, none of the paste methods above will help you. Here are the three that will.

Use FORMULATEXT to pull a formula out as a text string

FORMULATEXT is the purpose built answer to "excel get formula as text". Point it at a cell and it returns that cell's formula as a plain text string.

=FORMULATEXT(A1)

If A1 contains =SUM(B2:B9), the formula above returns the eleven characters =SUM(B2:B9) as text. Put it in the column next to your working formulas, fill it down, and you have an instant audit column showing every formula as text beside its result.

• FORMULATEXT is available in Excel 2013 and later, and in Excel for Microsoft 365 and Excel for the web.

• If the referenced cell does not contain a formula, FORMULATEXT returns #N/A. That is expected behaviour, not a bug. Wrap it as =IFERROR(FORMULATEXT(A1),"no formula") if a clean column matters more than the flag.

• The result is live text. Change the formula in A1 and the FORMULATEXT cell updates. To freeze it, paste values over the FORMULATEXT column using Method 1 above.

Microsoft documents the full argument list and error cases in the official FORMULATEXT function reference.

Rule: FORMULATEXT reads a formula from somewhere else. It cannot show the formula that lives in its own cell, because a cell can only hold one entry.

Type an apostrophe so Excel stores the formula as text

If you are writing documentation, a tutorial or an email and you want a formula to sit in a cell without ever running, put a single straight apostrophe in front of the equals sign:

'=A1+B1

The cell displays =A1+B1 and calculates nothing. The apostrophe itself is not shown in the cell and is not part of the text, although you will see it in the formula bar. This is the simplest way to paste a formula as text into a sheet without Excel grabbing it.

Format the cells as Text before you type

To make a whole block behave that way, change the number format first. Select the range, go to Home, open the Number Format dropdown and choose Text, then type your formulas. Every entry is stored as characters.

Rule: Formatting matters only at the moment of entry. Applying the Text format to cells that already contain formulas changes nothing on its own. You have to re-enter each one, by pressing F2 and then Enter, before Excel treats it as text.

That single unstated prerequisite is the reason most people conclude the Text format is broken. It is not, it just runs at entry time.

Convert a whole column of formulas to text with Find and Replace

For an existing sheet full of live formulas that you want frozen as readable text, use a prefix swap:

1. Select the range.

2. Press CTRL+H to open Find and Replace.

3. Find = and replace with #=, then click Replace All.

Because the entries no longer begin with an equals sign, Excel stops treating them as formulas and stores them as text. To bring the sheet back to life later, run the reverse: find #= and replace with =.

Show Formulas: see every formula at once without changing anything

Pressing CTRL + `, the grave accent key above Tab, toggles Show Formulas. Every cell on the sheet displays its formula instead of its result. You can also reach it on the Formulas tab. Nothing is converted and nothing is saved differently. It is a view, so press the shortcut again to switch back.

We cover the display toggle, printing formulas and the per sheet settings in more depth in our guide to how to show formulas in Excel.

How do you convert a value to a text string with the TEXT function?

Everything above deals with formulas. If what you actually need is a number turned into a genuine text string, with the formatting baked in, the TEXT function in Excel is the tool.

Note what TEXT returns: a text string, left aligned by default, which will not add up in a SUM. That is the point when you are building a label or a merged sentence, and it is a trap when you still need to do arithmetic. Use =VALUE(A1) to convert a value back to a number in Excel.

Is there an Excel function that converts a formula to a value automatically?

No, and it is worth being blunt about why. A worksheet function returns a result into the cell that contains it. No function can reach out and overwrite the cell it lives in, so no built in function can flatten a formula on its own. Anyone promising an "excel function to convert formula to value automatically" is describing something that does not exist.

What you have instead:

• F9 while editing. Double-click the cell, select the part of the formula you want frozen, press F9. That fragment becomes its calculated value. Press Enter to keep it, or Escape to abandon the edit.

• Power Query. Method 4 above. Refreshing the query re-pulls the values, so it is repeatable rather than automatic.

• A one line macro. If you do this daily, record or paste a macro. The body is a single statement: Selection.Value = Selection.Value. Assign it to a keyboard shortcut and one keypress flattens whatever you have selected.

Troubleshooting: when converting a formula to text goes wrong

Final thoughts on converting a formula to text in Excel

Converting a formula to a text string in Excel is easy once you have separated the two jobs. Paste Values keeps the answer and drops the formula. FORMULATEXT, the apostrophe prefix and the Text format keep the formula and drop the calculation. Pick the half you need and the rest is a couple of clicks.

Working in the other spreadsheet? The same split applies, and we walk through it in how to remove a formula and keep the value in Google Sheets. If you are trying to strip formulas out of a workbook entirely, start with how to remove formulas in Excel. And if long text entries are now spilling out of your cells, see how to wrap text in Excel.

Visit Simple Sheets for more easy-to-follow guides and examples, and remember to read the related articles section of this blog post.

For the most straightforward Excel video tutorials, subscribe to Simple Sheets on Youtube!

Frequently asked questions on how to convert a formula to text in Excel

How do I convert a formula to text in Excel?

It depends which result you want. To keep the calculated result and drop the formula, copy the cells and use Paste Special, Values. To display the formula itself as readable text, use =FORMULATEXT(A1) in another cell, or type an apostrophe before the equals sign so Excel stores the entry as text.

How can I convert formulas to text strings for multiple cells?

Select the whole range by clicking and dragging, or by holding CTRL to pick scattered cells. Then copy the selection and paste it back as Values. The same selection works for FORMULATEXT: enter it once in the adjacent column and fill it down the full range.

How do I convert formulas to values in the same cell?

Select the cells, press CTRL+C, then paste values straight back over the same selection with ALT+E+S, V, Enter. Excel replaces each formula with the number it had just produced, in place.

Is there an Excel function to convert a formula to a value automatically?

No. A worksheet function cannot overwrite the cell it sits in, so no built in function can flatten a formula by itself. Use Paste Special Values, F9 while editing, Power Query, or a one line macro that runs Selection.Value = Selection.Value.

How do I convert a formula to a value in Excel without Paste Special?

Two ways. Select the cells, hold the right mouse button on the selection edge, drag to the next column and choose Copy Here as Values Only. Or edit the cell, select the formula in the formula bar, press F9, then press Enter.

How do I get an Excel formula as text instead of its result?

Use =FORMULATEXT(A1), which returns the formula in A1 as a text string such as =SUM(B2:B9). It needs Excel 2013 or later and returns #N/A if the referenced cell contains no formula.

How do I convert a formula to a number in Excel?

Paste the cells back as Values. The result stays a real number that you can still sum and format. If the value has already ended up stored as text, sitting left aligned and refusing to add up, convert it back with =VALUE(A1).

Why does my formula show as text instead of calculating?

The cell was formatted as Text before the formula was typed, or the entry begins with an apostrophe, or Show Formulas is switched on for the sheet. Set the format to General and re-enter the cell with F2 and Enter, delete the apostrophe, or press CTRL + ` to turn Show Formulas off.

Related Articles:

How to Compare Two Columns in Excel

How To Divide In Excel: A Comprehensive Guide

Excel: Remove Trailing Spaces Quickly and Easily With These Simple Steps