USING IRONXL

How to Lock Cells in Excel: a Step by Step Tutorial

Updated February 24, 2022
Share:

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:

  1. Create a C# .NET or VB .NET project in Visual Studio.
  2. Download the DLL file of the IronXL library or install it via NuGet Packet Manager in Visual Studio.
  3. Apply for your License key using this tutorial.
  4. Use IronXL in your project.

    a. using IronXL;

  5. Load the Excel book using the WorkBook.Load() function.

    a. WorkBook wb = WorkBook.Load("fileaddress.xlsx");

  6. When the Excel file loads, select the sheet you want to lock using the GetWorkSheet() function.

    a. WorkSheet ws = wb.GetWorkSheet("SheetName");

  7. Next, protect the sheet using the ProtectSheet() function. Give the password as a parameter.

    a. ws.ProtectSheet("Password");

  8. 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.

How to Lock Cells in Excel: A Step-by-Step Tutorial, Figure 1: Navigate to Format Cells dialog in Excel Navigate to "Format Cells" dialog in Excel

A "Format Cells" section will open. Go to the Protection tab.

How to Lock Cells in Excel: A Step-by-Step Tutorial, Figure 2: Protection tab in Format Cells dialog Protection tab in "Format Cells" dialog

Check the Locked check box and click on the OK button.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 3: Lock cells option Lock cells option

Next, go to the Review tab and click the Protect Sheet option in the Protect group.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 4: Navigate to the Protect Sheet button in Excel Navigate to the "Protect Sheet" button in Excel

Then, enter a password to protect the sheet and click the OK button.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 5: Enter a password in the Protect Sheet dialog Enter a password in the Protect Sheet dialog

Next, re-enter the password to verify your password.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 6: Re-enter a 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.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 7: Error on editing a locked sheet in Excel 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.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 8: Format Cells dialog in Excel 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.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 9: Navigate to the Lock cell function 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.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 10: Lock cell in Format Cells dialog 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.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 11: Protect Sheet dialog and enter a password 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.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 12: Error on editing a locked sheet in Excel 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.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 14: Navigate to Format Cells function Navigate to Format Cells function

Go to the Protection tab, uncheck the 'Locked' option and click the OK button.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 15: Tick on the Locked option 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.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 16: Enable Formulas option in Go To Special dialogue box Enable "Formulas" option in "Go To Special" dialogue box

You will see that cells with formulas will be selected in the Excel sheet.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 17: Selected cells with formulas 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.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 18: Protect formula cells Protect formula cells

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 19: Confirm password dialogue box 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".

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 20: Navigate to Unprotect Sheet function Navigate to Unprotect Sheet function

In the "Unprotect Sheet" dialogue box, enter the correct password. The sheet will now be open to edits.

How to Lock Cells in Excel: a Step-by-Step Tutorial, Figure 21: Enter the password to unlock the sheet 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
< PREVIOUS
Reading CSV Files in C#: a Tutorial
NEXT >
How to Merge Cells in Excel: Step by Step Tutorial

Ready to get started? Version: 2024.7 just released

Free NuGet Download Total downloads: 883,975 View Licenses >
123