How to Stop Showing Formulas in Excel (2 Methods)

In Microsoft Excel worksheets, by default values of the formulas are displayed. If you click on a cell containing formulas, you can see the underlying formula in the formula bar. While sharing your datasheet with others, you may not want to show the formulas to other users. In this regard, you will learn 2 different methods to stop showing formulas in Excel from this article.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


2 Methods to Stop Showing Formulas in Excel

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

By default, if you click on a cell having formulas, you can see the formula in the formula bar.

If you want to stop showing formulas in the formula bar, then follow the steps below:

❶ First of all, select all the cells having formulas.

❷ Then press CTRL + 1 to get the Format Cells dialog box.

Use Format Cells Dialog Box and Protect Sheet to Stop Showing Formulas in Excel

❸ Go to the Protection tab and mark a tick on the Hidden option and hit OK.

Format Cells : Stop Showing Formulas in Excel

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.

Thus, to lock the worksheet,

❹ Go to the Review tab.

❺ From the Protect group, choose Protect Sheet.

 Protect Sheet to Stop Showing Formulas in Excel

The Protect Sheet dialog box will appear.

❻ Insert a password there and click OK.

Protect sheet to stop showing formula in Excel

❼ Insert the password again in the Confirm Password dialog box and click OK.

Now you will notice that Excel has stopped showing the formulas in the formula bar.

stop showing formulas in excel


Similar Readings


2. Use 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.

If you are looking for VBA codes, to stop showing formulas in the formula bar in Excel, then follow the steps below:

❶ First of all press ALT + F11 to open the VBA editor.

❷ Then go to Insert > Module to create a new module.

❸ Copy the following VBA code.

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

❹ Paste and save the code in the VBA editor.

Use VBA Code to Stop Showing Formulas in Excel

❺ Now go back to your Excel worksheet and press the ALT + F8 keys.

This will open the Macro dialog box.

❻ Click on the Run button to run the VBA code.

After running the VBA code, Excel will stop showing formulas in the formula bar in Excel.

You can check it by clicking on a cell having a formula.

Read More: How to Show All Formulas in Excel (4 Easy & Quick Methods)


Stop Showing Formulas Instead of Results in Excel

While inserting a formula into a cell, if you type a single quote (‘) or space before the formula, the formula won’t show the results. Or, if you enter a formula in a text formatted cell, you won’t get the result, rather just the formula will be shown.

To avoid this problem, make sure your formulas always start with an equal sign (=).

Stop Showing Formulas Instead of Results in Excel

If a cell is already formatted as Text, then the formula inside it won’t work.

Always make sure that the cell format is set to General before inserting a formula.

If the cells are already in text format, first change the format to any number format (Number, Currency, Accounting, etc.). Then delete the single quote before the equal sign.

Read More: Excel Showing Formula Instead of Result (8 Reasons)


Things to Remember

  • Press ALT + F11 to open the VBA editor.
  • To open the Macro dialog box, press the ALT + F8.

Conclusion

To sum up, we have discussed 2 formulas to stop showing formulas in Excel. 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. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo