Learn How To Hide Formulas In Excel

hide formulas

Table of Contents

How to Hide Formulas in Excel?

Sometimes, you may need to share Excel files with colleagues or clients, only to find that they’ve altered the original formulas. Perhaps you want to keep the formulas confidential and prevent others from viewing them.

Hiding the formulas in Excel is an efficient way to achieve this, ensuring that recipients can’t decipher which formulas you’ve employed or how the calculations are done.

How to Hide Formulas in Excel Cells

Before diving into the process, it’s essential to understand that there are two common ways to view a formula in an Excel cell: first, by editing the cell, and second, through the formula bar.

So, is there a method to block both these avenues and secure your formulas? Absolutely, there is.

Excel offers a straightforward solution to protect your formulas, and in this article, I’ll guide you through this simple yet powerful trick.

It happens sometimes you send Excel files to others and they made some changes into formulas. Or, sometimes you simply don’t want to show formula to others.

Step 1: Un-Protect Worksheet

At this point when you click on a cell, it shows you the entire formula in the formula bar.

  • First of all, you need to make sure that your worksheet in unprotected.
  • For this, go to review tab and in protect group.
  • If you find a “Unprotect Worksheet” button there, that means your worksheet is protected and you need to unprotect it.
2 unprotect-a-worksheet
  • Click on that button to un protect it.

If you have the “Protect Sheet” button there that means your worksheet is unprotected. In that case, skip the above steps and start from here.

Step 2: Activate Hide Formula Option

  • Now, select the cell or range of cell, or select all the non-contiguous cells where you want to hide formulas.
  • After that, right-click and select format cells option.
  • From format cells option, go to protection tab.
  • In protection tab, you have two check boxes to mark, first locked and second is hidden.
  • Mark on both of them and click OK.

These both options which you have checked solve your both of the problems which we have discussed above.

The locked option restricts editing of a cell and the hidden option hides a formula from the formula bar. Now the final step is to protect the worksheet.

Step 3: Protect Worksheet

  • Right click on worksheet tab and then click on “Protect Sheet”.
  • Here you need to enter a password. So enter a password (Twice).
  • And in the end, click OK.

Now all the cells which you had selected got formula hidden from the formula bar.

If you send this file to someone they will not be able to see the formula or edit it unless this worksheet is unprotected.

How Do I Find Hidden Formulas?

Once you protect a file using the above steps even you can’t see the formula until you unprotect the worksheet. But if you just want to find them from a protected worksheet where they are, you can use the “Go To Special” option.

  • Go to Home Tab -> Editing -> Find & Select -> Go to Special.
  • Select “Formulas” option and it’s all four sub option (Numbers, Text, Logicals, Errors) and click OK.
  • Once you click OK, it Excel will select all the cell from worksheet where you have formulas.

Hide a Formula without Protecting a Worksheet: I’m sure you have this thought in your mind how can I hide a formula without protecting a worksheet. I’m afraid there is no other method for this. You need to go this way. The only possible thing which I know is to create a macro code that runs at the opening of the workbook and hide the formula bar. But, again this is not a permanent solution. 

Conclusion

This option not only helps you to hide formula but also helps if you have any crucial calculation which you want to hide from others.

Until someone has the password, formulas are hidden and safe. I hope you this tip will help you to manage your worksheets.

Free Excel Templates

Organizational-charts

Organizational Chart

An organization chart is a diagram that displays a reporting …

baby-checklist

Baby Checklist

Want to make sure you have everything for your baby …

inventory and booking

Inventory and Booking Manager for Rental Business

Inventory and Booking Manager for Rental Business is a spreadsheet …

job applicant tracking spreadsheet

Job Applicant Tracking

Streamline Your Hiring Process with a Job Applicant Tracking Spreadsheet …

We Create Your Life Easy With Our Free Excel Templates & Spreadsheet Dashboards

Use free Excel Templates, Tools and Dashboards to create and draft a professional looking dashboards and computation sheet for your personal and business use. You can Explore Calendars, Activity Planners, Invoice Templates, Sales Forecast Sheets, Budget templates and various business information delivering Templates in Excel and Spreadsheet at XLSX Templates. 

These templates, dashboards and tools are useful in various occasions. You can also customize these templates as per your requirement. Modification of fields and data source helps in making the template more relevant.

Learn Microsoft Excel : Blogs and Articles

Learn about various tips and tricks in Microsoft Excel and Spreadsheet. Create best templates and dashboards using free tricks and tutorials in excel and spreadsheet. These tutorial posts are useful for everyone who wants to master the skills in excel and spreadsheet.