How To Lock Formulas in Excel in 5 Steps

For websites like Bright Hub, freelancer Matthew Matthew has written a variety of software articles. He is skilled with several software programs, has an A-Level in ICT with a grade of C, and Check out his book at http://battlesofthepacificwar. blogspot. co. uk/. The book Battles of the Pacific War, 1941–1945, describes the locations, dates, and circumstances of the Pacific War’s victories and defeats on its battlefields. Read more June 19, 2021.

With the spreadsheet program Excel, you can add a ton of functions to your sheets. Additionally, Excel 2016 offers improved collaboration options for sending sheets to additional recipients. If you frequently share your spreadsheets, you might occasionally need to lock the formula (or function) cells. By locking the function cells, you can prevent other spreadsheet users from erasing or making changes to the formulas.

Spreadsheets can be locked and protected in Excel’s full versions. No one can edit certain function cells that you choose to lock, not even you. Enter your sheet password to enable editing of the cells. Therefore, it is typically preferable to lock the cells in advance if you do not want anyone to edit the functions of a shared spreadsheet.

1. With the cells with formulas selected, press Control + 1 (hold the Control key and then press 1).
2. In the format cells dialog box, select the Protection tab.
3. Check the ‘Locked’ option.
4. Click ok.

Examples of formulas you might lock

Excel has many formulas that calculate values like sums, differences, and percentages. Locking these formulas ensures that they function properly with the data entered by users. Here are some commonly locked types of formulas:

Division formulas

Excel’s division formulas work by dividing one number by another to calculate values. Calculating percentages or a process’ speed using a unit per time measurement are common applications. This formula is Numerator/Denominator,%22 so%22 if%22 converting to a percentage%22 use %22Ctrl Shift%%22 . By locking the formula, users can enter data for the division calculation without accidentally changing the formula.

Comparison formulas

When comparing data from two sets to see what values match, as is the case when checking inventory, comparison formulas examine changes between two values, such as production numbers from week to week. Formulas for comparison often include greater than (” Additionally, comparisons employ the “IF” function for similar ranges and the “VLOOKUP” function for dissimilar ranges. By locking this formula, all comparisons between two dynamic datasets will be uniform.

Addition formulas

Using the “=SUM” construction, addition formulas combine values from a specified cell range or multiple ranges to determine the added value. The number of words written in a week or the total sales over four consecutive weeks are two examples of things that addition formulas can keep track of. These calculations frequently take into account totals of data that fluctuate over time or totals of data from a variety of sources, such as the combined weekly sales in an area with several store locations.

Date range formulas

Spreadsheets frequently contain dates and date ranges, which are frequently calculated using one of several formulas. Adding five days to a start date with “=A2 5” is a common addition method for calculating an end date based on a start date. Using the formula “=(date2-date1)” to find the difference, subtraction formulas can be used to calculate the number of days between a start and end date. Displays of date ranges frequently use “=TEXT(date1,”format”)

What is the purpose of locking formulas in Excel?

To safeguard the spreadsheet’s integrity, formula cells in an Excel spreadsheet can be locked. Locking a spreadsheet is advantageous to both the original creator and any additional users. When working on a spreadsheet with multiple formulas, locking the formulas can be helpful because it prevents users from making mistakes like deleting or adding characters, overwriting formulas, or deleting formulas that could change the results of the sheet. Also, if the worksheet is being used to gather the same type of data from multiple users, the original user may want additional users to enter data in specific cells.

How to lock formulas in Excel

The following procedures can be used to protect formulas in an Excel spreadsheet:

1. Open the spreadsheet

Locate the spreadsheet in which you want to lock the formulas first. If you want to lock an existing spreadsheet, open it by choosing “File,” “Open,” from the left column, and then selecting your spreadsheet. Create a new spreadsheet if you don’t already have one by clicking “File” in the top left corner, followed by “New.” Format your new spreadsheet to your specifications, including adding formulas.

2. Unlock all cells

All cells are initially created with the “Locked” function enabled, but this function isn’t activated until the spreadsheet has been protected. By first unlocking every cell, you can later select which one you want to lock. You can unlock the cells using one of two methods after first selecting all of the cells with the “Ctrl A” command.

The first approach entails performing a right-click on the selection, selecting “Format Cells” from the context menu, then selecting the “Protection” tab, unchecking the “Locked” box, and finally clicking “OK.” Using the “Ctrl 1” command to open the “Format Cells” dialog after selecting all cells, go to the “Protection” tab, and uncheck the “Locked” box Now the whole spreadsheet is unlocked.

3. Select the cells

Selecting the cells you want to lock is the next step after unlocking the spreadsheet. If you want to lock every cell in the spreadsheet with a formula, click “Find” in the “Editing” group under the “Home” tab at the top of the spreadsheet. “Press and hold “Ctrl” while selecting the specific cells you want to lock if you want to select only a subset of the formula-containing cells, including non-adjacent ones.

4. Lock the cells

You can lock the spreadsheet once more after choosing the cells you want to be locked. When the “Format Cells” dialog box appears, use the “Ctrl 1” command to lock the spreadsheet. Then, select the “Protection” tab and make sure “Locked” is checked. As with unlocking the spreadsheet, the “Lock” function can only be used after the worksheet has been protected. You can now make any necessary formula adjustments before completely locking the worksheet.

5. Protect the spreadsheet

Protecting the spreadsheet enables the “Locked” effect. Start at the “Review” tab, click the “Changes” tab, choose “Protect Sheet” to open the “Protect Sheet” dialog, and then enter a password for the spreadsheet in the designated field to secure it. Authorized users are able to access and edit locked cells with the password. Next, select the actions allowed for users. When securing the spreadsheet, Excel by default chooses the “Select Locked Cells” and “Select Unlocked Cells” actions. With these, only selected locked and unlocked cells are accessible to all users, including those with the password.

By checking or unchecking the boxes next to each action, you can control which actions users are permitted to carry out, such as editing or adding formatting to unlocked cells. Once youve chosen the allowed set of actions, select “OK. You will be asked by Excel to “Confirm Password” by entering the same password a second time. By selecting “OK,” the spreadsheet’s security and cell locking are complete. “.

FAQs about locked cells in Excel

Here are some frequently asked questions about Excel’s locked cells and their responses:

Why lock cells without formulas?

Spreadsheet creators lock cells for a few reasons. Users may occasionally want to format specific cell ranges, add an image, or create a design. This requires merging, expanding or filling cells with color. By locking these cells, the sheet’s author can ensure that the formatting and design are maintained when other users access the document. Also, sometimes spreadsheets are used to collect specific data or input from a number of other users. In these cases, the spreadsheet’s creator locks the document and enables specific user actions to ensure that the other users input their data in the proper location or format.

How do you display results without displaying the formula?

The “Hide” function is used to display a formula’s results without displaying the formula. You can hide the contents of those cells and protect the spreadsheet to prevent other users from seeing sensitive or important data if you want to send a spreadsheet without displaying it. Select the desired cells in Excel, press “Ctrl 1” to open the “Format Cells” dialog, choose “Hidden” from the “Protection” tab, and then safeguard the spreadsheet.

How do you edit locked cells?

You must unprotect the sheet and unhide any cells in order to edit locked cells. There are two methods for this. Open the “Cells” group by clicking the “Format” button on the “Home” tab, then choose “Unprotect Sheet,” enter the password, and click “OK.” Select the “Unprotect Sheet” button at the “Review” tab, type the password, and then click “OK. To reveal the contents of a hidden cell, select “Unhide” from the “Format Cells” box’s “Protection” tab. Lock and hide the cells after making your edits, then protect the sheet once more.

Please note that Indeed is not affiliated with any of the businesses mentioned in this article.

How to Lock Cells that have Formulas in Excel (Step-by-Step)

FAQ

Can you lock just formulas in Excel?

You can also protect formulas in addition to workbooks and worksheets. Cells or certain worksheet areas cannot be locked in Excel for the web. Click Open in Excel, lock cells to protect them, or lock or unlock specific areas of a protected worksheet if you want to protect certain areas.

How do you lock formulas in Excel but allow data entry?

Press F4 after choosing the cells you want to lock. This will secure the references to the cells in the formula by appending the dollar sign ($) to them. For instance, pressing F4 will cause a formula in cell A1 that refers to cell B1 to change to =$A$1 $B$1.

How do I quickly lock a formula in Excel?

1. Start Excel.
2. Switch to the “Check” tab and select “Remove sheet protection”.
3. Click in the top left corner of the table to select every cell.
4. Uncheck “Locked” under “Format> Format cells> Protection” in the “Start” tab.

How do I lock a formula in Excel without protecting the sheet?

1. Start Excel.
2. Switch to the “Check” tab and select “Remove sheet protection”.
3. Click in the top left corner of the table to select every cell.
4. Uncheck “Locked” under “Format> Format cells> Protection” in the “Start” tab.