How to Lock Cells In Excel
Microsoft Excel is a component of the Microsoft Office package. When it is necessary to compute, analyze, and evaluate data, excel is the go-to tool that is used. Excel has surpassed all other spreadsheet management programs in popularity since it’s so simple to learn and use. The Excel program incorporates both simple and advanced mathematical formulas in order to meet the demands of the evolving business sector. You can use these formulas to generate values from your Excel data and to make it simpler for you to utilize Excel for data analysis.
Other users can access the Excel worksheets created by you. By locking the cells, it is possible to stop users from changing any of the cell data or formatting or messing with the formulas that are used to produce values on the worksheet. Within an Excel worksheet, an individual cell, a collection of cells, an entire group of cells, or even a row, can be secured.
In this article, we will learn the easy approaches to demonstrate how to lock the cells in Excel worksheet.
Why lock the cells?
- When working with an Excel worksheet and accidentally pressing DELETE, BACKSPACE, or any alphabetic or numeric key, your data is frequently lost or incorrectly altered. If you catch the mistake, that’s great; if not, you risk getting a wrong answer.
- You could also want to lock the cells if you don’t want anyone else to edit your Excel worksheet without your permission. So that the user can only open and view your worksheet and not alter it, you can lock the cells.
Solution
- Excel offers a mechanism to prevent this from happening to your data. You can lock the cells to prevent editing and secure the file from unauthorized alterations. The worksheet’s protection can also be altered. It means that even when the worksheet is closed or secured, you can still let users execute some actions on it.
What operations need to lock the cell?
You must sequentially complete the following procedures on your worksheet in order to protect it from unauthorized alterations.
- Lock the cells: As previously discussed that all cells are by default locked. Verify that the cells are genuinely locked before continuing to the next stage of protecting them. The cells must first be locked if they are not already.
- Protect the cells: To complete the task, you must take one more step after the cell has been locked. As vital as locking the cells is the stage of protecting the cells. Both actions are equally crucial. This won’t function and the content of the cell will still be editable if the cells are not secured. Therefore, make sure the cells are locked.
How to lock cells in Excel
Locking all the cells
In Excel, you can lock every cell simply by locking the entire worksheet. As we already know, doing this locks the Excel cells by default. Protecting the cells is all that is required. Only when these cells are locked or secured can we keep anyone from making any changes?
- The worksheet with the cells you want to lock or protect should be displayed.
- You may need to press Ctrl + A twice to select everything.
- Press Ctrl + 1 or right-click any of the chosen cells and select Format Cells. The dialogue window for formatting cells appears.
- Navigate to the Protection tab.
- Verify that Locked is checked or chosen. Cells are by default locked.
- Select OK.
- On the Ribbon, select the Review tab.
- Click Protect Sheet in the Changes group. A dialogue box is displayed.
- Select the desired choices by checking or unchecking them (the first two should typically remain checked). To see all of the choices, be sure to scroll down.
- If you wish to prevent someone else from being able to unlock the sheet’s protection, enter a password. You should always have a duplicate of your passwords on hand because they are case-sensitive.
- If you entered a password in the previous step, enter it again.
- Input OK (twice if necessary). In the locked cells, you won’t be able to add or modify data or formulas.
Locking specific cells
There may be situations when you wish to prevent users from changing some cells in a worksheet while allowing them to modify others. As was already indicated, when you safeguard the sheet, all cells are locked by default. However, you can indicate in a cell’s format properties whether it should be locked or unlocked.
- Choose the cells you want to be unlocked. Additionally, you can choose entire rows or columns (typically by Ctrl-clicking or Shift-clicking the row or column headings).
- Press Ctrl + 1 or right-click the chosen cell(s) and select Format Cells. The dialogue window for formatting cells appears.
- Navigate to the Protection tab.
- Deselect or uncheck Locked.
- Select OK.
- On the Ribbon, select the Review tab.
- Click Protect Sheet in the Changes group. A dialogue box is displayed.
- Toggle the desired options on or off.
- Specify a password if you choose.
- If you entered a password in the previous step, enter it again.
- Click OK (twice if necessary).
The pop-up message “The cell or chart you’re trying to alter is on a protected sheet” will now appear if you attempt to change the data in a locked cell. Unprotect the sheet in order to make changes. There might be a password entry requirement. The Format Cell option will also be disabled when you right-click on a worksheet cell. Importantly, the values in the unlocked cells can still be altered.
If you want to unlock the worksheet later simply navigate to the Review tab on the toolbar and choose the Unprotect Sheet option Once you’re done changing, return to the Review tab and choose Protect Sheet to stop further changes to locked cells before saving.
Conclusion
You can prevent unintentional editing by other users by locking cells in an Excel spreadsheet and safeguarding the worksheet. Take the Business Analytics with Excel course, which also includes Power BI training, to advance your analytics profession with strong new Microsoft Excel abilities.
In order to support data-driven decision-making, this business analytics certification course teaches you the fundamentals of data analysis and statistics. With the help of this course, you will become familiar with Power BI and the statistical ideas that will enable you to derive insights from data and display your findings using executive-level dashboards.
