Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Editor choice

How to Show Formula as Text in Another Cell in Excel (6 Ways)

Certainly, Excel is a popular and useful tool for analyzing large sets of data. Now, wouldn’t it be great if we could display the formulas in our spreadsheet? Sounds complex, right? Wrong! In this article, we’ll demonstrate 6 ways how to show formula as text in another cell in Excel.


Download Practice Workbook


6 Ways to Show Formula as Text in Another Cell in Excel

First of all, let’s consider the List of Employee Salary dataset shown in the B4:F13 cells containing the Job ID, Name, Salary, Allowances, and Total Salary columns respectively. Here, we want to display the formula used to obtain the Total Salary, hence, let’s see each method in detail.

Dataset for excel show formula as text in another cell

Here, we have used the Microsoft Excel 365 version; you may use any other version at your convenience.


1. Using Formulas Ribbon

First and foremost, let’s begin with the simplest and most obvious method, that is to say, we’ll use Excel’s Formulas Ribbon.

📌 Steps:

  • In the first place, go to the Formulas ribbon >> click the Show Formulas button.

📃 Note: We can also use the CTRL + ` (Tilde) keys to show the formulas.

Using Formulas Ribbon

Boom! That is how simple it is to show formulas as text in another cell in Excel.

📃 Note: When using this method, Excel automatically changes the text alignment and the column width.

excel show formula as text in another cell using Formulas Ribbon

Read More: How to Stop Showing Formulas in Excel (2 Methods)


2. Utilizing Excel Options

Alternatively, we can use Excel Options to display the formulas as text. Now, allow us to demonstrate the process in the steps below.

📌 Steps:

  • First of all, click the File tab located at the top-left corner.

Utilizing Excel Options

  • Next, press the Options button at the bottom.

Clicking Options button

  • Then, move to the Advanced tab >> scroll down to the Display options for this worksheet section >> tick Show formulas in cells instead of their calculated results >> hit OK.

Enabling show formulas option from the advanced section

Voila! The results should resemble the image shown below.

📃 Note: By default, Excel automatically increases the column width and changes the text alignment.

Excel show formula as text in another cell Utilizing Excel Options

Read More: How to Show Formula in Excel Cells Instead of Value (6 Ways)


3. Implementing FORMULATEXT Function

Now, functions are the heart and soul of an Excel worksheet! Luckily, Excel has the FORMULATEXT function to display the formulas as text. Therefore, let’s it in action.

📌 Steps:

  • Initially, jump to the G5 cell >> enter the formula given below >> drag the Fill Handle tool to copy the formula to the cells below.

=FORMULATEXT(F5)

Here, the F5 cell refers to the Basic Salaryof James.

Excel show formula as text in another cell with FORMULATEXT Function

Read More: How to Convert Text to Formula Using the INDIRECT Function in Excel


4. Applying Find & Select Feature

Conversely, we can also employ the handy Find & Select feature of Excel to replace values in our worksheet with the formula text.


4.1 Inserting Leading Apostrophe

For one thing, we can insert a leading Apostrophe comma () before the Equal (=) sign so that Excel treats the formula as a string of text.

📌 Steps:

  • First, select the F5:F13 cells >> navigate to the Find & Select drop-down >> choose the Replace option.

Inserting Leading Apostrophe

  • Second, in the Find what field enter an Equal sign >> in the Replace with field type a leading Apostrophe and then an Equal sign >> hit Replace All.

Steps for Find and Replace

  • Third, click OK to close the alert.

Finally, the results should look like the picture given below.

Excel show formula as text in another cell by inserting Leading Apostrophe

Read More: How to Show Value Instead of Formula in Excel (7 Methods)


4.2 Using Leading Space

By the same token, we can also place a Space character ( ) in front of the Equal sign to show formulas as text in another cell in Excel.

📌 Steps:

  • To begin with, select the F5:F13 cells >> proceed to Find & Select, then the Replace option >> in the Replace with field, remove the Apostrophe and insert a Space character >> press Replace All.

📃 Note: You can also press the CTRL + H keys to directly open the Find & Replace dialog box.

Using Leading Space

Eventually, the final output should appear in the screenshot below.

Excel show formula as text in another cell using Leading Space

Read More: How to Show Formulas When Printing in Excel


5. Employing Format Cells Option

Besides, we can implement the Format Cells option to also display formulas in our worksheet. So, let’s go through the steps in detail.

📌 Steps:

  • At the very beginning, go to the F5 cell >> hit the CTRL + 1 shortcut keys.

Employing Format Cells Option

  • Afterward, in the Format Cells window, select the Text tab >> click on OK.

Formatting as Text

  • At this point, move to the F5 cell >> press the F2 key and then the ENTER key >> repeat the same process for the cells below.

Lastly, the end result should look like the figure given below.

excel show formula as text in another cell with Format Cells Option


6. Incorporating VBA Code

Last but not least, we can use VBA Code to define a custom function that shows formulas as text in another cell in Excel.

📌 Steps:

  • To start with, navigate to the Developer tab >> click the Visual Basic button.

Incorporating VBA Code

  • Now, go to the Insert tab >> select Module.

Inserting Module

For ease of reference, copy the code from here and paste it into the window as shown below.

Function Disp_Form(arr As range)
Disp_Form = arr.Formula
End Function

VBA Code

Code Breakdown

Here, we’ll explain the VBA code used to define the function for showing formulas as text.

  • In the first sub-routine, enter a name, here it is Disp_Form()
  • Next, define the argument arr and assign it as Range.
  • Then, use the Range.Formula property to display the formulas as text.

VBA Code explanation

  • Following this, close the VBA window >> jump to the G5 cell >> call the Disp_Form function.

=Disp_Form(F5)

In this case, the F5 cell represents James’s “Basic Salary.

excel show formula as text in another cell with VBA code


Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section


Conclusion

In essence, this article shows 6 effective methods regarding how to show formula as text in another cell in Excel. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful and if you have any further queries or recommendations, please feel free to comment here. Lastly, visit ExcelDemy for many more articles like this.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo