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.

Overview to copy formula and paste as text in Excel


📁 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.

Sample dataset of Sales Data

Method

1. Using FORMULATEXT Function and Notepad

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:

=FORMULATEXT(E6)

Extracting formula using FORMULATEXT function

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

The output of using the FORMULATEXT function

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

Pasting formula in 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.

Applying the Use Text Import Wizard option

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

Selecting delimited as our actual data type

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

Unchecking the option for preview

  • Check the Text from the Column data format option.

Selecting Text as our column data format

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

Final results

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

📕 Read More: 4 Quick Ways to Copy Formula Down with Shortcut in Excel

Method

2. By Keeping Show Formulas Option On

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.

Using the Show Formula feature

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

Extracting formula using Show formula feature option.

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

Final output

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

📕 Read More: 9 Tricks to Copy Formula in Excel Without Dragging

Method

3. Using VBA Macro

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.

Selecting the View Code option

  • 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

Inserting VBA code

  • Run the code and input the cell range.

Inputting the cell range

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

Final outcome

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

📕 Read More: 9 Examples to Copy Formula from Above Cell Using VBA in 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.


Related Articles

(Visited 83 times, 1 visits today)
Md Shamse Tabrej

Md Shamse Tabrej

Hi, I'm Shamse Tabrej. I'm an admirer of Excel who enjoys sharing and solving problems in Excel. I make an effort to investigate all of the choices so that I may point readers toward the most viable solutions. Both the globe and its problems are boundless. Let's assist one another in developing. Reader questions and suggestions are most welcome. 

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo