If you are using a spreadsheet at work, there is a chance that you may have to share it with other people. It can be difficult to keep track of changes made by others and if they accidentally enter the wrong data, it could lead to an inaccurate result.
You can protect your data from being accidentally modified by locking the cells. When a cell is locked, it cannot be edited unless the lock is removed.
In this mini-guide, we’ll teach you how to lock cells in Excel, so your data is kept safe.
How To Lock Cells In Excel
Locking cells in Excel is very important if you are sharing your spreadsheet, which is a common requirement in most businesses. It can also come in handy if you are a beginner and you want to avoid losing any progress that you have already made.
Let’s start by talking you through how to lock an entire spreadsheet. When you protect a spreadsheet, all of the cells will be locked, and no one will be able to edit them.
Find the review tab at the top of the screen. In the dropdown, click Protect Sheet. A pop-up will prompt you to set a password and offer you a list of actions that you can allow for users.
Click OK to lock the whole spreadsheet. If anyone tries to edit cells, they will see a pop-up informing them that the sheet is protected and that they need a password to unlock it.
Locking & Unlocking Specific Cells
Locking and unlocking specific cells is simple. Select the cells that you want to lock, right click, and open the Format Cells window.
Here, you can find the Protection tab. Click Locked and then click OK. This will close the pop-up window. These cells are now locked.
Locking Format Cells
If you want to prevent other users from editing cells that have specific controls and formulas, then you will need to lock formatting. Follow the same steps as above and clear the Locked checkbox.
Go to the Home tab and find Go To Special. Click Formulas, and then click OK to select every formula in the spreadsheet. Now, return to the Protection tab and click Locked again. Now, the formatting and formulas cannot be changed.
Locked Check Box
You may want to lock any checkboxes on your spreadsheet. There are two cells to protect here. First, you must protect the cell with the check box. Second, you must protect the cell that this check box is linked to.
Select both cells, and then find the Format Cells window. Open the Protection tab and click Locked. This will mean that the status of the check box in the cell cannot be altered. This is very important if you are sending a sheet with survey results, for example.
The Protection Tab
The protection tab in Excel offers you different options depending on how completely you want to protect your project. If you choose Protect Workbook, the entire file will be protected if you click Protect Structure.
If you click Protect Sheet, you will be given many more options for allowing users access. By default, it will allow users without a password to select cells. You can choose to grant access for tasks such as inserting rows and links, deleting rows and columns, and formatting.
How To Lock Colour Formatting
Sometimes you will want to grant another user the ability to edit a spreadsheet, but you will want the colour formatting to remain unaltered.
Remember that colours count as formatting, so select the cells in question and find the Protection tab. Here, you will see a list of actions you can allow users to do. Make sure that the format cells box is not checked and hit OK.
When you want to share an Excel sheet, you need to head to the Review tab and click Share Workbook or Share. You will then be prompted to set share permissions.
Here, you can allow people to edit or format cells or make it read-only. You can also allow changes by more than one user at the same time for co-authored projects. To see what has been changed, select Protect and Share Workbook, which will prevent track changes from being removed.
Conclusion
It is easy to prevent anyone from making any changes to your workbook, but things can get a little fiddly when you want to allow specific changes.
The protection tab will give you the options you need to prevent formatting changes, allowing you to keep the structure of your project while still allowing for data changes and edits. If you want to learn more about how to lock cells, get in touch with us! We have a range of Excel courses and tutorials to help you develop your skills from beginner to advanced.
FAQs
How to lock cells in excel when scrolling?
Go to the View tab at the top of your workbook and hit Freeze Panes. This will keep the columns and rows that you want locked in place while you are scrolling. Select the cell or column adjacent to the ones you want to remain visible and click Freeze Panes.
Can you lock cells in excel and still sort?
If you want to sort cells that have been locked, you will need to select the cells and head to the Data tab. Click Filter, then head to Review and click Allow Users to Edit Ranges. You can give the range a name and go to the Protection pop-up, where you can allow any user to sort and use an auto-filler in this range.
How to lock cells in excel using a shortcut?
If you want to lock a formula, then press F4. This will lock both the row and the column. If you want to lock the column, press F4 again, and press F4 again if you just want to lock the row.
Can you lock cells in excel for certain users?
Yes! You will need to select the locked cells and set a password to gain permission to edit the cells. When someone tries to edit these cells, there will be a prompt for access.
