How to Stop Showing Formulas in Excel (7 Quick Fixes)

Basically, formulas are an essential component of Excel, as they allow users to perform complex calculations and automate tasks. But, while sharing your datasheet with others, you may not want to show the formulas to other users. So, in this article, we will show 7 different scenarios and their fixes on how to stop showing formulas in Excel.
By default, Excel displays the values of the formulas. If you click on a cell containing formulas, you can see the underlying formula in the formula bar. However, it is frustrating when these formulas are displayed in the cells instead of the results. This is a common problem that many Excel users encounter, but fortunately, it is easy to fix.
Also, this will make your spreadsheets look more professional and easier to read, and save you time and effort when working with data. So, let’s dive in and learn.

How to Stop Showing Formulas in Excel: 7 Quick Fixes

Let’s assume we have a Sales Report of XYZ Gadgets in our hands. This dataset includes Customer and their corresponding P. Code, Unit Price, Qty, and Revenue under columns B, C, D, E, and F respectively.

By default, if you click on a cell having formulas, you can see the formula in the Formula Bar. For example, here we clicked on cell F5 and the following formula is showing in the Formula Bar.

1. Use Format Cells Dialog Box and Protect Sheet to Stop Showing Formulas

In this method, we’ll stop showing the formula in the Formula Bar. That means if we click on a certain cell containing a formula, the Formula Bar will remain empty.

• First, select all cells containing the formula (F5:F14).
• Then, navigate to the Home tab and click on the small arrow icon on the right side of the Number group of commands.

It’ll open the Format Cells dialog box. In the Protection tab,

• Check the box of the Hidden option and click OK.

Now you need to lock your worksheet to protect it from others. If you don’t lock your worksheet, Excel won’t stop showing the formulas.

• At this time, go to the Review tab >> Protect group dropdown >> Protect Sheet option.

The Protect Sheet dialog box will appear.

• Here, insert a password and click OK.

• In the Confirm Password dialog box, enter the same password again and click OK.

Now you will notice that Excel has stopped showing the formulas in the Formula Bar.

2. Stop Showing Formulas by Checking If Show Formulas Option Is Enabled

Here, we can see that all cells are showing formulas instead of values in the F5:F14 range. Why is it happening? Let’s find the answer.

• Firstly, advance to the Formulas tab, and in the Formula Auditing group, you can see the Show Formulas option is highlighted. That’s why Excel shows formulas rather than showing values.
• Then click on this option again to disable it.

Excel will stop showing the formula. You will get to see values in the same range now.

3. Verify Number Format of Cells Containing Formulas to Stop Showing

Here, the cells in the Revenue column are showing formulas in those cells. If we take a look at the Number Format bar, we can see that cell F5 is in the Text format. That’s true also for the remaining cells in this column. That’s the main reason for showing the formula in these cells.

We can solve this problem by following some easy steps.

• Select cells in the F5:F14 range.
• Then, click on the drop-down arrow beside the Number Format bar on the Number group of commands under the Home tab.
• Next, select the General format.

However, there is no visible change showing in these cells. Yet they are showing formulas inside them.

A bit of work that still needs attention.

• Click on cell F5 and press the F2 key to open the editing mode. Then, press ENTER.

Now, it’s showing value inside the cell.

• Currently, use the Fill Handle feature to copy the formula to the remaining cell and get results.

4. Precede Formulas with an Equal Sign to Stop Showing It

If we forget to put an equal sign (=) before the formula, Excel considers the formula as plain text and shows it as a value in the cell. Exactly this happened in the following image.

• Just click on the cell (F5) containing the formula and give an equal sign (=) before it and press the ENTER key. The formula then becomes the following.

Certainly, the system appears to be functioning properly. It’s showing value again.

• Replicate the process on the other cells as previously demonstrated.

5. Formulas Stop Showing by Removing Space Before Equal Sign

In this case, it seems there is no error or typo in the formula. Then, why does it look like that? Normally, the formula should not be shown in the cell.

But if you notice carefully, you can understand there is a single space before the equal sign of the formula. That’s why Excel is showing formulas in these cells.

• Just remove the extra preceding space and press ENTER.

The solution is operational and shows value in cell F5.

• Follow the same process on the other cells as demonstrated previously.

6. Eliminate Opening Quotation Mark in Formulas

Here, there is an apostrophe before the equal sign (=) for the formula in cell F5. It’s the reason why Excel is showing formulas in worksheet cells.

• After removing the apostrophe from the formula, it’ll stop showing formulas again.

7. Apply VBA Code to Stop Showing Formulas in Excel

If you click on a cell having formulas, you can see the formula in the formula bar. Here, we’ll apply VBA code to stop showing formulas in Excel.

• Move to the Developer tab, then click on the Visual Basic button in the Code group.

It launches the Microsoft Visual Basic for Applications window.

• Now, click the Insert tab and choose Module from the list. We get a small Module window to insert our VBA code.

• Here’s the working code to do the task. Paste this code into the module.

Sub StopShowingFormulas()

With ActiveSheet
.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Cells.SpecialCells(xlCellTypeFormulas).FormulaHidden = True
.Protect AllowDeletingRows:=True
End With

End Sub

• After completing the code script, click the green-colored play button to Run the code. You can press F5 on the keyboard to do the same task.

We have successfully resolved the problem. The formulas aren’t showing anymore.

How to Hide Formulas in Excel Without Protecting Sheet

Previous VBA code requires the worksheet to be protected while executing the macro to run the code successfully. But in this section, you will get to learn how to hide formulas even in the unprotected sheet.
Here, you can see that the sheet is in default mode and showing the formula now.

• First, right-click on the sheet name and select View Code from the context menu.

It’ll open a code module applicable to that sheet only.

• Then, copy the following code and paste it into the code module of the specified worksheet.

Dim iDictionary As New Dictionary

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim iCell As Range
Dim iRange As Range

Set iRange = Range("F5:F14")

If iDictionary.Count <> iRange.Count Then
For Each iCell In iRange
iDictionary.Add iCell.Address, iCell.FormulaR1C1
Next
End If

If (Target.Count = 1) And (Not Application.Intersect(iRange, Target) Is Nothing) And (Target.HasFormula) Then
With Target
.Value = .Value
End With
Else
For Each iCell In iRange
iCell.Formula = iDictionary.Item(iCell.Address)
Next
End If

End Sub

• Don’t run this code, Save it.

• Next, click on Tools >> References… from the code window tab.

• From the appeared References – VBAProject pop-up window, check Microsoft Scripting Runtime. Then, click OK.

• After that, go back to the worksheet of interest and click on the cells to check whether the formulas behind them are really hidden now or not.

You can watch the following short video for a better understanding.

How to Hide Formulas but Allow Inputs in Excel

You can protect a sheet or workbook to make only specific cells editable by users. This will enable you to protect the formulas from serious errors or data loss caused by any accidental and unexpected changes by the users.

• Go to the Home tab >> Find & Select dropdown >> Formulas option.

This action will select cells with formula only.

• Just press CTRL + 1 to open the Format Cells dialog box.

• First, jump to the Protection tab.
• Next, check the Locked and Hidden boxes. After that, hit the OK button.

• Now select the cells which you want to keep editable for the users i.e. you want to allow users to input data in those cells (D5:E14). Next, press CTRL + 1 again.

• Again, go to the Protection tab. This time keep both the Locked and Hidden boxes unchecked and hit the OK button.

• Protect the sheet like we did in Method 1.
• Now if you select the cells containing formulas, you won’t see any formula in the formula box.

• Now try to edit those cells containing formulas. Then you will see the following error.

But you can easily input values in the range D5:E14. And this will change the results in cells F5:F14.

• Change the value of cell E5 from 3 to 5 and press ENTER.

Eventually, the result is before our eyes.

How to Show Value Instead of Formulas in Excel

We will use the Power Query feature of Excel to view values instead of formulas.

• First, click on any cell inside the data range. In this case, we selected cell B4.
• Then, proceed to the Data tab >> From Table/Range on the Get & Transform Data group.

Instantly, the Create Table dialog box appears. Excel detects the entire data range automatically.

• Just click OK.

Immediately, it will take us to the Power Query Editor.

• Here, click on Home >> Close & Load >> Close & Load To….

• In the Import Data dialog box, choose the Existing worksheet to put the data. And, select cell B16 as the first cell of the output range. Then, click OK.

See the magic. It shows the value in the Formula Bar yet you click on cell F17.

Things to Remember

• Make sure to protect the worksheet if you want to stop showing formulas.
• You could use CTRL + ` (grave accent) keyboard shortcut to show formulas or vice versa.
• Also, you can hide the Formula Bar from the View tab.
• Furthermore, if you want to show the formula in another cell, you can use the FORMULATEXT function.
• Press ALT + F11 to open the VBA editor. To open the Macro dialog box, press the ALT + F8 keys altogether.

Frequently Asked Questions

1. How do I permanently stop showing formulas in Excel?

To do this, go to File >> Options >> Advanced >> Display options for this workbook >> uncheck the Display formulas in cells instead of their calculated results option, and then click OK. Save the workbook, and Excel will remember this setting the next time you open the file.

2. How do I prevent others from seeing my formulas in Excel?

To do this, go to File >> Save As >> Tools >> General Options >> enter a password under Password to modify >> OK. You can also use the Protect Sheet or Protect Workbook options under the Review tab on the Ribbon to limit user access to your formulas.

3. How can I show formulas in Excel without the cell reference?

To show formulas in Excel without the cell reference, you can use the Evaluate Formula feature. Select the cell containing the formula you want to evaluate, and then go to the Formulas tab >> Evaluate Formula. This will display the formula without the cell references

Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.

Conclusion

To sum up, we have discussed 7 scenarios where Excel is showing formulas instead of values and fixes on how to stop showing formulas in Excel. Definitely, by following this article, you could find out how to stop showing formulas in Excel. Just make sure to use the method which suits you best for you.
You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP. Happy Excelling.

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 Formula in Cells Instead of Value in Excel
• 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

<< Go Back To Show Excel Formulas | Excel Formulas | Learn Excel