Hi All,
The best part of Microsoft Excel is the calculation. When we type any formula inside any cell, it showed into the formula bar, which helps us to understand the cell address of the formula. If it’s not enough, we can evaluate our formula by clicking on formula tab>> Formula auditing group>> Evaluate Formulas button. which is the step by step evaluation.
However, we are always worried that what would be, if anyone changes any cell reference or remove or add commas or change the cell address. We get the wrong answer or error message like (#Value, #N/A etc.). Therefore, this is our main concern, how can we prevent our sheet from these problems? Either we can hide the formula to shown in the formula bar or we can lock the formula so that no one can edit or delete the cell. Good news for excel user, Microsoft Excel makes it easy to hide and lock the formula. In this small blog-post, we will learn the process, step by step.
Let’s dive in the Excel ocean.
Password protection on formula-
When we put all our effort to build a master sheet with the help of formula and we share this sheet with other people. certainly, we don’t want anyone mess with excel formula. The most common way, we use to protect the entire sheet with a password. But, the end-user cannot edit any cell because through this way the entire sheet will be a lock but which we don’t want.
Let’s take an example and understand the process, how can we lock our formula with password-
As we can see in the above screenshot, we calculate amount with the multiplication or Qty (B2) and Price (C2). The formula is showing inside the formula bar. The following steps demonstrate the way to lock the formula with the password.
- Unlock all cell in the worksheet
- Select the whole sheet by clicking on the left upper corner of the sheet or press ctrl+A
2. Right-click on the database
3. Format cells or Press Ctrl+1
4. Click on Protection
5. Uncheck the Locked Option
6. Ok
7.
- Select the cell which we want to protect
Note: we can select multiple cells, holding with ctrl key and click with a mouse
- Right-click
- Format cells
- Protection
- Click on option “Locked”
- Ok
Note: Why we did this process first unlock the whole sheet because by default locked option is enabled in every new or existing sheet. It does not mean that we can not edit it.
Apply the Password-
- Go to the review tab
2. Protect sheet
3. Apply Password
Note: If we want to allow any other facilities to our end-user, we can click into the box as per our need. We already posted a piece of deep information about Protect sheet. Visit our website www.nurturetechacademy.in and read the full blog post.
4. Confirm password
5. Ok
After this, if we double click on the cell consisting formula, it gives us the below-mentioned message, which means this cell is password protect. Apart from these cells (D2: D4, we can do edit or delete in any cell of this data, which is not password protected.
Note: The best part of this process, Formula is a lock but it works. For example, if we make any changes like if we change qty or Price and press enter. The amount will be updated accordingly 😊
Hide Formula-
We are taking the same example as we took above. In this example, we will learn that cell consisting formula but it is not showing inside the formula bar.
Let’s start-
Unlock the cell-
- Select the entire sheet
- Right-click
- Format cell or press Ctrl+1
- Uncheck the option Locked
- Ok
Note: This is the same process, which we have just understand above. If confused, go and read it again.
Mark on Lock and Hidden
- Select the cells (D2: D4)
- Right-click
- Format cell or Press Ctrl+1
- Click on both option
- Locked
- Hidden
5. 
6. Review tab
7. Protect Sheet
8. Apply Password
9. Re-enter password
10. Ok
As we can see in the above screenshot, we put the cursor into the cell D2, however, nothing is displayed inside the formula bar. Neither anyone can see the formula, nor he/she can edit or delete it.
As we demonstrate above, if we make any changes qty or price, the amount will be updated automatically. It means the formula is working properly.
Hope, this blog-post help you to understand to Hide and lock the formula. Follow the process and do let us know if any query occurs. We would feel great to assist you.
Stay connected with nurture tech academy.
Happy Learning 😊