Editor choice

Excel Show Formula as Text in Another Cell (4 Easy Methods)

In Excel, you enter a formula, select references and press Enter. And drag the Fill Handle to apply the formula to the rest of the cells. Now, you want just the Formula to show inside a cell rather than the values. To do that, you can use Ribbon or Keyboard shortcut, Function, Putting Apostrophe or Space in front of the Equal sign of a Function.

I have a dataset similar to the image shown below

dataset

Dataset for Download

4 Easy Methods to show Excel Formula as Text in Another Cell

Method 1: Using Formula Ribbon 

Step 1: Go to Formulas Ribbon >> Select Show Formulas (from Formula Auditing Section).

formula ribbon After Selecting the Show Formula command, the result will be similar as shown below.

formula ribbon

You can use the keyboard shortcut CTRL+ ` to activate the Show Formula command in Excel.

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

Method 2: Using File Tab For Different Worksheets

When you have several Excel Worksheets in an Excel workbook & you specifically want to show formulas to one or two worksheets. You can use File Tab >> Options>> Advanced to select any specific worksheet and Check the option Show Formulas in Cells Instead of Their Calculated Results.

Step 1: Click on File Tab. A side window appears.

file tab method

Step 2: Select Options from the menu.

file tab

Step 3:Another window appears. On the left side of the window, select Advanced.

Step 4: On the right, Scroll down to Display Option for this Worksheets>> Select any Sheet you desire.

file tab

Step 5: Check the option Show Formulas in Cells Instead of Their Calculated Results.

file tab

Step 6: Click OK. 

The executions of the steps bring the outcome similar to the image shown below

file tab

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

Method 3: Using FORMULATEXT Function

The FORMULATEXT function fetches a Formula as a Text from reference. In pursuit of showing a formula applied in a cell to another cell FORMULATEXT is quite handy.

Step 1:Select a cell adjacent to the cells containing the formula.

Type 

=FORMULATEXT(Reference)
 

Select Reference, in this case, F4.

FORMULATEXT

Step 2:Press Enter. And Fill Handler the rest of the cells.

In a moment, the outcomes turn out to be similar to the picture below.

FORMLATEXT

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

Method 4: Using Find & Select 

In case there is a formula already applied, you can use Home Tab>>Find & Select>> Replace to convert the values in the text.

Method 4.1: Inserting a Leading Apostrophe

Step 1: Go to Home Tab>> Click Find & Select (in Editing section)>> Select Replace. 

find & select

Step 2: In the Replace Command Box, in Find What box type Equal (=) and Replace With box press the Apostrophe (`) key then Equal (=). Click on Find All.

find & select

Step 3:Then Click on Replace All.

find & select

Step 4: A pop-up window appears citing something like All done. We made 9 replacements. Click OK.

find & select

All the steps result in a picture similar to the image below.

find & select

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

Method 4.2: Inserting a Leading Space

Step 1: Repeat Step 1 from the above method. In the Replace Command Box, in Find What box type Equal (=) and Replace With box press the Space key once & Equal (=). Click on Find All.

find & select

Step 2: Then Click on Replace All.

find & select

Step 3: A pop-up window appears citing something like All done. We made 9 replacements. Click OK.

find & select

All the steps result in a picture similar to the image below.

find & select

Read More: How to Show Formulas When Printing in Excel

Self-Practice

For self-practice, I attach a dataset sample with Dataset for Download. Check it out, you will find similar to the image below

practice datasetConclusion

In the case of understanding a dataset, showing applied formulas is quite a handy way to go through. In this article, we showed how an existing formula gets shown in Active Cell or another cell using Excel tools. We used Excel Ribbon, File Tab Option, the FORMULATEXT function, and Find & Select to show formulas as text in Excel cells. I hope, you find these methods handy & time-saving. Comment, if you need further clarifications & have something to add.


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