Formula Friday – Find And Hide All Formulas On An Excel Worksheet

Happy Formula Friday, Excellers. Formulas are a huge part of Excel, and they can be very helpful in organizing and analyzing data. However, sometimes you may need to hide them from view so that your worksheet is easier to read. In this blog post, we will show you how to find and hide all formulas on an Excel worksheet. Stay tuned!

This tutorial is a response to a question asked by a colleague recently. How to hide all Excel formulas in an Excel workbook?. The workbook due to be shared contained a lot of formulas that the end-users did not need to view. So, let’s hide any Excel formulas found in the workbook.

Why Hide Excel Formulas?.

I often hide Excel formulas. There are a few reasons behind this.

1. I do not want other users to see the formulas.
2. Protection of the Excel formulas. I do not want users to change them in any way, maliciously or by accident.

How To Hide Excel Formulas.

So,  here is a quick and efficient way to identify the cells with formulas and hide them.  There are a couple of (easy) steps to it.

Step 1.

• Select all of your data on your worksheet.
• Hit F5
• Click Special
• Select Formulas

Now all cells with formulas are selected. I have formulas in several areas in the example I am using, which are all highlighted.

Step 2.

Now to hide the formulas. All Excel cells have two attributes. Locked and hidden. Locked cells are just that, locked and cannot be changed by an end-user, whereas the hidden attribute means that formulas contained in those cells will not be displayed in the formula bar at the top of the worksheet.

We do however need to also remember that none of these attributes are in force until a worksheet is protected.   So, onto to the detail of Step 2.  We already have our cells with formulas selected –

• Home Tab –  Cells Group
• Format – Protection – Protect Sheet
• Ensure that Protect Worksheet and Contents are selected
• Enter a Password when promtped
• Job Done!

Now, if we click or select one of the cells that contained a formula, see if you can see your Excel formulas now?…..nope…thought not……!

In this blog post, we have discussed how to find and hide Excel formulas. The steps are quite simple if you follow the article’s instructions. We hope that this information was useful for you!

So, how do YOU prevent users seeing or accessing your Excel formulas? Leave a comment below.

More Excel Tips.

• Formula Friday – Shhhhh…Want To Hide Your Excel Formulas?????….
• Hide All Excel Worksheets Except The Active One.
• Macro Mondays – An Easy Excel Macro To Hide All But Your Active Worksheet
• Protect And Hide Your Excel VBA Macros – Macro Mondays
• A Macro To List All Very Hidden Worksheets in Excel.