Guide: Lock Formula in MS Excel: Tips and Tutorial
Excel is a spreadsheet application with many functions you can add to worksheets. Excel also has improved collaboration options for sharing worksheets with other recipients. Those who often share their spreadsheets sometimes need to lock formula (or function) cells. Locking the function cells ensures that other spreadsheet users cannot delete or edit the formulas. Full versions of Excel include spreadsheet locking and security options. If you choose to lock certain function cells, no one, including yourself, can edit them. You can unlock the cells for editing by entering your sheet password. So if you don’t need anyone to edit the functions of a shared spreadsheet, it’s usually better to lock the cells beforehand.
Unlock all cells in the spreadsheet
The first thing to note is that Excel’s locked option is selected by default. However, this has absolutely no effect until you choose to protect the spreadsheet. The Locked setting is selected for all cells, so if you protect the spreadsheet, all cells in it will be locked, regardless of whether they contain functions or not. Those who only need to lock functions must first unlock the spreadsheet and then select only the formula cells. To unlock a spreadsheet, select all cells by pressing the Ctrl + A keyboard shortcut. You can also click Select All button at the top left of the spreadsheet. That will select all cells in the sheet as in the snapshot below. Then press the keyboard shortcut Ctrl + 1 to open the Format Cells window. That window contains a Protection tab where you can select the Locked option. Click the Protection tab and clear the selected Locked check box. Press OK button to exit the window.
Lock the spreadsheet formulas
Now that you’ve unlocked the spreadsheet, you can choose to lock only the function cells in it. Select all function and formula cells in the sheet by pressing Find and Select button on the Home tab. Select Go To Special to open the window below. Click on the Formulas radio button to select all options for formula types and press OK button.
Alternatively, you can manually select a function cell with the cursor. Hold down the Ctrl key to select multiple cells. Or hold down the left mouse button button and drag the cursor across multiple cells. Now press the keyboard shortcut Ctrl + 1 to reopen the Format Cells window. Select the Locked option on the Protection tab. Click OK to close the Format Cells window. Nothing will be locked until you apply spreadsheet protection. To protect the sheet, click the Review tab. Press the protective sheet button on that tab to open a password window.
First, enter a password for the sheet in the text box of the Protect Sheet window. By default, the Select locked cells and Select unlocked cells options are selected, so Excel users can select only the function cells, but not edit them. You can select more options there so that spreadsheet users can still apply formatting changes or add hyperlinks to the formula cells. If you press OK button A Confirm Password dialog box opens in the Protect Sheet window. Enter the exact same password again in the text box of that window and hit OK button. If the second password does not match, your original password may contain a typo. Also make sure you haven’t pressed the Caps Lock key, which will use all text in capital letters. Now that you have locked the formula cells, you need to unlock them to edit the functions. You can unlock cells by selecting the Review tab, which contains an Unprotect sheet option. Release pressure on the sheet button to open a password text box. Enter the unlock password in the text box.
Lock spreadsheet cells with Kutools for Excel
If you still need more locking options, please visit Kutools for Excel. Kutools is an add-on for Excel that adds more than 200 additional options to the application. You can also lock cells with Kutools for Excel’s Worksheet Design utility. The Kutools add-on is available for $ 39, and you can try a full trial version for a few months. If Kutools is installed, you can open a new Enterprise tab in Excel. Press the worksheet design button on the Enterprise tab to access the add-on’s locking options. Then you can select a Highlight formulas option to highlight cells containing functions. Select the highlighted cells in the spreadsheet and press the Selection Lock button to lock the formulas. Press the protective sheet button on the Design tab to enter a password. That’s How to lock formula cells in Excel spreadsheets with the built-in options of the application and Kutools add-on. Locking cells keeps their functions intact when sharing spreadsheets. Check out this YouTube page to play an Excel screencast with more information on how to lock spreadsheet cells.
Lock Formula in MS Excel: Tips and Tutorial: benefits
Faq
Final note
I hope you like the guide Lock Formula in MS Excel: Tips and Tutorial. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends. For our visitors: If you have any queries regards the Lock Formula in MS Excel: Tips and Tutorial, then please ask us through the comment section below or directly contact us. Education: This guide or tutorial is just for educational purposes. Misinformation: If you want to correct any misinformation about the guide “Lock Formula in MS Excel: Tips and Tutorial”, then kindly contact us. Want to add an alternate method: If anyone wants to add more methods to the guide Lock Formula in MS Excel: Tips and Tutorial, then kindly contact us. Our Contact: Kindly use our contact page regards any help. You may also use our social and accounts by following us on Whatsapp, Facebook, and Twitter for your questions. We always love to help you. We answer your questions within 24-48 hours (Weekend off). Channel: If you want the latest software updates and discussion about any software in your pocket, then here is our Telegram channel.
