How can you display the Formulas within the cells instead of the cell results on your spreadsheet?

How to show formulas in Excel instead of the values is a very common question faced by every individual who wishes to keep track of their calculations in a spreadsheet while working with large amounts of data. Most Excel beginners know that Excel showing formulas instead of values is a common problem which obstructs their learning path and makes them confused, without realizing that this is exactly how MS Excel works with formulas.

Showing formulas instead of values in Excel can help you keep track of data in each calculation and check if the formula used has the correct set of values. It also helps you in checking for errors, irrespective of whether it is your own data or somebody else’s.

Excel has a bunch of in-built tools and methods that will help you display formulas in Excel through a few easy steps. In this article today, we shall be delving into each of these methods in detail. Read on to find out more.

Check How to Lock Formulas in Excel for a shared spreadsheet.

3 Methods to Show Formulas In Excel Instead Of The Results

Showing formulas instead of values is one of the common issues most people encounter while learning about MS Excel. To help you do so, there are three methods which are currently available for users, which are as follows:

1. Shortcut Command Method
2. Formula Method/Ribbon Method
3. Show formulas in cells instead of their calculated results option/ File Tab Method

Learn How to Divide Values in Excel here.

Method 1 : Shortcut Command Method

One of the easiest ways to display formulas in Excel instead of the values is the Shortcut Command method.

Step 1: In the above example, we are shown the sales done by two electronic shops, with column E displaying the ratio between the sales for each product for both the shops. It must be noted that the cells in D and E have a formula which is being used to calculate the ratio of sales.

Step 2: Now, if you need to display the formulas of these cells, you can simply click the ‘Show Formulas’ option in the ribbon below the menu bar or click CTRL+ on your keyboard and the column with results including the formula would start appearing.

Learn How to SUM a Column in Excel to add values.

Method 2 : Formula Method/ Ribbon Method

If you do not want to use the shortcut command method, using the ribbon/formula method is probably the easiest way to display formulas in MS Excel. The reason it is called the ‘Formula Method’ is because there is a direct option in the Formula tab on Excel to get the results. It is also sometimes known as the ‘Ribbon Method’ because the tab is located at the top of the Excel worksheet ribbon.

You can follow the below mentioned steps to display formulas instead of values in the Excel worksheet, using this method:

Step 1: Click on Formulas tab on the ribbon

Step 2: In the ‘Formula Audition Group’, click on ‘Show Formulas’

Step 3: Result : The cells will start showing the formulas in place of values.

If you wish to have the values back, just click on the Show Formulas button and you will have the results obtained by the formulas back.

Learn How to Use the IF Function In Excel here.

Method 3 : Show Formulas In Cells Instead Of Their Calculated Results Option/ File Tab

This is the third method using which you can easily show formulas instead of values in Excel.

Step 1: Go to Files and click on ‘Backstage View’, where you need to then click on the Options category from the drop-down, on the left side.

OR

Step 2: Navigate to the ‘Excel Options’ dialog box, where you have to click on the ‘Advanced’ tab from the left side.

Step 3: Once the Advanced category has been selected, you should scroll down to the Display Options For This Worksheet area, followed by clicking on ‘Show Formulas In Cells Instead Of Their Calculated Results’.

Step 4: Once it gets activated, press OK on the ‘Excel Options’ dialog box to return to the worksheet, where all the formulas will be displayed for each cell.

This method is a very convenient and handy way to display formulas when dealing with multiple sheets in Excel.

Check How to Use the Average Function in Excel here.

Why Does Excel Show Formulas Instead Of Results In Some Cases?

A very common concern of regular Excel users is that sometimes their worksheets start displaying the formulas instead of the values. However, it is not a serious issue and there is nothing to worry about. This can happen due to a lot of reasons:

1. You may have clicked on the show formulas in the ribbon by mistake or might have clicked on CTRL+, which is why your Excel is showing formulas instead of the values. You can get rid of this problem by clicking on Show Formulas again or press CTRL+ and your results will be back.
2. Another reason why your Excel sheet is having problems displaying values, could be because of a mistake in the formula itself. You might have added space or a single quote ( ‘) adding the equal sign in the formula. Excel understands this error as a normal text entered in the formula field, hence, you need to either remove the space or single quote to get your values.
3. A common error made by users which makes the formula appear is the selection of the cell’s formatting to text. Similar to the space and single quote error, in this case too, Excel reads the formula added as normal text. To fix this error, you need to make the cell’s formatting to General in the Number Group in Home Tab. Another way to fix it is by clicking on F2 and pressing enter.

Know How to Insert a Check Mark Symbol in Excel here.

We hope that with the aforementioned methods, you would have gotten an idea on how to show formulas in Excel instead of the values and also how to get rid of these tiny but troublesome errors that lead to the formula being displayed in place of the values.

If you want to learn how to show formulas in Excel in place of values and other advanced Excel tips and tricks in a hands-on manner, we recommend you enroll for the Advanced Excel course, offered by Testbook. For those who want to access more such articles, exam preparation tips, mock test papers and other similar educational resources, you can download the Testbook App from the Google Play store and get amazing discounts and offers!

How can you display formulas within the cells instead of the cell results Excel?

How can you display the formula and not the result?

How do you display formulas in a spreadsheet?

What should you do if you see pound signs instead of values or results of formulas?