Have you ever worked for hours on a document or spreadsheet, only to find your work deleted or data modified, much to your chagrin, while you were away on a coffee break? Well, it is safe to say that each of us has at some point in our lives encountered a similar situation and had that “annoying someone” in the form of a friend, coworker, child, or even a pet who was found to be the culprit in such circumstances. How we wish we had protected our documents better! Most of the time, it is an unintended keystroke that causes the damage, resulting in the loss of valuable information that, in some cases, cannot be recovered. Such scenarios can cause a great deal of frustration because one might need to spend more time and effort fixing things or, worse yet, start over if all the data is lost.
As a result, it is critical that you learn the preventive measures to protect your document from accidental deletion or modification so that all your hard work does not go to waste. Fortunately, Microsoft Excel offers the ability to lock cells and protect documents, preventing such unfortunate events. So, the next time you share your spreadsheet with someone, you will know what to do if an accidental keystroke occurs! In this article, we will look in depth at the best way to lock cells in excel and protect them.
Excel is one of the most well-known programs made by Microsoft, which helps us to organize our data in spreadsheets and includes extra features like calculations, the use of mathematical formulas, graphs, and tables, to name a few, that make it easier and faster for us to make sense of data.
Locking cells in Excel
By locking cells in an Excel spreadsheet, we can safeguard our data from accidental deletion and changes made by unauthorized entities that could jeopardize all our hard work. Along with saving our documents, protecting our Excel sheets by locking the cells is a critical step that must be taken, particularly in a work environment. There are methods by which we can lock all the cells in an excel sheet or even choose to lock only specific cells in the sheet. The latter tip is especially useful when we need to share our spreadsheets with coworkers who may be authorized to make certain data changes, particularly when working on a project together.
How to lock all the cells in Excel?
When you lock all the cells in an excel spreadsheet, it is impossible for anyone to make changes to it. Now let us look at the steps involved in locking the cells of an excel sheet.
- Choose the cells you want to lock.
- Select the home tab, followed by the format option on the right-hand side of your screen.
- Scroll down to the ‘protection’ section and look for the ‘lock cells.’ Simply click on it.
Even if you have locked all the cells, you can still make changes to them. As a result, you must password-protect your spreadsheet to prevent further changes to the cells.
How to protect the Excel sheet with a password?
- Navigate to the review tab.
- Click on protect sheet.
- 3. A pop-up window will appear, prompting you to enter a password of your choosing.
- Check the boxes of actions that you want to allow the user to perform on the sheet, and then click OK.
- A pop-up window will appear asking you to re-enter the password. Once you re-enter the password, click OK.
- Once the sheet has been password protected, look for the ‘unprotect sheet’ icon in the Review tab to confirm.
- Once the sheet is password-protected, only the users who have the password can edit or change the cells. If you try to make any changes to the sheet, an alert message will appear asking you to unprotect it by entering the password, indicating that the sheet is password protected.
How to lock only specific cells in the worksheet?
There will be situations when you want to protect specific cells from any edits or changes while allowing other users to make changes in the rest of the worksheet. By default, all the cells are locked when the sheet is password protected. However, it is possible to lock only specific cells in the sheet. The steps that follow will show us how to accomplish this.
- When you protect the sheet, all cells are locked by default. So, if you need to lock only specific cells, first you need to unlock all the cells.
- Select all the cells.
- Right-click the selection and select ‘Format cells.’ Click on the protection tab.
- Uncheck the locked check box and then click OK.
- Next, select the specific cells that you wish to protect.
- Right-click on the selected cells and click Format Cells.
- On the protection tab, check the locked checkbox and then click OK.
- Next, go to the Review tab and click on Protect sheet and enter your desired password.
- Only the selected cells are now password protected, and the rest of the sheet can be edited. Edits in specific cells are not possible unless the user unprotects the sheet by entering the correct password.
- Protect the sheet by following step 8; otherwise, locking or unlocking specific cells by right-clicking on the format cells will have no effect.
How to protect formulas in an Excel sheet?
Your datasheet may occasionally contain formulas that need to be protected so that no other user can make changes to them that would tamper with the data in the sheet. To accomplish this, the following steps must be taken.
- Navigate to the Home tab, where you will find a magnifying glass icon which is ‘Find & Select.’ Click on the icon.
- Scroll down to ‘Go to special’ and click on it.
- Select formulas and click OK. All the formulas in the excel sheet will be selected.
- Now, right-click on the selected formulas and go to the Protection tab.
- Mark the locked check box and click OK.
- Finally, in the Review tab, click on ‘protect sheet’ and enter the password to protect the formulas.
- All the formulas in the excel sheet are now password protected and no edits cannot be made to them.
Thus, implementing the aforementioned safeguards for your excel sheet will ensure that you can take that much-needed coffee break after hours of toiling away on the worksheet with no worries in the world.
With Business Analytics with Excel, which includes Power BI training, you will be able to jumpstart your analytics career with powerful new Microsoft Excel skills. As a part of this Business Analytics certification course, you will learn about data analysis and statistics as well as how to make informed decisions based on data. Power BI training will introduce you to the software and provide you with a thorough understanding of statistical concepts that will assist you in devising insights from data to present your findings using executive level dashboards.
