How to Lock Formula Cells & Hide Formulas in Excel

The indigence for locking cells comes with the ease of editing in Excel. Think of a calculation that has multiple columns referenced within the formula ( e.g. net wage using taxes, allowances, and bonus calculations ). If by chance one rate is changed, it affects the whole string of calculations and consequently the final examination measure ( not to mention employee esprit de corps ). such things can easily make your spreadsheets a chilling thing. however, thankfully we have the option of locking the cells containing formulas to avoid designed and unintentional tamper and edit ; that ’ s what we will address today. Locked cells can not be edited, but it does not restrict us from seeing the rule in the Formula Bar. While this is n’t a problem in most cases, but if you do n’t want the formulas to be visible, you need to make them hidden. This tutorial will take you through locking convention cells and hiding formulas in Excel. Lock & Hide Formulas In Excel

Notes on Cell Locking and Hiding

This may come as a storm but all cells are locked by default in Excel. The interlock cells however are only truly locked from editing once the worksheet is protected. Hidden cells besides need sheet protection to hide the contents of the cells. This means that both cell locking and hiding need sheet protection to work. To lock sealed cells on a worksheet, all cells must be unlocked first. then the specific cells can be selected and locked. adjacent, the sheet has to be protected to lock the cells from being changed .

Lock Formula Cells In Excel

Locking recipe cells means that we will be locking some chosen cells. For that, we need to change the default determine of all the cells in the sheet ( which is locked ) to unlock. then we will select the formula cells and lock them. After locking the cells, the worksheet needs to be protected to lock the cells from being edited. There is a bang-up trick to automatically select all the recipe cells in a sheet alternatively of selecting them manually. That bang-up trick is to use Go To Special. We will use this sport to select the formula cells in the sheet to lock them. Below are the steps to unlock all the cells, lock the rule cells and protect the worksheet. Let ’ s break them down, one by one .

Step 1 – Unlock all the Cells in the Worksheet

  • Select all the cells in the worksheet (Ctrl + A).
  • Launch the Format Cells dialog box by right-clicking any cell and selecting Format Cells from the right-click context menu (or using the keyboard shortcut Ctrl + 1).

Lock-And-Hide-Formula-Cells-In-Excel-01

  • In the dialog box, go to the Protection The Locked checkbox will be ticked. Uncheck the Locked checkbox.

Lock-And-Hide-Formula-Cells-In-Excel-02

  • Click OK.

now all the cells on the sail are unlocked. This will not have any ocular impact on the sheet unless the datum contains formulas. In that case, you will see a green arrow in the top left field corner of all formula cells. Selecting any of these cells will show the Trace Error button which will report exposed formulas due to unlock cells. Lock-And-Hide-Formula-Cells-In-Excel-03 Do n’t worry locking these cells will fix this error and that is our future step .

Step 2 – Lock the Cells in the Worksheet Containing the Formulas

nowadays we will lock only the formula cells in the worksheet so that after the sheet is protected, alone the formula cells can not be changed. rather of selecting the rule cells manually, we will use the Go To Special have. Below are the steps to lock the rule cells in the worksheet with Go To Special :

  • Select all the cells in the worksheet (Ctrl + A).
  • In the Home tab, from the Editing section, click on the Find & Select From the drop-down list, select Go To Special to open the Go To Special dialog box.

Lock-And-Hide-Formula-Cells-In-Excel-04

  • Select the Formulas radio button and make sure all the checkboxes under Formulas are checked. Then click OK.

Lock-And-Hide-Formula-Cells-In-Excel-05 As you can see below, all cells containing formulas on the worksheet will be selected. Lock-And-Hide-Formula-Cells-In-Excel-06 The next footstep is to lock the selected recipe cells .

  • Launch the Format Cells dialog box by clicking on the dialog launcher (small arrow on the bottom right) in the Alignment section (or use the keyboard shortcut Ctrl + 1).
  • In the Protection tab, check the Locked Then click OK.

Lock-And-Hide-Formula-Cells-In-Excel-07 nowadays that the rule cells are locked, the recipe errors have disappeared. Lock-And-Hide-Formula-Cells-In-Excel-08 To wholly restrict the edit of lock in cells, the worksheet needs to be protected, which is the adjacent dance step .

Step 3 – Protect the Worksheet

To activate the properties of a locked cell, you have to protect the worksheet and the simpleton steps for doing that are as follows :

  • Go to the Review From the Protect section, select the Protect Sheet command button.

Lock-And-Hide-Formula-Cells-In-Excel-09

  • Confirm that the Protect worksheet and contents of locked cells checkbox is checked.

Lock-And-Hide-Formula-Cells-In-Excel-10

  • In this Protect Sheet dialog window, you have the option of adding a password to unprotect the sheet. It can be helpful to keep a password to allow some users to edit the locked cells by sharing the password with them.
  • Other controls of the worksheet are in Allow all users of this worksheet to: field. We are going with the default settings (top 2 checkboxes checked).
  • After making the changes as you like, click OK.

Following these steps have protected the worksheet and locked the convention cells, disallowing any editing. Any cell other than the rule cells can be edited. Should you try to edit a cellular telephone containing a formula, a prompt will pop up notifying that the cell belongs to a protected sheet. Lock-And-Hide-Formula-Cells-In-Excel-11 If a password had been set up in the Protect Sheet window, the exploiter will be asked to enter the password while unprotecting the sheet from the Unprotect Sheet command button in the Review tab. With no password, the sheet can be unprotected by clicking the Unprotect Sheet command push button straight away. Lock-And-Hide-Formula-Cells-In-Excel-12 Recommended read : All About Locking & Protecting Worksheets

Hide Formulas In Excel

By hiding formulas, the recipe of the cell will not display in the Formula Bar when the cell is selected. The process of hiding recipe is very similar to that of locking cells ( with barely an extra setting enabled ). Like lock cells, concealed cells besides work merely once the sheet is protected. The rule cells have to be selected to be hidden. We will use the Go To Special feature of speech to select the formula cells to hide them. here are the steps to select and hide the convention cells and to protect the sheet :

  • Select all the cells in the worksheet (Ctrl + A).
  • In the Home tab, from the Editing section, click on the Find & Select From the drop-down list, select Go To Special to open the Go To Special dialog box.
  • Select the Formulas radio button and make sure all the checkboxes under Formulas are checked. Then click OK.
  • All cells containing formulas on the worksheet will be selected like so:

The future pace is to hide the selected formula cells .

  • Launch the Format Cells dialog box by clicking on the dialog launcher (small arrow on the bottom right) in the Alignment section (or use the keyboard shortcut Ctrl + 1).
  • In the Protection tab, check the Hidden Then click OK.

Lock-And-Hide-Formula-Cells-In-Excel-13 This has no ocular impact on the sheet even as the hide properties alone work once the sheet is protected. If you select any of the obscure cells right now, you can still see the convention in the rule banish. Let ’ s move on to protecting the sheet :

  • Go to the Review From the Protect section, select the Protect Sheet command button.
  • Confirm that the Protect worksheet and contents of locked cells checkbox is checked.
  • In this Protect Sheet dialog window, you have the option of adding a password to unprotect the sheet. It can be helpful to keep a password to allow some users to edit the locked cells or view the formulas by sharing the password with them.
  • Other controls of the worksheet are in the Allow all users of this worksheet to: field. We are going with the default settings (top 2 checkboxes checked).
  • After making the changes as you like, click OK.

Following these steps have protected the worksheet and hidden the convention cells. If you select any cell containing a formula now, you can see that the Formula Bar will not display its formula. Lock-And-Hide-Formula-Cells-In-Excel-14 If you had set up a password in the Protect Sheet window, the exploiter will be asked to enter the password while unprotecting the sheet from the Unprotect Sheet command button in the Review tab. With no password, the sheet can be unprotected by clicking the Unprotect Sheet command button. Recommended Reading – Excel Shows Formula alternatively of Result ( How to Fix )

VBA to Lock and Hide Formulas In Excel

You can ditch all the steps above and select, lock and hide formulas and protect the worksheet using VBA. All you have to do is entree the VBA editor program, feed in the right field code, run the dominate and that ’ s job done ! VBA ( Visual Basic for Applications ) is a scheduling tool for MS Office applications. We can use codes in VBA to get assorted tasks done in Excel, specially if they are arduous or repetitive. Below we will show you how to use VBA to lock and hide formulas and protect the sheet, all using one code. The steps are :

  • Press Alt + F11 to run the VBA editor. If you don’t have the Developer tab, you can add it by customizing the ribbon and then access the VBA editor using the Visual Basic button.
  • Click on the Insert tab and select Module from the list.

Lock-And-Hide-Formula-Cells-In-Excel-15

  • In the opened module window, copy and paste the following code:
 submarine  LockAndHideAllCellsWithFormulas()
With ActiveSheet
.Unprotect
.Cells.Locked = delusive
.Cells.SpecialCells(xlCellTypeFormulas).Locked = true
.Cells.SpecialCells(xlCellTypeFormulas).FormulaHidden = true
.Protect AllowDeletingRows: = truthful
end With
end submarine

Lock-And-Hide-Formula-Cells-In-Excel-16

  • Close the module window and the VBA editor.
  • Go to the View tab and select the Macros command button.

Lock-And-Hide-Formula-Cells-In-Excel-17

  • Select the relevant macro and then select Run.

Lock-And-Hide-Formula-Cells-In-Excel-18 Using the above-mentioned code, we have locked and hidden the formula cells and protected the sheet. If you try editing a formula cell, you will receive a prompt, disabling you from editing. That confirms the recipe cells are locked. Selecting any recipe cellular telephone, you can see below that the recipe is not visible in the Formula Bar. This confirms the recipe cells are hidden.

Lock-And-Hide-Formula-Cells-In-Excel-19 Both the locked and hidden properties are working which means the sheet is protected which is besides confirmed from the Review check. immediately let ’ s lock that up and stop right here. We hope we gave you the wide particulars on protecting the formula cells from all sorts of tweaking. We ’ ll be binding, unlock and unraveling more excel mysteries ; be fix to keep up !

reservoir : https://epicentreconcerts.org
Category : How To

Related Posts

Leave a Reply

Your email address will not be published.