Suppose that your boss wants you to protect an entire workbook, but also wants to be able to change a few cells after you enable protection on the workbook. Before you enabled password protection, you had unlocked some cells in the workbook. Now that your boss is done with the workbook, you can lock these cells. Follow these steps to lock cells in a worksheet:
Select the cells you want to lock. On the Home tab, in the Alignment group, click the small arrow to open the Format Cells popup window. On the Protection tab, select the Locked check box, and then click OK to close the popup. Note: If you try these steps on a workbook or worksheet you haven’t protected, you’ll see the cells are already locked. This means that the cells are ready to be locked when you protect the workbook or worksheet. On the Review tab in the ribbon, in the Changes group, select either Protect Sheet or Protect Workbook, and then reapply protection. See Protect a worksheet or Protect a workbook, Tip: It’s a best practice to unlock any cells that you may want to change before you protect a worksheet or a workbook, but you can also unlock them after you apply protection. To remove protection, simply remove the password.
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,
Contents
How do you lock a single cell in Excel?
Go to the Protection tab and uncheck Locked option and click Ok. Now select only the cells or columns, rows that you want to protect. Right click and choose Format cells again. Go to the protection tab and check Locked option and click Ok.
How do I lock certain cells from moving in Excel?
Freeze panes to lock specific rows or columns 2. On the View tab, in the Window group, click the arrow below Freeze Panes. time, click Freeze Panes. Unfreeze Panes so that you can unlock any frozen rows or columns.
How do I make only one cell editable in Excel?
Only allow changes to certain cells with Excel build-in feature – You can use the Format Cells feature and the Protect Sheet feature to only allow changes to certain cells in Excel.1. Select the cells which you only allow to change in a worksheet, right click on the selected cells, and then click Format Cells from the right-clicking menu. See screenshot: 
2. In the Format Cells dialog box, go to the Protection tab, uncheck the Locked box, and then click the OK button. 
3. Click Review > Protect Sheet, See screenshot: 4. Enter your password into the Protect Sheet dialog box, and confirm this password in the Conform Password dialog box. 
Now changes only be allowed to the unlocked cells in this worksheet. If you change other cells except the unlocked ones, you will get a dialog box as below screenshot shown.
How do I lock a cell reference?
Excel for Microsoft 365 for Mac Excel 2021 for Mac Excel 2019 for Mac Excel 2016 for Mac Excel for Mac 2011 More.Less By default, a cell reference is relative, For example, when you refer to cell A2 from cell C2, you are actually referring to a cell that is two columns to the left (C minus A), and in the same row (2).
A formula that contains a relative cell reference changes as you copy it from one cell to another. For example, if you copy the formula =A2+B2 from cell C2 to C3, the formula references in C3 adjust downward by one row and become =A3+B3, If you want to maintain the original cell reference when you copy it, you “lock” it by putting a dollar sign ( $ ) before the cell and column references.
For example, when you copy the formula =$A$2+$B$2 from C2 to D2, the formula stays exactly the same. This is an absolute reference. In less frequent cases, you may want to make a cell reference “mixed” by preceding either the column or the row value with a dollar sign to “lock” either the column or the row (for example, $A2 or B$3).
Select the cell that contains the cell reference that you want to change. In the formula bar, click the cell reference that you want to change. Press + T to move through the combinations. The following table summarizes what happens if a formula in cell A1, which contains a reference, is copied. Specifically, the formula is copied two cells down and two cells to the right, to cell C3.
| Current reference (description): | Changes to: |
| $A$1 (absolute column and absolute row) | $A$1 (the reference is absolute) |
| A$1 (relative column and absolute row) | C$1 (the reference is mixed) |
| $A1 (absolute column and relative row) | $A3 (the reference is mixed) |
| A1 (relative column and relative row) | C3 (the reference is relative) |
Does F4 lock cells in Excel?
15 essential Excel shortcuts for locking cell references If you work with formulas in Excel, you know that it’s important to lock cell references. Otherwise, you run the risk of your formulas returning errors. But what’s the best way to lock cell references? There are a few different ways to lock cell references in Excel.
You can use the F4 key, you can use the $ sign, or you can use a combination of both. Each method has its own advantages and disadvantages. Let’s take a look at each one in turn.1. Use the F4 key The F4 key is the easiest way to lock cell references. Simply select the cell or range of cells that you want to lock, then press the F4 key.
Excel will automatically add the $ sign to the appropriate places in the cell reference. One advantage of using the F4 key is that it’s quick and easy. You don’t have to remember any special syntax, and you can lock cell references with just a few keystrokes.
However, there are a few disadvantages to using the F4 key as well. First, the F4 key only works if you’re using the absolute cell reference style. That means that if you’re using the relative cell reference style, you won’t be able to use the F4 key to lock cell references. Second, the F4 key can be a little finicky.
If you accidentally press the wrong key, you might end up changing your cell reference instead of locking it.2. Use the $ sign Another way to lock cell references is to use the $ sign. The $ sign is used to indicate an absolute cell reference, which means that the reference will not change when you copy or move the formula.
- =B$1
- If you copy this formula to cell C1, the cell reference will change to C1. But if you use the $ sign, the cell reference will stay the same:
- =$B$1
You can use the $ sign to lock either the row reference or the column reference, or both. To lock the row reference, put the $ sign before the row number. To lock the column reference, put the $ sign before the column letter. To lock both the row and column reference, put the $ sign before both the row number and column letter.
One advantage of using the $ sign is that it’s very flexible. You can lock just the row reference, just the column reference, or both. You can also use the $ sign in conjunction with the F4 key. For example, if you want to lock the row reference but not the column reference, you can use the following syntax: =$B1 Another advantage of using the $ sign is that it’s easy to remember.
Once you get used to using it, you’ll be able to lock cell references without even thinking about it. However, there are a few disadvantages to using the $ sign as well. First, it can be easy to forget to use the $ sign, which can lead to errors in your formulas.
- =B$1
- This can be a frustrating mistake to make, and it can be hard to spot if you’re not paying close attention.
- 3. Use a combination of the F4 key and the $ sign
Another way to lock cell references is to use a combination of the F4 key and the $ sign. This method is similar to the previous one, but it’s a little more foolproof. Simply select the cell or range of cells that you want to lock, then press the F4 key.
- Excel will automatically add the $ sign to the appropriate places in the cell reference.
- One advantage of using this method is that it’s very difficult to make a mistake.
- If you accidentally lock the wrong cell reference, you can simply press the F4 key again to unlock it.
- Another advantage is that you can use this method with the relative cell reference style.
That means that you can lock cell references without having to switch to the absolute cell reference style. However, there are a few disadvantages to using this method as well. First, it’s a little more time-consuming than the other methods. Second, it’s not as flexible as the other methods.
For example, you can’t use this method to lock just the row reference or just the column reference. You have to lock both the row and column reference at the same time.4. Use a named range Another way to lock cell references is to use a named range. A named range is a range of cells that has a name. For example, you could create a named range for a range of cells that contains customer data.
To create a named range, select the cells that you want to include in the range, then click the “Name” box on the formula bar. Type a name for the range, then press Enter.
- Once you’ve created a named range, you can use the name in formulas instead of the cell references. For example, if you have a named range called “Customers”, you can use the following formula:
- =SUM(Customers)
- This formula will sum all the values in the named range. You can also use the named range in other formulas, such as the VLOOKUP function:
- =VLOOKUP(A1,Customers,2,FALSE)
- This formula will look up the value in cell A1 in the named range “Customers” and return the value in the second column. You can also use the INDIRECT function to reference a named range:
- =INDIRECT(A1)
This formula will return the value of the cell that is referenced in cell A1. For example, if cell A1 contains the text “Customers”, the formula will return the value of the cell in the named range “Customers”. One advantage of using named ranges is that they’re very flexible.
- You can use them in any type of formula, and you can reference them by name instead of by cell reference.
- This can make your formulas easier to read and understand.
- Another advantage is that you can use named ranges with the relative cell reference style.
- That means that you can lock cell references without having to switch to the absolute cell reference style.
However, there are a few disadvantages to using named ranges. First, they can be a little bit time-consuming to set up. Second, you have to remember the name of the range when you use it in a formula. This can be a challenge if you have a lot of named ranges, or if you’re not used to using them.
- Finally, named ranges can’t be used in array formulas.5.
- Use the INDIRECT function Another way to lock cell references is to use the INDIRECT function.
- The INDIRECT function is a built-in function in Excel that allows you to reference cells indirectly.
- In other words, you can use the INDIRECT function to reference cells by their address instead of by their cell reference.
For example, if you have a cell that contains the text “A1”, you can use the INDIRECT function to reference the cell that is at the address “A1”:
- =INDIRECT(A1)
- This formula will return the value of the cell that is at the address “A1”. You can also use the INDIRECT function to reference a named range:
- =INDIRECT(A1)
- This formula will return the value of the cell in the named range “Customers”.
One advantage of using the INDIRECT function is that it’s very flexible. You can use it to reference cells by their address, by their cell reference, or by their named range. This can make your formulas easier to read and understand. Another advantage is that you can use the INDIRECT function with the relative cell reference style.
- That means that you can lock cell references without having to switch to the absolute cell reference style.
- However, there are a few disadvantages to using the INDIRECT function.
- First, it can be a little bit time-consuming to set up.
- Second, you have to remember the address or name of the cell when you use it in a formula.
This can be a challenge if you have a lot of cells, or if you’re not used to using the INDIRECT function. Finally, the INDIRECT function can’t be used in array formulas.6. Use the OFFSET function Another way to lock cell references is to use the OFFSET function.
- The OFFSET function is a built-in function in Excel is a powerful tool that can help you manage and analyze data with ease.
- However, it can be time-consuming to perform repetitive tasks, such as locking cell references.
- Fortunately, Excel offers a variety of shortcuts that can help you work more efficiently.
In this article, we will explore 15 essential Excel shortcuts for locking cell references.
How do I make only certain cells editable?
Steps to Allow Only Certain People to Edit Specific Cells in Excel –
Select the cell or range of cells that you want only a certain person to be able to edit. Go to the Review tab and click Allow Users to Edit Ranges, A window will open; in that window, click New. In the window that opens, enter a title for the range, this is what you will see when you manage ranges that can be edited; make sure the cells that you want to be edited by the user are entered correctly, this should have happened already if you selected the desired cells before you got here; then, enter a password that the user must have in order to edit this range of cells. When you are done, hit the OK button. In the next window, you will have to re-enter the password you used in the previous window and then hit OK. Now, you will see the password protected range of cells and the title for that range in a list. Hit New to add another range if you want. (Otherwise hit OK and skip to step 11) Here, you will repeat step 4. The difference now is that you will need to select the new range that you want to be password protected. To do that, click the little box to the right of the input box for the Refers to cells: section. Then, select the desired cells: Then, hit Enter or click the little box on the right side of the tiny window again. Now, your new range has been selected and is ready to go. Finish filling out the Title and the Password like we did above and then hit OK and re-enter the password. Now, we have two password protected ranges. Repeat Steps 7 to 9 as much as you need to add as many password protected ranges as you need. When you are done, hit OK on the window that lists the ranges, the one shown in Step 9. We are not done yet. In order to make this work, we need to protect the worksheet. In the window that opens, type a password that will be used to lock the entire worksheet. Once the worksheet is locked, only those that have a password to edit specific ranges will be able to edit anything, unless, of course they have the password that was used to lock this sheet. After entering the password, hit OK. Re-enter the password on the next window and hit OK. That’s it!
Now let’s test it out. When you try to edit a cell that was password protected, you must have the correct password in order to edit that cell. Once you enter the password, you can freely edit that range of cells.
How do I restrict editing to certain users in Excel?
Set an expiration date for a file –
- Open the file.
- Select File > Info,
- Select Protect Workbook, point to Restrict Permission by People, and then select Restricted Access,
- In the Permissions dialog box, select the Restrict permission to this workbook check box, and then select More Options,
- Under Additional permissions for users, select the This workbook expires on check box, and then enter a date.
- Select OK twice.
Why is F4 not locking in Excel?
Some keyboards have a function lock key that can prevent the F4 key from working as expected. Look for a key labeled ‘Fn Lock’ or ‘F Lock’ and try pressing it to toggle the function lock on and off.
What happens when you click F4 twice in Excel?
Excel Absolute Reference Shortcut – The One Keystroke Solution If you work with formulas in Excel, you know that you can make a cell reference absolute by adding dollar signs to the row and column reference, like this: $A$1. This makes the reference absolute, meaning it won’t change when you copy the formula.
Absolute references are handy when you want a formula to always refer to the same cell, no matter where you copy it. For example, if you have a formula in cell A1 that sums cells B1:B5, and you copy that formula to cell C1, the copied formula will sum cells D1:D5 instead, since it’s relative to the new location.
But if you make the reference to cell B1 absolute, like this: $B$1, the copied formula will still sum cells B1:B5. You can enter the dollar signs manually when you create a formula, but there’s an easier way. Just press F4 after you type the cell reference in the formula.
For example, type =A1+B1 and then press F4. The cell reference will change to $A$1. Press F4 again and it will change to A$1, and so on. When you get to the reference you want, just stop pressing F4. You can also use the F4 key to change an existing cell reference to an absolute reference. Just put your cursor on the reference you want to change and press F4.
For example, if your formula is =A1+B1 and you want to make the reference to cell A1 absolute, just put your cursor on the “A1” part of the formula and press F4. The reference will change to $A$1. If you want to change an absolute reference back to a relative reference, you can press F4 multiple times until the reference changes back to the way you want it.
For example, if your formula is =$A$1+B1 and you want to make the reference to cell A1 relative, just put your cursor on the “$A$1” part of the formula and press F4 twice. The reference will change to A1. You can also use the F4 key to change a mixed reference to an absolute reference or a relative reference.
A mixed reference is one that has both an absolute reference and a relative reference. For example, if your formula is =$A1+B1, the reference to cell A1 is an absolute reference because it has a dollar sign before the column reference, and the reference to cell B1 is a relative reference because it doesn’t have a dollar sign before the column reference.
- If you want to make both references absolute, just put your cursor on the “A1” part of the formula and press F4 three times.
- The reference will change to $A$1.
- If you want to make both references relative, just put your cursor on the “$A$1” part of the formula and press F4 once.
- The reference will change to A1.
You can also use the F4 key to change a relative reference to an absolute reference. Just put your cursor on the reference you want to change and press F4 until the reference changes to the way you want it. For example, if your formula is =A1+B1 and you want to make the reference to cell B1 absolute, just put your cursor on the “B1” part of the formula and press F4.
- The reference will change to $B$1.
- The F4 key is a handy shortcut for making cell references absolute or relative.
- Just remember that you can press F4 multiple times to cycle through the different reference options.
- And if you’re not sure what reference you have, just put your cursor on the reference and press F4 until you get the reference you want.
Excel is a powerful tool that can help you manage and analyze data with ease. However, it can be time-consuming to perform certain tasks, especially if you’re not familiar with all the shortcuts available. One of the most useful shortcuts in Excel is the absolute reference shortcut, which can save you a lot of time and effort.
What do I add to Excel to lock a formula?
3 steps to lock formulas in excel using the $ shortcut If you’ve ever accidentally deleted a cell in Excel that contained a formula, you know how frustrating it can be. Even worse is when you accidentally delete part of a formula. All your hard work, gone in a flash.
- Select the cells that contain your formulas.
- Click the ‘Home’ tab.
- Click the ‘Format’ button.
- Click ‘Format Cells’.
- Click the ‘Protection’ tab.
- Check the ‘Locked’ box.
- Click ‘OK’.
Now, when you try to delete a cell that contains a locked formula, you’ll get a warning message telling you that the cell is locked and the formula will not be deleted. You can still delete the cell if you want, but the formula will remain intact. One more thing: you can also use the ‘$’ shortcut to quickly lock a cell or range of cells.
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. Now, no matter what you do, the formula will always reference cells A1 and B1.
Locking formulas is a quick and easy way to protect your work in Excel. Give it a try the next time you’re working on a complex spreadsheet. Excel is a powerful tool that can help you manage and analyze data with ease. One of the most important features of Excel is the ability to use formulas to perform calculations.