Published February 25, 2022
How to Lock Cells in Excel: a Step by Step Tutorial
Microsoft Excel is an incredible tool for any business. It allows users to create and manipulate data with a spreadsheet program that can be used in various ways. These include organizing information, tracking budgets, and tracking progress on projects.
People use Excel for keeping their records, essential data, and confidential information about their projects. It is often therefore necessary to make sure our Excel files are secure. If we want to make the data available as 'read-only' or we wish to secure functions that we have written into our excel documents, then it's made possible by locking these important cells in Excel.
By locking cells, people will not be able to edit or customize them. You can also hide the essential function of cells. This measure is beneficial for confidentiality. You can lock a cell in Microsoft Excel by entering a password. Once you've entered a password, you can protect the cell from being edited. A protected cell cannot be edited. You can also secure a cell by hiding the formulas within it. However, the password must be entered in the Confirm Password Dialog Box in order to access locked cells. Otherwise, cells will remain locked.
Many people find it challenging to understand the procedure for locking cells in Excel. We are here to help. This article will show you how to lock cells in an Excel file easily, one step at a time. You will be able to do it like a pro by the end of this article, so let's get started.
IronXL: The C# Excel Library
IronXL is a library for C# that lets you work with Excel without installing Excel on your system. This is a significant benefit as you won't need access to administrative rights and don't have to worry about interfering with the user's machine. If there are still any doubts about this library, we recommend reading our datasheet. To create a new Excel file and format the data according to your needs, you can use the IronXL C# library. This is a go-to package for all Excel operations, meaning that it provides complete functionality. If you're building any software and need to edit or create an excel file, you can use the IronXL library to do this quickly.
In order to use it, you must import the library's DLL file or install it with NuGet Package Manager. Its 30-day trial version can be used to test and ensure that all features function as intended. The trial version is free and does not expire until the end of 30 days. When you purchase IronXL, you will need to apply for the license key. To learn how to do this, click the link in this sentence — it will take you to a tutorial explaining what to do. If you decide that you like IronXL after reading the documentation, please make sure to buy it at an affordable price!
Let's see how we can protect an Excel sheet using the IronXL C# Library:
- Create a C# .NET or VB .NET project in Visual Studio.-
- Download the DLL file of the IronXL library or install it via NuGet Packet Manager in Visual Studio.
- Apply for your License key using this tutorial.
Use IronXL in your project.
a. using IronXL;
Load the Excel book using the WorkBook.Load() function.
a. WorkBook wb = WorkBook.Load("fileaddress.xlsx");
When the Excel file loads, select the sheet you want to lock using the GetWorkSheet() function.
a. WorkSheet ws = wb.GetWorkSheet("SheetName");
Next, protect the sheet using the ProtectSheet() function. Give the password as a parameter.
a. ws.ProtectSheet("Password");
The sheet is protected, and we can now save the file using the Save() function.
a. wb.Save();
You have now seen how easy it is to protect cells in Excel using the IronXL library, given a working knowledge of C#. You can also benefit from multiple tutorials on Excel operations in the IronXL official documentation.
You can download a file project from this link.
Lock Cells in Microsoft Excel
The lock cell function is a protection feature in Microsoft Excel that allows you to lock cells so that they can not be edited. Let's look at how we can lock cells in Microsoft Excel.
1) Protect the Entire Sheet
Sometimes you have to give your document to someone else for read-only purposes. You can do this by protecting the sheet with a password. Let's see how we can do this:
Open your file in Microsoft Excel.
Go to the Home tab and click on the dialogue box launcher icon in the Alignment group.
A "Format Cells" section will open. Go to the Protection tab.
Check the Locked check box and click on the OK button.
Next, go to the Review tab and click the Protect Sheet option in the Protect group.
Then, enter a password to protect the sheet and click the OK button.
Next, re-enter the password to verify your password.
After following these steps, your sheet will be completely protected. When someone tries to edit any cell in an Excel file, they will get an error prompt.
Lock Specfic Cells
Sometimes you will need to protect specific cells with data or formulas. If so, unprotect the rest of the spreadsheet before protecting those essential cells. Let's look at how we can lock specific cells in Excel.
Open your Excel file in Microsoft Excel.
Select the whole sheet and press Ctrl + 1 to open the "Format Cells" dialogue box. The "Format Cells" dialogue box will open.
Go to the Protection tab, uncheck the 'Locked' option, and press the OK button. This will unlock cells in the Excel spreadsheet.
Next, select the cells you want to protect and open the cell dialogue box using the Ctrl+1 keyboard shortcut key. Go to the Protection tab, check the locked option, and click the OK button.
Go to the review tab and click "protect the sheet" from Protect Group. The protection dialogue box will open. Check the option "Protect worksheet and content of the cell." Enter the password to protect it.
By doing this, your selected cells will be protected. When you try to edit these cells, you will get the error message.
Lock Formula Cells
You can choose to lock only formula cells to protect your calculations in Excel. Follow these steps to lock formula cells in Excel:
Open your Excel file.
Unlock the whole sheet by selecting all the cells, going to the cell group, and click on the Format menu. Select "format cells" from the dropdown. The "Format Cells" menu will open.
Go to the Protection tab, uncheck the 'Locked' option and click the OK button.
This will unlock the whole sheet. Next, find the formula cells. Go to the Home tab and click on the Find and Select menu. From the drop-down, select the "Go to special" menu.
From the "Go to Special" dialogue box, select Formulas and press the OK button.
You will see that cells with formulas will be selected in the Excel sheet.
With these cells selected, go to the review tab and click "protect sheet". Enter the password to protect and re-enter the password to confirm.
The formula cells will now be protected. No one will be able to edit these cells without entering the correct password.
Unprotect Sheet
Follow these steps to enable editing:
Select the whole sheet. Go to the review tab and click "unprotect the sheet".
In the "unprotect sheet" dialogue box, enter the correct password. The sheet will now be open to edits.
Conclusion: The Benefits of Locked Cells vs. Unlocked Cells
There are clearly benefits to locking cells, but the level of security you need depends on what you are using the cells for.
The benefits of locking cells include:
- Preventing access to unauthorized users (e.g. children)
- Preventing people from tampering with an item
- Controlling the environment in which items are stored
The drawbacks of locking cells include:
- Costs of purchasing and installing locks
- Difficulties in accessing items when they need to be retrieved (e.g. when a file has gone missing)
- Difficulty in moving items within a facility
