How to Hide Formulas in Excel? 2 Different Approaches
(Note: This guide on how to hide formulas in Excel is suitable for all Excel versions including Office 365)
Excel deals with large amounts of data in which the values operate using a variety of functions and formulas. When you enter a formula in Excel, the formula that pertains to the cell always shows up on the formula bar.
This might be a great way to know what formula is used to obtain the particular value. But in some cases, the appearance of formulas might be a little irrelevant. So, what do you do when you don’t want the formula to appear in the formula bar in Excel?
In this article, I will show you how to hide formulas in Excel using 2 different approaches, without hiding the formula bar completely.
You’ll Learn:
Watch this Video on How to Hide Formulas in Excel
Related Reads:
How to Calculate Percentile in Excel? 3 Useful Formulas
The FORMULATEXT Excel Function – 2 Best Examples
How to Round Up in Excel(and Round Down)?
Why Hide Formulas in Excel?
Before we learn how to hide formulas in Excel, let us see how a formula shows up in Excel and why we need to hide them with an example.
When you enter a formula in any cell, they show up in the formula bar above the column. The value which populates the cell is a result of the formula or function consisting of variables, other cells, and operands.
Though the formula may be a great way to understand how we have obtained the value, in some cases, they do feel a bit irrelevant.
Consider that you have to share the Excel sheet with a group of unrelated people. In such cases, it is better to keep the formulas and functions hidden, thereby maintaining confidentiality. Also, this helps in protecting the file from being edited and the user will be able to only view the data.
In addition, consider the above example where the formula =RAND() is used to find the random value. When showing it as an example or while taking a screenshot, the presence of the formula cell might not be necessary.
In such cases, it would be better to hide only the formula without hiding the formula bar.
How to Hide Formulas in Excel?
Hide the Worksheet by Making the Cells Non-Editable
This is one method to hide the formula showing up in the formula bar. However, using this method to hide the formula bar also protects the sheet and makes the cell uneditable.
To hide the formulas and to make the cells uneditable, first, select the cells from which you don’t want the formula to show up. You can select a single cell, an adjacent group of cells, non-adjacent cells, and even the whole sheet.
After selecting the cells, right-click on the selected cells and click on Format Cells.
Another way to arrive at the Format Cells dialog box is by navigating to Home. Under the Number section, click on Number Format which appears as a small dropdown arrow.
This opens up a Format Cells dialog box. Go to the Protection tab and check the checkbox for Hidden. Click OK.
Once you have hidden the cells, navigate to Review. Under the Protect section, click on Protect Sheet.
Once you click on the Protect sheet, a dialog box appears. If the sheet is confidential to you, enter a password. You can unprotect the sheet only by using this password. In this case, let us leave the password textbox blank.
Make sure the checkbox for Protect worksheet and contents of locked cells is checked. The below two checkboxes for select locked cells and select unlocked cells are checked. This only lets the user click on them, but they are detained from doing any operation on the cells. Click OK.
This hides the formulas from showing up on the formula bar. When you click on any of the cells, you cannot see the formulas. In this case, when I previously clicked on the cells the formula =RAND() showed up in the formula bar. Now, after protecting the sheet the formulas are hidden for the selected cells.
In addition to hiding the formulas, this method also protects the sheet from being edited or altered.
Suggested Reads:
How to Use the PROPER Function in Excel? 3 Easy Examples
Excel DATEVALUE – A Step-by-Step Guide
How to Use SUMPRODUCT Function in Excel? 5 Easy Examples
Hide Only the Cells with Formulas and Make Other Cells Editable
In the above method, we saw how to hide the formulas from showing up in the formula bar when a cell is selected, making the selected cells uneditable.
However, in some cases, the user might have to input some data. For example, any data entry sheet to be circulated should be editable by other users. Then the method of protecting the entire worksheet by making them uneditable won’t be useful this time.
So, you can use the below-mentioned method to only hide the cells with formulas and keep the rest of the cells editable. This method is especially useful when there is a large amount of data and you cannot identify where the formulas are and which cells to hide or unhide.
Consider an example where the sheet contains the profit and losses made by salesmen and the clients will rate them. Here, some cells require editing when different clients rate a salesman.
To make this method work, let us first disable the lock property of all the cells in the sheet.
Select the entire sheet either by clicking on the gray arrow on the top-left corner of the column or by using the keyboard shortcut Ctrl + A.
Now to unlock the cell, navigate to Home. Under Number, click on Number Format which opens a dialog box.
Under the Protection tab, uncheck the checkbox for Locked. And, click OK.
This disables the lock property of the cells and makes them editable. Once the cells are unlocked, the cell containing the formulas throws up a warning message.
Now then, let us see how to protect only the cells which have formulas.
Again select all the cells in the sheet by using the gray triangle on the top-left of the sheet or by pressing Ctrl+A.
After you have selected the whole sheet, we only need to identify the cells which only have the formulas. To do that, navigate to Home. Click on the dropdown from Find & Select and click on Go To Special.
This opens up the Go To Special dialog box. In the dialog box, select Formulas and make sure all the checkboxes under the Formulas header are checked. Click OK. This step helps Excel identify the cells with formulas and selects them.
After the formulas are selected, let us now lock and hide the cells. You can either navigate to Home and click on Number and Number Format, or right-click on the selected cells and click on Format Cells. You can also use the keyboard shortcut key Ctrl+1.
In the Format Cells dialog box, under Protection, check the checkboxes for both Locked and Hidden. Click OK.
Now, you can see the warning symbols disappear on the cells with formulas.
After this, navigate to Review. Under the Protect section, click on Protect Sheet. Enter the password to protect the sheet and check the checkboxes for Protect worksheet and content for locked cells. Select the locked and unlocked cells, and click OK.
This locks only the cells with formulas, whereas the rest of the cells remain editable. If you click on any of the cells containing the formula, the formula does not appear on the formula bar, and also you cannot alter them. Whereas you can edit any of the other cells which do not contain a formula.
Also Read:
How to Fix the #Div/0 Error in Excel? 2 Easy Methods
How to Use ISTEXT in Excel? With 3 Different Methods
Short Date Format in Excel – 3 Different Methods
FAQs
Is there any way to hide without protecting the sheet?
No, there is no way to hide formulas in Excel without protecting the sheet. However, you can use VBA codes, but they can sometimes be inconsistent and lead to other errors.
What to do when you lose the password for a protected file?
There is no way in Excel to get back the password and unprotect the sheet using conventional methods. However, there are a lot of hacks and workarounds on the internet which might help you unprotect the sheet.
How can you remove only formulas and keep values in Excel?
You can copy the cells which contain the values, then paste them onto another sheet (or in a different place on the same sheet) by pasting them as Values.
Closing Thoughts
In this article, we saw how to hide formulas in Excel without hiding the formula bar. You can either hide the formulas by making the whole sheet protected and non-editable, or you can just pinpoint and protect the cells containing the formulas by keeping the other cells editable. Both are useful in their ways, you can use the method which suits your purpose better.
Please visit our free resources center to know more about the date and time functions.
We have high-quality Excel guides and you will be really excited to know how handy and useful these functions are.
Simon Sez IT has been teaching Excel for over ten years. For a low monthly fee, you can access 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!