Mrinmoy Roy
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.
❸ Go to the Protection tab and mark a tick on the Hidden option and hit 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.
Thus, to lock the worksheet,
❹ Go to the Review tab.
❺ From the Protect group, choose Protect Sheet.
The Protect Sheet dialog box will appear.
❻ Insert a password there and click OK.
❼ 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.
Similar Readings
- How to Show Value Instead of Formula in Excel (7 Methods)
- [Fixed!] Formula Result Showing 0 in Excel (3 Solutions)
- How to Show Formulas When Printing in Excel
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.
❺ 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 (=).
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.
