Lock Cells to avoid editing, Hide Formulas
Sometimes you create amazing projects, dashboards however people can make mistakes and edit the calculations, formats what you have made. Still you can protect the sheets, workbooks and prevent users to make any editing via “Protect Workbook” Option
However you need an additional feature along with the “Protect Sheet” to allow user to made editing, formatting to specific cells or hide formulas, disallow the user to select locked Ranges
So here let’s understand how we can do this:
1. LOCK SPECIFIC RANGE/UNLOCK SPECIFIC RANGE FOR EDITING IN PROTECTED WORKSHEETS
- Entire spreadsheet cells are always in Locked mode by default. So we should unlock the specific cells/range which we want to keep on editing mode
- Select the Cells, Range which you want to allow for editing. So here I select “B2:B21” range which I will be unlocking
- Right Click on the Selected Ranges and click on Format Cells. Below window should appear:
- Below window will appear. Then Go to Protect Tab and uncheck the box “Locked” and click “Ok”. It will unlock the cells. Then you may Protect Sheets
This will unlock the selected Ranges for editing and user may get restricted to these ranges. It will help you to protect your excel formatting, formulas, headers etc. and everybody can use only specified ranges which you may colour code.
Note: While protecting worksheet, if you uncheck the box “Locked Cells” as below image. It will restrict the user to navigate only on “Unlocked Ranges”
So below gif Image may help you to understand entire process of “Unlocking Specific Range”
2 - Hide Formulas In Protected Worksheet
I am sure that there are some projects where we did lots of research to create complex formulas, calculation and we do not want to show those amazing tricks which we created by ourselves but people can read your formulas in “Formula Bar” even if you have protected the sheet as you can see below image (though this is not a complex one ):
Though you can hide Formula Bar but still you can hide formulas without hiding the Formula Bar:
- Select the Cells, Range which is formula driven cells/range. So here I select “B2:B21” range:
So very simple step, you should follow
- Right Click on the Selected Ranges and click on Format Cells. Below window should appear (You can see that formula bar is displaying the formula):
- Below window will appear. Then Go to Protect Tab and Check the box “Hidden” and click “Ok”. It will hide the formulas
Now when you protect the worksheet, it will hide all the formulas where you have ticked this box.
So below gif Image may help you to understand entire process of “Hide Formulas”
Hope you like but still you have questions or any feedback. Please comment below and share with your friends
Recommended Articles
Secrets of Excel Data Visualization: Beginners to Advanced Course
Here is another best rated Excel Charts and Graph Course from ExcelSirJi. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.
This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.
So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.
Excel VBA Course : Beginners to Advanced
We are offering Excel VBA Course for Beginners to Experts at discounted prices. The courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion
This course is going to help you to excel your skills in Excel VBA with our real time case studies.
Lets get connected and start learning now. Click here to Enroll.
Use Automated Excel Utility Tools to increase productivity
- Quickly Move or Delete Files or Folder >> Click here
- Handle Duplicate Files with Automated Utlity >> Click here
- Generate Dummy Data For Testing >> Click here
- Excel Files and Sheets Consolidator >> Click here
- Manage Outlook Emails efficiently >> CLick here
- Send Bulk Emails wiht our Excel Automation >> Click here
Hope you are enjoying learning Excel with us, if you want any support related to this article, please do comment else you can ask questions in Excel Community
