In this article today, I will show you an amazing way to quickly find all your formulas on an Excel sheet and hide them. The article is in response to a question that a colleague recently asked me, who needed to find all the cells on a sheet (large ... very large) of an Excel sheet, which contained a formula and hid details of a formula in a formula bar. They wanted to hide the formulas, so the worksheet could be distributed without fear that all their hard work on their formulas would be canceled with a click of the end user (clumsy) mouse.
I often hide formulas, and for this there are several reasons. Maybe I either don’t want other users to see the formulas at all, or I don’t want users to change them in any way, either maliciously or accidentally. So, here is a quick and effective way to identify cells containing formulas and hide them from the end user. There are several (simple) steps to it.
Step 1.
- Select all your data on the sheet sheet.
- Hit F5
- Click "Special"
- Choose Formulas
This selects all the cells on your sheet containing the formula. In the example that I use, I have formulas that work in several areas, and all of them are highlighted.
Step 2.
Now we need to hide the formulas. All Excel cells have two attributes: locked and hidden. Locked cells are simply locked and cannot be changed by the end user, while a hidden attribute means that the formulas contained in these cells will not appear in the formula bar at the top of the sheet.
However, we also need to remember that none of these attributes are valid until the worksheet is protected. So let us go to the detail of step 2. We already have our cells with the selected formulas -
- Select the “Home” tab - “Then a group of cells”
- Hit format - Protection - Protective sheet
- Make sure that “Protect Worksheet” and “Contents” are selected.
- Next, enter the password when prompted.
- Process complete
Now, if you try to click or select one of the cells containing the formula, it will not appear. You can continue and check it out. The formula bar will appear empty even in the Excel cell and in the xcel formula.
