Editor choice

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

Get FREE Advanced Excel Exercises with Solutions!

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 to show formula as text in another cell in Excel.


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

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


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


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


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


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


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


Download Practice Workbook


Conclusion

In essence, this article shows 6 effective methods regarding how to show formulas 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.


Related Articles


<< Go Back To Show Excel Formulas | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo