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.
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.
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.
Read More: How to Stop Showing Formulas in Excel
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.
- Next, press the Options button at the bottom.
- 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.
Voila! The results should resemble the image shown below.
📃 Note: By default, Excel automatically increases the column width and changes the text alignment.
Read More: How to Show Formula in Excel Cells Instead of Value
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 Salary” of James.
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.
- 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.
- Third, click OK to close the alert.
Finally, the results should look like the picture given below.
Read More: How to Show Value Instead of Formula in Excel
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.
Eventually, the final output should appear in the screenshot below.
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.
- Afterward, in the Format Cells window, select the Text tab >> click on OK.
- 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.
Read More: How to Display Cell Formulas in Excel
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.
- Now, go to the Insert tab >> select 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
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.
- 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”.
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.
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.
