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

It is important to show formulas in lieu of values in Excel for myriad operations or while debugging. In this article, we will learn how to show formula as text in another cell in excel in 4 convenient approaches.

📁 Download Excel File

Download the following Excel workbook to realize the topic more clearly.

Learn to Show Formula as Text in Another Cell in Excel with These 4 Suitable Approaches

Today we will learn how to show the formula as text in another cell in excel using the following sample dataset. In the dataset, we calculated a 5% Commission for each Sales Person respective to their Sales.

In our first approach, we will use the Excel Ribbon to show the formula as text. Let’s start!

  ⬇️⬇️ STEPS ⬇️⬇️

• First, navigate to the Formulas tab.
• Under the Formula Auditing group, click Show Formulas.

• Now, you can see all the formulas used in corresponding cells.

In our second approach, we will show how to show formulas as text using the Excel Options.

  ⬇️⬇️ STEPS ⬇️⬇️

• Initially, go to the File tab, and select Options from the bottom left corner of the window.

• Momentarily, an Excel Options dialog box will appear on the screen.
• Click Advanced and scroll down to Display options for this worksheet.
• Now, check the Show formulas in cells instead of their calculated results. Click OK.

• Done! You will see all the formulas used in this worksheet.

We will demonstrate how to show formulas as text using the apostrophe symbol or space.

  ⬇️⬇️ STEPS ⬇️⬇️

• In the beginning, press Ctrl+F concurrently.
• The Find and Replace dialog box will open.
• Type in = in the Find what box and write ‘= or add a space in front of = in the Replace with box, under the Replace tab.
• Click Replace All.

• A tiny dialog box will show how many replacements were made. Click OK.

• Close the Find and Replace dialog box.
• All your formulas will appear on the corresponding cells.

We will now use the FORMULATEXT function to show the formulas used.

  ⬇️⬇️ STEPS ⬇️⬇️

• Select cell F6 and Enter the formula below:

• Now from the bottom right corner of cell F6 use the Fill Handle icon and double-click.

• You’re all set. The formulas used in the respective cells are shown in the Formula column.

How to Show Value Instead of Formula in Excel

If your worksheet shows formulas instead of values you can follow the next steps. We will use keyboard shortcuts to show value in place of formulas.

  ⬇️⬇️ STEPS ⬇️⬇️

• First of all, select the cell where you want to show the value instead of the formula. In our case, we choose cell E6.
• Now press F2. This will let you edit the cell. Then press F9. Next, press Enter to move to the next cell.

• Great! The cell is now showing value in lieu of formula.
• Now do this to all the cells that are not showing values.

📝 Takeaways from This Article

📌  First of all, we used the Show formulas option from the Excel Ribbon under the Formulas tab.

📌  Secondly, we demonstrated how to show formulas as text using the Excel Options.

📌  Thirdly, we added an apostrophe symbol or a space to show formulas as text utilizing the Find and Replace dialog box.

📌  Afterward, we implemented the FORMULATEXT function to show formulas as text in another cell.

📌  Finally, we learned how to show value instead of formula by applying the keyboard shortcuts.

Conclusion

That concludes the discussion for today. These are some convenient methods to show formula as text in another cell in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.