Method 1 – Use the Text Import Wizard and the Notepad to Copy a Formula and Paste it as Text in Excel
1.1 Display Cell Formulas
The image below shows the formula result in column E.
To show the hidden formula, use the FORMULATEXT Function.
- Create a new column with a column header: Formula.
- Enter the following formula in F5.
= FORMULATEXT(E5)
It will display the hidden formula in E5.
- Press ENTER.
The hidden formula used in E5 is displayed in F5.
- Drag the Fill Handle icon to the end of the column.
All formulas used in column E are displayed:
Read More: How to Copy Formula in Excel Without Dragging
1.2 Use the Notepad
Copy the formulas into the Notepad and copy them back to the Excel worksheet:
- Select all formulas in column F.
- Press CTRL + C to copy them.
- Go back to the Windows desktop and right-click it.
- Choose New.
- Follow the arrow direction and select Text Document.
This will open the Notepad.
- Press CTRL + V to paste the copied formulas into the Notepad.
- Select all the formulas and press CTRL + C.
Read More: How to Copy Formula Down Without Incrementing in Excel
1.3 Use the Text Import Wizard
- Create a new column to paste the formulas as text. Here, Formula as Text in column H.
- Select the top cell in the new column.
- Go to Home > Paste > Use Text Import Wizard.
- In the dialog box, check Delimited and click Next.
- Uncheck all options in Delimiters.
- Click Next.
- In Column Data Format, select Text and click Finish.
The formulas were pasted as text in Formula as Text, column H.
Read More: How to Copy Formula to Another Sheet in Excel
Method 2 – Using a VBA Code to Copy a Formula and Paste It as Text in Excel
- Press ALT + F11 to open the VBA editor.
- Create a new Module in Insert.
- Enter the following VBA code.
Sub PasteFormulaAsText()
 Dim aa As Long
 Dim bb As Long
 Dim cc As String
 Dim dd As String
 aa = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
 bb = Cells(Rows.Count, "E").End(xlUp).Row
 cc = Columns("E").SpecialCells(xlCellTypeFormulas)(1).Formula
 dd = Left(cc, 3)
  With Cells(1, aa).Resize(bb)
   .Value = Range("E1:E" & bb).Formula
   .SpecialCells(xlCellTypeConstants).Clear
   .Replace dd, Mid(dd, 2), xlPart
 End With
End Sub
- Save the code in the VBA code editor.
To run the code:
- Press ALT + F8 to open the Macro dialog box.
- Click Run.
All the formulas in column E are pasted in the next column.
Things to Remember
- You can also press ALT + F8 to open the Macro dialog box.
- To run the VBA code you can also press F5.
Download Practice Workbook
Download the Excel file.
Related Articles
- How to Copy and Paste Formulas from One Workbook to Another in Excel
- [Fixed] Excel Not Copying Formulas, Only Values
- VBA to Copy Formula from Cell Above in ExcelÂ
- Excel VBA to Copy Formula with Relative ReferenceÂ
<< Go Back to Copy Formula in Excel | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!