Lock Cells to avoid editing, Hide Formulas

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

Excel VBA Master Tool Kit

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
Lock Cells to avoid editing, Hide Formulas
  • Right Click on the Selected Ranges and click on Format Cells. Below window should appear:
Lock Cells to avoid editing, Hide Formulas
  • 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
Lock Cells to avoid editing, Hide Formulas

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”

Lock Cells to avoid editing, Hide Formulas

So below gif Image may help you to understand entire process of “Unlocking Specific Range”

Lock Cells to avoid editing, Hide Formulas

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 ):
Lock Cells to avoid editing, Hide Formulas

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

Lock Cells to avoid editing, Hide Formulas
  • 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):
Lock Cells to avoid editing, Hide Formulas
  • Below window will appear. Then Go to Protect Tab and Check the box “Hidden” and click “Ok”. It will hide the formulas
Lock Cells to avoid editing, Hide Formulas

Now when you protect the worksheet, it will hide all the formulas where you have ticked this box.

Lock Cells to avoid editing, Hide Formulas

So below gif Image may help you to understand entire process of “Hide Formulas”

Lock Cells to avoid editing, Hide Formulas

Hope you like but still you have questions or any feedback. Please comment below and share with your friends

Recommended Articles

Similar Posts

Why use dollar sign ($) in Excel formulas
| |

Why use dollar sign ($) in Excel formulas – absolute and relative cell references

BySumit Shrimal

When creating a formula in Excel, many people get confused by the $ symbol in cell references. But it’s actually very simple. The dollar sign in an Excel cell reference tells Excel whether to keep the reference the same or allow it to change when you copy the formula to other cells. This guide will explain how it works and why it’s helpful.
Understanding Excel cell references is really important. Knowing the difference between absolute, relative, and mixed references will help you get the most out of Excel formulas and functions.

How to Color alternate row in excel
| | |

How to color alternate rows in Excel: highlight every other row

BySumit Shrimal

This tutorial shows you how to change the row colors in Excel to automatically highlight every other row or every nth row or column in your worksheets. You will also learn how to use Excel’s banded rows and columns and find some helpful formulas to shade rows based on value changes.
Using alternating colors for rows in Excel is a common way to make data easier to read. While it’s simple to manually highlight rows in a small table, it can be very time-consuming in larger tables. A better approach is to automatically alternate the colors of rows or columns, and this article will show you how to do it quickly

Correlation in excel
|

Correlation in Excel: coefficient, matrix and graph

BySumit Shrimal

This tutorial teaches the basics of correlation in Excel. It shows how to find a correlation coefficient, make a correlation matrix, and understand the results.
Correlation is one of the easiest calculations you can do in Excel. Even though it’s easy, it helps a lot in understanding how two or more things are related. Excel has all the tools you need to do a correlation analysis—you just need to know how to use them

Leave a Reply

Your email address will not be published. Required fields are marked *

Excel VBA Master Tool Kit