Show formula in Excel
MS Excel is a software tool that provides multiple sheets in an Excel document. The Excel users can store their data in it and perform calculations on the data stored in it. They can operate the Excel sheets and their data. This can be used for data manipulation. Several formulas can be applied to a single Excel sheet.
This chapter will have different ways to show the formula in an Excel sheet. Usually, the Excel user can see the formula applied to an Excel sheet for a cell by clicking on that cell and its related formula in the formula bar.
We are discussing the topics in this chapter –
- Where to see Excel formula?
- Show formula of individual cell
- Show all formulas in a sheet
- Highlight the formula cell in a sheet
- Show formula using shortcut key
- Show all formula in a sheet from File tab
Where to see Excel formula?
Usually, the Excel user can see the formula inside the formula bar. For this, the users have to click the cell that contains the formula resultant data and then see the formula applied on it inside the formula bar.
This is the simplest way to see the Excel formula applied to a cell in an Excel sheet. But a single Excel sheet can have more than one formula and the user want to see all the formula list applied to a sheet. It will be very time taking to go through each cell to see the formula.
In such scenarios, we require another method that can show all the formulas applied to an Excel sheet. By default, result for the formula is visible inside the Excel sheet rather than the formula itself.
Show formula of individual cell
The Excel users can see the formula applied to an individual cell very easily inside the formula bar. It can simply determine if a cell having any formula. It is very easy to determine whether any formula is applied to a specific cell or not. If the formula is applied to that cell, then what is it?
Hence, the following are two simple steps to know the formula applied to a cell. For example, we have taken a random Excel sheet with some data inside it. We will check for it.
Step 1: First of all, go to that cell for which you want to know the formula if any applied. Then, look at the Formula bar for the formula. It shows the formula applied to that cell.
“If that particular selected cell containing any formula result, you will find its formula inside the formula bar. Otherwise, the value showing in cell will visible inside the formula bar.”
Step 2: We will now check for another cell having no formula applied to it. See, this time value is showing inside the formula bar instead of any formula.
In this way, one can show or know the formula applied to any specific cell. This is one of the simplest ways to show the formula. But the only disadvantage of this method is that the user can see only one formula at a time.
Show all formulas in a sheet
Sometimes, the users do not need to know the formula of a particular cell; they want to know all the formulas applied to a sheet. It will be difficult or complex to go through each cell and then see the formula. It will consume the time of users. So, what can we do to show all the formulas that resides in a sheet?
Excel enables a Show Formula feature using which the users can show all the formulas applied to that sheet. But at that time, the users can only see the formula but not their result. This is the only disadvantage of this method. Overall, it is a good way to show the formulas at once.
Let us see where to perform these steps to show the formula.
Step 1: We have taken a random Excel sheet with some data inside it. We will check for it.
Step 2: On that Excel sheet, switch to the Formulas tab and click the Show Formulas button to turn on the formula mode.
Step 3: Next, you will see that all the formulas are visible inside the Excel sheet.
You will notice that the formula results are hidden when the formula mode is on. So, you have to turn off this Show Formulas mode to again see the formula result and make the sheet as normal it was.
Highlight the formula cell in a sheet
You have learned the method to see the formula of a specific cell and all cells containing formulas in a sheet. Both methods help in different situations. It might be possible that the user only wants to know which cell contains the formula rather than the formula itself. What to do in such a situation?
The Excel users can highlight the cell that contains a formula. It helps when you do not want to know the actual formula but just all the cells that contain a formula. So, highlight them.
For this, follow some simple steps to highlight the formula cells in a sheet. It will be very helpful to you.
Step 1: Open an Excel sheet and click the Find & Select dropdown button inside the Editing group in the Home tab.
Step 2: Select Formulas in the dropdown list to basically target only formula cells.
Step 3: Once you select the Formulas in the previous steps, all the cells that contain even a simple formula will get highlighted/selected.
This is temporary. So, if you do not apply formatting to make it permanent, the cells will get unselected and you have to again follow the same steps to know the formula cells. So, you can apply the color formatting to keep highlighted the formula cells and make them permanently visible to all users of that sheet.
Step 4: Formula cells are already selected from the previous steps. So, just fill the cells with color to highlight the selected cell by clicking the Fill Color inside the Font group inside the Home tab.
Step 5: Here, cells highlighted by green color are forula cells. Means those cells which having some formula implemented on it.
Rather than the filling the color to the selected cell, you can only highlight the formula cell inside the selected by a different color other than Blank. Thus, the users can easily recognize the formula cell. For this, you can select Font Color instead of Fill Color.
Show formulas using shortcut key
There is a shortcut key to show all formulas available in an Excel sheet. This shortcut key is basically for the Show Formula mode that we performed in method 2. By using this shortcut, we can see the all formulas in a much faster way.
Just press the Ctrl+` to show the formulas. This will turn on the formula mode.
Press the Ctrl+` to turn off the formula mode again.
It is the fastest and easiest way to see all the formulas in an Excel sheet. We find the shortcut key is the fastest and easiest way to show the formula as you do not need to perform several steps. Just use the shortcut key to turn on and turn off the Show formula mode. You can save a lot of time of yours. They have to remember the key.
Why to show formula?
Sometimes, Excel users need to see formula instead of their values for some reason. The reason can be like – they want to remove a particular formula from the sheet or replace any formula. Besides this, there can be other reasons too.
Showing an Excel formula instead of its result helps to track the data used inside that formula. It can also be needed to fix the bugs of the Excel formula.
Thus, whenever the Excel users need to show the formula, they can use any of the methods according to their needs. As each method has a different implementation and result.
Show all formulas in a sheet from File tab
Apart from all the above ways that we have discussed to show the formula in Excel, we have one more way to show all formulas in an Excel sheet. Using which, you can show all the available formulas in an Excel sheet.
Although we will say to prefer the shortcut key or other method because you might feel it is a lengthy method. So, let us see how we can implement it
Follow the steps below:
Step 1: Open an Excel file and go to backstage by clicking on the File tab.
Step 2: Here, click More and then Options.
Step 3: In this panel, switch to the Advanced tab in sidebar and scroll down until you find Display options for this worksheet. Here, choose a sheet for the currently opened workbook.
We have chosen Sheet1.
Step 4: Now, mark the Show formula in cells instead of calculated results. In the end, click OK to close the panel.
Step 5: You will see that the formulas are showing instead of results inside the selected worksheet.
