In Excel, a lot of things are done using the functions that together make formulas. In this article, we are going to show you some tips on “How to show formula in Excel cells instead of value”. Sometimes while performing a task in Excel you are more concerned with the formulas than the results. Hopefully, after reading this article you will get a number of ways of doing that.
How to Show Formula in Cells Instead of Value in Excel: 7 Ways
Suppose you have calculated areas where the heights and widths are given for some given data. The formula for calculating the area is, Area= Height*Width. Here the heights and widths are given in certain cells. Suppose you calculated the area using the formula =B5*C5. Where the height is in cell B5 and the width is in cell C5.
Instead of showing the results, we will only show the associated formula for these.
In this section, I will show you 7 handy approaches to show formula in Excel cells instead of value. I will discuss them with proper illustrations. Let’s figure them out!
1. Using Show Formulas Option from Formulas Tab
This is the best way to show the formulas in Excel. Let’s say your worksheet looks like the picture below where you calculated the areas based on some heights and widths.
We want to show the formula in the corresponding cell that was used to calculate the area using the Show Formulas option from the Formulas tab. The procedure for this is given below.
Steps:
- First of all, click on the Formulas tab on top of your Excel worksheet.
- Now, click on the Show Formulas option under the Formula Auditing group.
- After clicking the Show Formulas option, you will get to see the below result where the formulas will be shown instead of the values in the formulated cells.
2. Using Advanced Excel Option
This is kind of a long process and after doing this you will always get to see the formulas instead of values. The process is stated below.
Steps:
- Click on the File tab on top of your Excel worksheet.
- After this click on the Options section below.
- In the Excel Options dialogue box, click on the Advanced option and scroll downwards until you get the Display options for this worksheet
- Now in the Display options for this worksheet select the worksheet where you want the formulas to be displayed and click on Show formulas in cells instead of their calculated results. After clicking this press OK.
- This will give you the below result. Now, whenever you are going to insert a formula for getting a result it will only show you the formula instead of values.
3. Using Keyboard Shortcut
The fastest way of showing formulas instead of values is by using the keyboard shortcut. In anywhere of your Excel worksheets press (CTRL and `) together. You will see that the values that are found by using formulas are turned into formulas.
This is because on those cells those formulas were applied. Press (Ctrl and `) again if you want to disable this feature.
4. Using ‘’=’ Instead of “=” While Applying Formula
Suppose you have your worksheet with a formula applied to it and you want some or let`s say one of your cells to show you the formula that has been applied. To do that, click on that specific cell and insert the grave sign (`) just before the equal sign.
Actually, this is a manual approach and not as effective as the others as you can apply it only for one specific cell. Still, it may be useful if you want to show a formula for just one specific cell.
5. Replacing “=” with “’=”
We all know that the Excel Formula starts with the “=” sign. Now if you insert anything before the equal sign the formula will not be applied. Usually, the grave signs are used ahead of the equal sign to show formulas. If you applied formulas in your worksheet and somehow you want some or every formulated cell of your worksheet to show the formulas you can replace the “=” sign with a “`=” sign. To perform this press CTRL+H together. You will get a Find and Replace dialogue box. In that dialogue box do the following and press the Replace All option. You will get to see all of your formulas by doing this.
6. Using Format Cells Option
If you make the formatting of the formulated cells as the Text format you can easily show the formulas instead of values. To do this, follow the steps below.
Steps:
- First of all, select the formulated cells of your worksheet and right-click on your mouse button to select the Format Cells option. Or you can just press CTRL+1.
- Then, the Format Cells dialog box will open up. Under the Category icon, select the Text option and press OK.
- Initially, you will not see the formulas. Click on any of those formulated cells and on the Formula Bar press ENTER.
- Here, you will see that instead of values you are now getting the formula.
- Now, repeat the same for each cell you want to show the formula.
Note: This is not a good practice to show formulas after the formatting if you need to convert your formulas as the values again you again need to format them as General or Numbers.
7. Applying FORMULATEXT Function
The FORMULATEXT function allows a user to select a cell containing a specific formula and return the formula of the selected cell as a text string in the cell where the function is applied. It will show the formula in another cell. So, you can show the formula of a cell instead of the value by applying the FORMULATEXT function. In order to do so, proceed with the following steps.
Steps:
- Firstly, type the following formula in a new cell where you want to show the formula.
=FORMULATEXT(D5) Here,
- D5 = the cell containing the formula that you want to show
- Then, press ENTER and you will see that the cell has returned the formula as the text string.
- Now, use the Fill Handle tool to Autofill the formula to the cells in which you want to show the formula instead of the value.
- Finally, you will get the cells showing formulas.
Download Workbook
You can download the workbook from the link below.
Conclusion
As you can see we have demonstrated so many methods on how to show formulas in Excel cells instead of values. You can choose any option you like. In my opinion, using the Keyboard Shortcut and Clicking Show Formulas from the Formulas Tab are the best options among these. Hope you will like this article. Don’t hesitate to ask us any questions. Cheers.
Related Articles
- How to Display Cell Formulas in Excel
- How to Show All Formulas in Excel
- Why Excel Shows Formulas Instead of Results
- How to Show Value Instead of Formula in Excel
- How to Show Formula as Text in Another Cell in Excel
- How to Show Formulas When Printing in Excel
- [Fixed!] Formula Result Showing 0 in Excel
- How to Stop Showing Formulas in Excel
<< Go Back To Show Excel Formulas | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
