Updated January 9, 2024
How to Lock Cells in Excel: a Step by Step Tutorial
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 won'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.
Furthermore, IronXL also offers a wide range of features to interact with Excel WorkBook, WorkSheet and Cells level such as converting between popular formats, cell data formatting, merging cells, inserting math functions, and even managing charts and adding images.
To use it, you must import the library's DLL file or install it with NuGet Package Manager. Its free 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. If you decide that you like IronXL after reading the documentation, please make sure to buy it at an affordable price! Visit this licensing page for more information about licensing.
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.
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.
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.
Navigate to "Format Cells" dialog in Excel
A "Format Cells" section will open. Go to the Protection tab.
Protection tab in "Format Cells" dialog
Check the Locked check box and click on the OK button.
Lock cells option
Next, go to the Review tab and click the Protect Sheet option in the Protect group.
Navigate to the "Protect Sheet" button in Excel
Then, enter a password to protect the sheet and click the OK button.
Enter a password in the Protect Sheet dialog
Next, re-enter the password to verify your password.
Re-enter a 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.
Error on editing a locked sheet in Excel
Lock Specific 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.
Format Cells dialog in Excel
Go to the Protection tab, uncheck the 'Locked' option, and press the OK button. This will unlock cells in the Excel spreadsheet.
Navigate to the Lock cell function
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.
Lock cell in Format Cells dialog
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.
Protect Sheet dialog and enter a password
By doing this, your selected cells will be protected. When you try to edit these cells, you will get the error message.
Error on editing a locked sheet in Excel
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 clicking on the Format menu. Select "format cells" from the dropdown. The "Format Cells" menu will open.
Navigate to Format Cells function
Go to the Protection tab, uncheck the 'Locked' option and click the OK button.
Figure 15: Tick on the Locked option
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.
Enable "Formulas" option in "Go To Special" dialogue box
You will see that cells with formulas will be selected in the Excel sheet.
Selected cells with formulas
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.
Protect formula cells
Confirm password dialogue box
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 Sheet".
Navigate to Unprotect Sheet function
In the "Unprotect Sheet" dialogue box, enter the correct password. The sheet will now be open to edits.
Enter the password to unlock the sheet
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
