Excel 2016 Someone Else Is Working in Please Try Again Later

This tutorial shows how to hide formulas in Excel so they practise not show up in the formula bar. Also, you volition learn how to quickly lock a selected formula or all formulas in a worksheet to protect them from being deleted or overwritten by other users.

Microsoft Excel does its best to make formulas easy to interpret. When y'all select a cell containing a formula, the formula displays in the Excel formula bar. If that's not enough, you can evaluate each part of the formula individually past going to the Formulas tab > Formula Auditing group and clicking the Evaluate Formulas button for a step-by-step walkthrough.

But what if you don't desire your formulas to be shown in the formula bar, nor anywhere else in the worksheet, for confidentiality, security, or other reasons? Moreover, you may desire to protect your Excel formulas to prevent other users from deleting or overwriting them. For case, when sending some reports outside your arrangement, you lot may want the recipients to see the final values, but you don't want them to know how those values are calculated, let along making any changes to your formulas.

Luckily, Microsoft Excel makes it adequately simple to hibernate and lock all or selected formulas in a worksheet, and further on in this tutorial we will show the detailed steps.

• How to lock formulas in Excel
• How to hide formulas in Excel
• How to unprotect and unhide formulas

How to lock formulas in Excel

If yous've put a lot of endeavour in creating an awesome worksheet that you need to share with other people, you certainly wouldn't desire anyone to mess upwardly whatever smart formulas that y'all worked so hard on! The most mutual fashion of preventing people from tampering with your Excel formulas is to protect the worksheet. Even so, this does non just lock formulas, simply rather locks all cells on the sheet and stops users from editing any of the existing cells and entering any new data. Sometimes you may not want to go that far.

The following steps demonstrate how yous can merely lock a selected formula(s) or all cells with formulas on a given sheet, and exit other cells unlocked.

one. Unlock all cells in the worksheet.

For starters, unlock all of the cells on your worksheet. I realize that it may sound disruptive because yous have not locked any cells yet. However, by default, the Locked selection is turned on for all cells on any Excel worksheet, whether an existing or a new one. This does not hateful that you cannot edit those cells, because locking cells has no upshot until you protect the worksheet.

And then, if you lot want to lock only cells with formulas, be certain to perform this step and unlock all cells on the worksheet first.

If you want to lock all cells on the sail (whether those cells contain formulas, values or are bare), then skip the first iii steps, and go right to Step iv.

• Select the entire worksheet either by pressing Ctrl + A, or clicking the Select All button (the gray triangle in the summit left corner of the worksheet, to the left of the letter A).
• Open the Format Cells dialog by pressing Ctrl + 1. Or, right-click whatsoever of the selected cells and choose Format Cells from the context card.
• In the Format Cells dialog, get to the Protection tab, uncheck the Locked option, and click OK. This will unlock all cells in your worksheet.

2. Select the formulas you want to lock.

Select the cells with the formulas yous want to lock.

To select non-adjacent cells or ranges, select the get-go jail cell/range, press and hold Ctrl, and select other cells/ranges.

To select all cells with formulas on the sheet, practise the following:

iii. Lock cells with formulas.

At present, go to lock the selected cells with formulas. To do this, press Ctrl + one to open the Format Cells dialog again, switch to the Protection tab, and check the Locked checkbox.

The Locked option prevents the user from overwriting, deleting or changing the contents of the cells.

four. Protect the worksheet.

To lock formulas in Excel, checking the Locked option is non sufficient because the Locked attribute has no effect unless the worksheet is protected. To protect the sheet, practise the following.

• Go to the Review tab > Changes grouping, and click Protect Sheet.
  
• The Protect Canvass dialog window will appear, and you type a password in the corresponding field.

  This password is needed for unprotecting the worksheet. No one, even yourself, will be able to edit the sheet without entering the password, so exist sure to remember it!
  

  Also, you need to select the actions that are immune in your worksheet. As you see in the screenshot above, 2 checkboxes are selected by default: Select locked cells and Select unlocked cells. If you lot click the OK button leaving only these two options selected, the users, including yourself, will be able simply to select cells (both locked and unlocked) in your worksheet.

  If y'all want to allow some other actions, e.g. sort, auto-filter, format cells, delete or insert rows and columns, bank check the respective options in the list.

• Once y'all've selected any additional actions you want to allow, if whatsoever, click the OK button.
• The Confirm Countersign dialog box will appear and inquire you to retype the password, to forestall an adventitious misprint from locking up your Excel worksheet forever. Retype the password and click OK.

Done! Your Excel formulas are now locked and protected, though visible in the formula bar. If you also desire to hide formulas in your Excel sheet, read through the following section.

Tip. If y'all need to edit or update your formulas in one case in a while and y'all don't want to waste product your time on protecting / unprotecting the worksheet, yous can motility your formulas to a separate worksheet (or even workbook), hide that sail, and then, in your main sail, simply refer to the appropriate cells with formulas on that hidden sheet.

Hiding a formula in Excel means preventing the formula from beingness shown in the formula bar when you click a jail cell with the formula's result. To hide Excel formulas, perform the following steps.

1. Select a cell or range of cells containing the formulas y'all want to hibernate.

   Y'all can select non-adjacent cells or ranges by holding the Ctrl key, or the unabridged sheet past pressing the Ctrl + A shortcut.

   To select all cells with formulas, use the Go To Special > Formulas feature as demonstrated in Selecting cells with formulas.

2. Open the Format Cells dialog by doing any of the following:
   • Printing the Ctrl + 1 shortcut.
   • Right-click the selected cell(southward) and choose Format Cells from the context menu.
   • Go to the Dwelling house tab > Cells group, and click Format > Format Cells.
3. In the Format Cells dialog box, switch to the Protection tab, and select the Hidden checkbox. It is this option that prevents an Excel formula from being shown in the formula bar.

   The Locked attribute, which prevents the contents of the cells from editing, is selected by default, and in most cases you'd want to leave it this style.
   

4. Click the OK button.
5. Protect your Excel worksheet past performing these steps.

Note. Please recollect that locking cells and hiding formulas has no effect until you protect the worksheet (a curt notice right underneath the Locked and Hidden options on the Format Cells dialog points to the next steps). To make certain of this, select any prison cell with a formula, and wait at the formula bar, the formula will nevertheless exist there. To really hibernate formulas in Excel, exist sure to protect the worksheet.

To get the previously hidden formulas to show in the formula bar over again, do 1 of the post-obit:

• On the Home tab, in the Cells grouping, click the Format button, and select Unprotect Sheet from the drop-down card. So type the countersign you entered when protecting the spreadsheet, and click OK.
• Or, become to the Review tab > Changes grouping, and click the Unprotect Canvass button.

Notation. If yous've subconscious the formulas before protecting the workbook, yous may want to uncheck the Hidden checkbox after unprotecting the worksheet. This won't have whatever immediate event because the formulas showtime showing in the formula bar as soon as yous've removed the worksheet protection. Notwithstanding, if you lot ever want to protect the same canvas in the hereafter, but permit the users encounter the formulas, make certain the Hidden attribute is not selected for those cells (select the cells with formulas, press Ctrl + 1 to open up the Format Cells dialog, go to the Protection tab and remove a tick from the Hidden box).

This is how you tin hide and lock formulas in Excel. In the next tutorial, nosotros volition talk over various ways to copy formulas and you will larn how to apply a formula to all cells in a given column in a click. I thanks for reading and hope to encounter you again soon!

You may also be interested in

Mail a comment

evansgreped.blogspot.com

Source: https://www.ablebits.com/office-addins-blog/2016/01/14/hide-lock-formulas-excel/