Hide And Protect Formulas

BY STANLEY ZAROWIN

Q. When I circulate my statistical Excel worksheet to users outside my company, I need to protect the underlying confidential formulas but also keep the worksheet easy for users to enter their data. Any ideas?

A. Try using Excel’s Protection ; it can hide underlying formulas and protect them from any attempted change.

Here’s how it works: Before you enable Protection be sure to format the affected cells (right-clicking opens the menu that includes Format Cells ) so they display their results—not the underlying formula—in the format of your choosing. Then, while still in Format Cells , click on the Protection tab, check Locked and Hidden , and click on OK (see screenshot below).

Now go to the Excel toolbar and click on Tools , Protection , Protect Sheet (see screenshot below). Make sure to place a check next to Protect worksheet and contents of locked cells . The defaults in the menu under Allow all users of this worksheet to: are Select locked cells and Select unlocked cells . Check any other options you want and enter a password, which appears as dots.

Be aware that if you fail to enter a password and click on OK , the cell is still hidden but anyone can reverse the protection by going through the above routine and clicking on Unprotect Sheet .

SPONSORED REPORT

Cybersecurity threats proliferating for midsize and smaller businesses

This report details how SMBs can properly protect private information from breaches, design and implement a cybersecurity policy, and create safeguards for training and education.

QUIZ

News quiz: Senate health care bill in the spotlight

Reports related to the Republican bill to repeal many provisions of the PPACA, other tax issues, and the giant AICPA ENGAGE Conference offered a diverse reading list for June. See how much you know about recent news with this short quiz.