How To Lock Formulas in Excel in 5 Steps

Matthew Matthew is a freelancer who has produced a variety of software articles for sites such as Bright Hub. He has an A – Level in ICT, at grade C, and is proficient with a number of software packages. Check out his book at http://battlesofthepacificwar.blogspot.co.uk/. Battles of the Pacific War 1941 -1945 recalls where, when and how the Pacific War was won and lost within the battlefields of the Pacific. Read more June 19, 2021

Excel is a spreadsheet application that has loads of functions that you can add to sheets. Excel 2016 also has enhanced collaboration options for sharing sheets with other recipients. Those who often share their spreadsheets might sometimes need to lock formula (or function) cells. Locking the function cells will ensure that other spreadsheet users can not delete or edit the formulas.

The full versions of Excel include lock and protect options for spreadsheets. When you select to lock specified function cells, nobody, including yourself, can edit them. You can unlock the cells for editing by entering your sheet password. So if you do not need anybody to edit a shared spreadsheet’s functions, it is usually better to lock the cells beforehand.

Here are the steps to Lock Cells with Formulas:
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

Many formulas in Excel work on calculating values like sums, differences or percentages. Locking these formulas helps ensure the formula works correctly with the data input by users. Here are some commonly locked types of formulas:

Division formulas

Division formulas in Excel calculate values by dividing one number by another. Common uses include calculating percentages or the speed of a process with a unit per time measurement. This formula is “=Numerator/Denominator,” then using “Ctrl+Shift+%” if converting to a percentage. Locking this formula ensures a users ability to input data for division calculation without inadvertently affecting the formula.

Comparison formulas

Comparison formulas look at the change between two values, like production numbers from week to week, or comparing data from two sets to see what values match, like when checking inventory. Comparison formulas include using greater than (“>“), less than (“<“), or equal to (“=”) signs to compare values, such as “=A2>B2″ to determine if a value in A is greater than the value in B. Comparisons also use the “IF” function for adjacent ranges and the “VLOOKUP” function for non-adjacent ranges. Locking this formula ensures all comparisons between two changing datasets function the same.

Addition formulas, which use the “=SUM” construction, combine values from a specified cell range or multiple ranges and calculate the added value. Addition formulas can track things like the number of words written in a week or the sum of sales from four consecutive weeks. These formulas often look at sums of data that change over time or collective sums of data from different sources, like combined weekly sales in a district with several store locations.

Date range formulas

Dates and date ranges commonly appear in spreadsheets, and theyre often calculated using one of several formulas. Calculating an end date based on a start date often uses addition, like adding five days to a start date with “=A2+5”. Subtraction formulas can find the number of days between a start and end date, using “=(date2-date1)” to find the difference. Date range displays often use “=TEXT(date1,”format”) & “-” &TEXT(date2,”format”)” formula, sometimes using “to” instead of “-” as the joiner, to display the endpoints of a set of dates.

What is the purpose of locking formulas in Excel?

The purpose of locking formula cells in an Excel spreadsheet is to protect the integrity of the spreadsheet. Locking benefits both the creator of a spreadsheet and any additional users. Locking formulas can help when working on a spreadsheet with multiple formulas because it prevents any users from making accidental changes, like deleting or adding characters, overwriting or deleting formulas that could affect the results in the sheet. Also, the original user might want additional users to input data within certain cells, like when using the worksheet to collect the same type of data from multiple users.

How to lock formulas in Excel

You can use these steps to lock formulas in an Excel spreadsheet:

First, find the spreadsheet that you want to lock formulas in. If you have an existing spreadsheet you want to lock, open it by selecting “File”, selecting “Open” from the left column and choosing your sheet. If you dont have a spreadsheet made, create a new one by selecting “File” and then “New” from the top left corner. Format your new spreadsheet to your specifications, including adding formulas.

2. Unlock all cells

When creating a spreadsheet, all cells have the “Locked” function on by default, although this function only takes effect after protecting the spreadsheet. Unlocking all cells first allows you to choose which particular cell you want to lock later. To begin, select all cells by using the “Ctrl+A” command, then you can use one of two methods to unlock the cells.

With the first method, you right-click on the selection and choose “Format Cells” from the context menu, then go to the “Protection” tab, uncheck the box for “Locked” and click “OK.” With the second method, after selecting all cells, use the “Ctrl+1” command to open the “Format Cells” dialog, then go to the “Protection” tab and uncheck the “Locked” box. Now the whole spreadsheet is unlocked.

3. Select the cells

After unlocking the spreadsheet, the next step is selecting the cells you want to lock. If you want to lock all cells in the spreadsheet with formulas, go to the “Home” tab at the top, open the “Editing” group, click on “Find & Select,” use the “Go To Special” button to open the “Go To Special” dialog box, then select the “Formulas” button to select all cells with formulas and finish with “OK.” If you want to select only certain cells with formulas, including non-adjacent ones, press and hold “Ctrl” while selecting the specific cells you want to lock.

4. Lock the cells

After selecting the cells you want to be locked, you can lock the spreadsheet again. Lock the spreadsheet by using the “Ctrl+1” command to open the “Format Cells” dialog box, then go to the “Protection” tab and check “Locked.” As with unlocking the spreadsheet, activating the “Lock” function only takes effect after protecting the worksheet. This step allows you to make final adjustments to any formulas before fully locking the worksheet.

Protecting the spreadsheet enables the “Locked” effect. To protect the spreadsheet, start at the “Review” tab, open the “Changes” tab, select “Protect Sheet” to open the “Protect Sheet” dialog, then create and type a password for the spreadsheet into the specified field. The password allows authorized users to access and edit locked cells. Next, select the actions allowed for users. By default, Excel selects the “Select Locked Cells” and “Select Unlocked Cells” actions when protecting the spreadsheet. These allow all users, including those with the password, to only select unlocked and locked cells.

You can choose the actions users can perform, including editing or adding formatting to unlocked cells, by checking or unchecking the boxes next to each action. Once youve chosen the allowed set of actions, select “OK.” Excel will prompt you to “Confirm Password” by typing the same password again. Finish protecting the spreadsheet and locking the cells by selecting “OK.”

FAQs about locked cells in Excel

Here are answers to some frequently asked questions regarding locked cells in Excel:

Why lock cells without formulas?

Spreadsheet creators lock cells for a few reasons. Sometimes, users want to create a design, insert an image, or format certain cell ranges specifically. This requires merging, expanding or filling cells with color. Locking these cells allows the creator to preserve the formatting and design when other users access the sheet. Also, sometimes spreadsheets function to gather specific data or input from a group of other users, so the creator locks the spreadsheet and activates certain actions for the other users so they input their data in the right spot or in the right format.

How do you display results without displaying the formula?

Showing the results of a formula without displaying the formula uses the “Hide” function. If you want to send a spreadsheet without displaying confidential or strategic data, you can hide the contents of those cells and protect the spreadsheet to prevent other users from seeing that information. To hide cells in Excel, select the cells, open the “Format Cells” dialog with “Ctrl+1”, then under the “Protection” tab select “Hidden”, then protect the spreadsheet.

How do you edit locked cells?

To edit locked cells, you unprotect the sheet and unhide any cells. There are two methods for this. Starting at the “Home” tab, open the “Cells” group and click the “Format” button, then select “Unprotect Sheet” and enter the password and select “OK.” Starting at the “Review” tab, select the “Unprotect Sheet” button and enter the password then select “OK.” You can unhide cells by going to the “Protection” tab of the “Format Cells” box, then select “Unhide” to show the cell contents. After you make your edits, lock and hide the cells and protect the sheet again.

FAQ

Can you lock just formulas in Excel?

In addition to protecting workbooks and worksheets, you can also protect formulas. Excel for the web can’t lock cells or specific areas of a worksheet. If you want to lock cells or protect specific areas, click Open in Excel and lock cells to protect them or lock or unlock specific areas of a protected worksheet.

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

Just select the cells you want to lock, then press F4. This will add the ‘\$’ symbol to the cell references in the formula, locking the cells in place. For example, if you have a formula in cell A1 that references cell B1, and you press F4, the formula will change to =\$A\$1+\$B\$1.

How do I quickly lock a formula in Excel?

Betreff: Lock cell without protecting worksheet
1. Start Excel.
2. Switch to the “Check” tab and select “Remove sheet protection”. …
3. Select all cells by clicking in the top left corner of the table.
4. In the “Start” tab, select “Format> Format cells> Protection” and uncheck “Locked”.

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

Betreff: Lock cell without protecting worksheet
1. Start Excel.
2. Switch to the “Check” tab and select “Remove sheet protection”. …
3. Select all cells by clicking in the top left corner of the table.
4. In the “Start” tab, select “Format> Format cells> Protection” and uncheck “Locked”.