• Formulas
  • Excel Tips & Tricks
  • Excel Charting
  • Tutorials
  • VBA
  • Book Store
  • More
    • Blog
    • Download Area
    • Excel Video Tutorials
Home » Formula Friday – Find And Hide All Formulas On An Excel Worksheet

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

October 26, 2016 by Barbara

Happy Formula Friday, Excellers. Today let’s look at a terrific way to quickly find all of our formulas on an Excel worksheet and hide them. This tutorial or blog post responds 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.

Excel Macro

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
learn how to find and hide Excel formulas

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

how to hide excel formulas

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……!

how to hide excel formula

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.

  • 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.
how to excel at excel youtube

Filed Under: Blog, Formula Friday, Formulas Tagged With: Blog, Excel, Excel Formulas, formula friday

Tags

Blog Cells Excel Excel Charting Excel Excel Tips Excel Formulas excel tip Excel Tip Category Excel Tips Excel Tips& Tricks excel tutorials Excel Videos Excel Video Tutorials formatting Formula formula friday Formulas macro Macro Mondays Macros Pivot Table Pivot Tables Power BI Power Query Tutorials vb VBA Worksheets

Recommended Excel Resources

Free Excel Dashboard Webinar

Copyright © 2022 · Enterprise Pro Theme on Genesis Framework · WordPress · Log in

Share
Additional Info

Want FREE Excel Tips?

Click on my FREE eBook, its my bonus for joining thousands of others who receive my 3 xFREE Excel tips every month in my Excel Newsletter. Join Us!.

Excel Jobs

Excel Charting

Self Expanding Charts
One Click Charts
Create Quick Dynamic Charts
Easy Combination Charts



  • Privacy Policy