Show and hide All formulas On An Excel Worksheet

Dear Friday Excellers, let’s analyze a terrific method to promptly find all of our formulas on an Excel worksheet and conceal them. Todays’s post is in response to a question i was asked recently, as a colleague crucial to locate all the cells on a (large…very big) Excel worksheet and hide them, so the worksheet may well be disbursed with out fear of all of their challenging work on their formulas being undone, with the click on of an end clients (clumsy) mouse.

I do myelf regularly conceal formulation, there are just a few reasons- it can be that I either don't want different users to look the formulas, or I don't want users to alternate them in any way either maliciously or accidentally. So, right here is a brief and productive solution to determine the cells with formulation and conceal them. There are a couple of (effortless) steps to it.

Step 1.

select your entire data in your worksheet.

Hit F5

click special

opt for formulas

This selects all of the cells in your worksheet that comprise a formulation. within the example i'm the usage of, I even have formulation running in a couple of areas, and they're all highlighted.

Step 2.

We now need to disguise the formulas. All Excel cells have two attributes, that of locked and hidden. Locked cells are just that, locked and can't be modified with the aid of an end person, whereas the hidden attribute potential that formulation contained in these cells are usually not displayed within the method bar on the desirable of the worksheet.

We do although should also remember that none of these attributes are in force except a worksheet is covered. So, onto to the element of Step 2. We already have our cells with formulation selected –

home Tab – Cells neighborhood

layout – coverage – offer protection to Sheet

ensure that offer protection to Worksheet and Contents are chosen

Enter a Password when promtped

Job completed!

Now, if we click or choose probably the most cells that contained a method, see if you can see your Excel formulation now?…..nope…idea now not……!

How do YOU prevent users seeing or gaining access to your Excel formulation? pls share below.

Books Recommended

Microsoft Excel 2013 Functions & Formulas Quick Reference Card (4-page Cheat Sheet focusing on examples and context for intermediate-to-advanced functions and formulas- Laminated Guide)

The Step-By-Step Guide To The VLOOKUP formula in Microsoft Excel (The Microsoft Excel Step-By-Step Training Guide Series) (Volume 3)

Excel 2016 Formulas (Mr. Spreadsheet's Bookshelf)