Show Formula In Excel

How To Show Formula In Excel?

We can Show Formula in Excel cell range D2:D13 by choosing Formulas > Show Formulas.

The output is shown above i.e., the formulas are visible.

How To Show Formula in Excel?

The Show Formula in Excel displays the formulas in the active spreadsheet, enabling users to review how they work across the sheet.
We can select Formulas > Show Formulas or the keyboard shortcut Ctrl+` to show the formulas in our worksheet. [Note: The grave accent (`) symbol key is below the ESC key on the keyboard]
We can show the formulas in our worksheet, set the print area, and use the keyboard shortcut Ctrl+P to print the formulas instead of the values.
We can hide the formulas in Excel by protecting our worksheets with a password.

Methods To Show Formula In Excel

Let us see a few methods to view the formulas in Excel, for the above example, namely,

Using Show Formulas.
Double-clicking the Formula Cell.
Using the F2 Key.
Using an Apostrophe.

In the following table, the data is:

Column A contains the Months.
Column B contains the Monthly Earnings.
Column C contains the Monthly Expenses.
Finally, column D contains the calculated Monthly Savings.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

#1 – Using the Show Formula function

The procedure to Show Formula in Excel using the Show Formula function is:

First, choose cell range D2:D13 > select the “Formulas” tab > go to the “Formula Auditing” group > click the “Show Formulas” option.

When we click “Show Formulas”, it will Show Formula in Excel instead of value, as shown below.

#2 – Double-clicking the formula cell

The procedure to Show Formula in Excel using Double-clicking the Formula Cell is:

Select any cell having the formula, here, cell D5. Now, double-click on it, then the cell formula will appear as shown below:

We can view the formula only for a moment. Then, press the Esc key to get the value again.

#3- Using the F2 key

The procedure to Show Formula in Excel using the F2 Key is:

First, select any cell having the formula, here, cell D5. Now, press the F2 function key on the keyboard, then the cell formula will appear as shown below:

[Note: The F2 key shortcut may differ on different keyboards. In such cases, use the keys Fn+F2].

#4 – Using an apostrophe

Another way to Show Formulas in Excel is by adding an apostrophe before the ‘=’ sign of the formula. However, Excel will treat the formula with the apostrophe as a text value and will not evaluate it.

The procedure to Show Formula in Excel using an Apostrophe is:

1: Choose the formula cell range D2:D13, and press the keyboard shortcut Ctrl + H to open the “Find and Replace” window. Once the “Find and Replace” dialog box pops up, enter the = sign in the “Find what:” field and the ‘= sign in the “Replace with:” field, as shown below.

2: Click the “Replace All” button.

3: In the message confirming the number of values replaced, click “OK”.

We can now see the formulas in the cells D2:D13, as shown in the above image. Next, click the “Close” button to close the “Find and Replace” window.

Shortcut To Show Formula In Excel

We can quickly apply the keyboard excel shortcut to show formula in excel: “Ctrl+`”.

The grave accent (`) symbol key is below the ESC key on the keyboard.

The following table shows the items’ price and discount details and the final prices after discount in column D.

The procedure to Show Formula in Excel using the keyboard shortcut is:

Select cell range D2:D6, click anywhere in the worksheet, and press the keyboard shortcut, Ctrl + `. We will get the following output.

This shortcut allows us to Show Formula in Excel quickly. Later, we can press the Ctrl+` keys again to display the values instead of the formula.

How To Enable Show Formula In Workbook Option?

In a workbook with multiple worksheets with formulas, we can enable Show Formula in Excel.

The below image shows the workbook with the file name “Show Formulas_Worksheet”, which has three worksheets, and all have formulas.

The steps to Show Formula in Excel instead of value are:

1: Select the “File” tab.

2: Choose the “Options” from the list to open the Excel Options window.

3: In the “Excel Window”, click on the “Advanced” option on the left à on the right, scroll down to the “Display options for this workbook:” and to the “Display options for this worksheet:” groups.

Ensure the Excel workbook and the worksheet names are correct in the Display options for this workbook and Display options for this worksheet parts, as highlighted in the above image.

In this example, the Excel file name is Show Formulas_Worksheet, and the chosen worksheet is Show Formula_Enable.

4: Now, check/tick the “Show formulas in cells instead of their calculated results” checkbox in the “Display options for this worksheet:” group > and click OK.

The Show Formula in Excel is enabled for the specified worksheet, and we get the following result.

[Note: Irrespective of which Show Formula in Excel cell method we use, Excel will display formulas only in the active spreadsheet. To display the commands or formulas in other spreadsheets, we need to repeat the specific technique for each worksheet individually].

Print Formulas Using Show Formula Option

We can make Print Formulas Using Show Formula Option using Excel’s Print option.

The following table shows a set of numbers and their squares.

The steps to Show Formula in Excel and to take a print are:

Step 1: Choose cell range B2:D16 > select the “Formulas” tab > go to the “Formula Auditing” group > click the “Show Formulas” option.

Step 2: Once the formulas are visible, select the printable region and press the keyboard shortcut Alt+P+R+S, to fix the print area.

Step 3: Press the Excel keyboard shortcut Ctrl+P to print the data showing the formulas.

As shown in the print preview in the image above, the formulas are visible instead of the values.

Hide Formulas In Excel

We can use the Hide Formulas In Excel feature if we share the workbook with other users. By using this feature, we can protect the data from getting modified or deleted.

Consider the following table with employee details with the formulas in Column D.

The steps to Hide Formulas In Excel are:

1: Select the formula cells D2:D11.

2: Right-click to open the context menu and choose the “Format Cells” option.

3: The “Format Cells” window pops up. Select the “Protection” tab > check/tick the “Hidden” checkbox > click the “OK” button to close the “Format Cells” window.

4: Next, select the “Home” tab > go to the “Cells” group > click on the “Format” drop-down > choose the “Protect Sheet” option, as shown below.

5: The “Protect Sheet” window pops up. Enter a passcode in the “Password to unprotect sheet” field > click “OK”.

6: Once we click OK in the Protect Sheet, another “Confirm Password” dialog box pops up to re-confirm or re-enter the password. Click “OK” to close both windows.

Now, if any user chooses a cell from the formula cell range, D2:D11, they will not be able to view the formula in the cells or the Formula Bar. However, we can enter the password to view the formulas.

Thus, we can utilize the Show Formula in Excel option when we need to view the expressions in our worksheet while keeping them hidden and protected from others.

Important Things To Note

We can Show Formula in Excel by double-clicking on the formula cell or by pressing the F2 key on the formula cell. To view the values and hide the formulas, press the Esc key.
We can add an apostrophe ‘ sign before the formula. However, the formula is now considered a text, and it will not get evaluated.
To show the formulas in multiple sheets of a workbook, go to File à Options à And then, check the Show formulas in cells instead of their calculated results checkbox.
In any method to Show Formula in Excel, we can undo the option and get back the values by choosing Formulas à Show Formulas.

Frequently Asked Questions (FAQs)

Where is Show Formula in Excel?

The Show Formula in Excel feature is in the Formulas tab, and the option we need to click is Show Formulas.

FAQ

How to make F2 Show Formula in Excel?

We can use the F2 key to Show Formula in Excel as follows:

1. First, choose the cell where to view the formula.
2. Then, click the F2 key to display the formula.

Why does my formula show as text in Excel?

Our formula might show as text in Excel due to the following reasons:

1. The formula cell formatting could be Text, and we might try entering the expression in it. Excel will exhibit the formula but will not evaluate it. Set the cell formatting as General in the Home tab to resolve the issue.
2. We might have added an apostrophe before the ‘=‘ sign in the formula, converting it to text data. We can replace ‘’=’ with ‘=’ in the formula cells to ensure Excel evaluates them. We can then use the option Formulas à Show Formulas to Show Formula in Excel.

Why does my formula not show in Excel?

Our formula might not show in Excel if it is a protected worksheet.

To display the command, go to Home à Format à Unprotect Sheet. The Unprotect Sheet window will open, where we need to enter the password used to protect the sheet. And once we click OK, we can see the formulas.

Download Template

This article must help understand Show Formula in Excel, with its formula and examples. We can download the template here to use it instantly.

Show Formulas in Excel Download

Show Formula In Excel