3 Quick Ways to Copy Formula and Paste as Text in Excel

In this article, I’ll show you three excellent methods for how to copy formula and paste them as text in Excel. Furthermore, these strategies will allow you to solve the problem that brought you here. However, you will learn some important Excel features and functions in this post that will come in handy for any Excel task.

📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

Learn to Copy Formula and Paste as Text in Excel with These 3 Useful Techniques

Here in this article, we will learn how to copy formula and paste them as text in Excel using different approaches. To be exact, I’ve provided a total of 3 methods down below. We consider a sales bill as our dataset that contains unit price, quantity, and the total amount for various products.

Firstly we will employ the FORMULATEXT Function to copy the formula and paste as text in Excel. The FORMULATEXT function in Excel outputs the cell’s formula as plain text. However, to use, simply supply a reference to the cell whose formula you wish to get as the function’s sole argument. The formula =FORMULATEXT(D1) will yield the text “=SUM(C1:C5)” if cell D1 has the formula “=SUM(C1:C5)”.  Excel 365 and later have access to the feature. Let’s explore this approach step by step

⬇️⬇️ STEPS ⬇️⬇️

• Add a column to the right side of the dataset which will contain a formula.
• Select cell E6.
• Write the following formula:

• Press Enter and drag the fill handle to see the results.

• Now copy down the cells F6:F10.
• Then paste it to Notepad by creating a new file and again copy it from Notepad.

• Then go to cell H6 which belongs to the newly added columns.
• Select the option Paste from the top ribbon of the Home tab.
• Afterward, select the option Use Text Import Wizard.

• Consequently check the option delimited as I showed in the figure and click Next.

• Uncheck all the options as I demonstrated below and click Next.

• Check the Text from the Column data format option.

• Finally, select the option Finish to see the results.

Accordingly, you can copy the formula and paste as text in Excel.

The “Show Formulas” choice under the “Formulas” tab of the ribbon in Excel allows you to see the formulas behind the calculations in the cells rather than the results. The benefit of this method is you will able to extract the formula in one click without using the formula.

This is the procedure for doing so:

⬇️⬇️ STEPS ⬇️⬇️

• First, go to the Show Formula option from the Formula tab.

• Then you will get the formula of the column Total.

• Afterward, paste it in column F in Formula in Text column from Notepad exactly the same way we did in the first method.

Hence you can copy the formula and paste it as text in Excel.

Finally, we used the VBA macro in this technique. Excel the VBA macros are a collection of instructions that can be used to automate various operations, including data preparation, chart creation, and calculation. Examples of VBA macro functionality include the creation of a new worksheet with pre-populated data taken from another worksheet depending on certain criteria and automatically formatting a set of cells each time the workbook is accessed. The steps are given in the proper sequence below.

⬇️⬇️ STEPS ⬇️⬇️

• Primarily, take the cursor to the sheet name and click right.

• Afterward, select the option View code.
• Insert the following VBA code:

 Sub CopyRangeFormulaText()
    Dim formula As String
    Dim rng As Range
    Dim cel As Range
    Set rng = Application.InputBox("Select the range of cells to copy", Type:=8)
    For Each cel In rng
        x = cel.formula
        cel.Offset(0, 1).Value = "'" & x
    Next cel
End Sub

• Run the code and input the cell range.

• Finally, you will see the formula in text format.

In this way, you will be able to copy the formula and paste it as text into Excel.

📝 Takeaways from This Article

📌  You can copy the formula and paste it as text using the FORMULATEXT function.

📌  Moreover, You can copy the formula and paste it as text using the Sow Formula option.

📌  In addition, You can copy the formula and paste it as text using VBA macro.

📄 Important Notes

🖊️  Verify that you’ve employed Absolute Reference when it was required. To get an absolute cell reference, press F4.

🖊️  Moreover, always look for ways that are easy to use in different situations.

🖊️  Finally, every time you try to use shortcuts on the keyboard.

Conclusion

First and foremost, I hope you were able to apply the principles I taught in this Excel lesson on how to copy formula and paste as text in Excel. As you can see, there are numerous ways to accomplish this. However, choose the strategy that is best suited to your situation with care.  Above all, I sincerely hope you can put it to good use. Please share your thoughts in the comments section about how you felt throughout the article and what we should change or add to make things better for you. However, if you have any Excel-related issues, please leave them in the comments section. The Excelden team is available to answer your inquiries at any time. Finally, keep reading to educate yourself. Please visit our website Excelden.com for further Excel-related articles.