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

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. 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`````` 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. • 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. ## Related Articles 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 Advanced Excel Exercises with Solutions PDF  