How to Lock Cells in Excel using Python 🔒 (by Cell Color, Font Style, Formula or Cell Value)

Introduction

In this post, I will guide you through the process of locking Excel cells using Python. This method focuses on how to lock cells based on their fill color, font style, formula, or cell value. Locking cells is particularly useful for protecting your spreadsheet from unwanted edits, ensuring that important data remains intact.

For demonstration purposes, I have created a Jupyter notebook, but I will also upload a Python file to GitHub so that you can easily copy and paste the code. Note that this method is specifically designed for Windows, as it utilizes the pywin32 package.

Coding Out the Solution

To get started, you need to install the required dependency to interact with Excel. Open your terminal or command prompt, type pip install pywin32, and hit enter.

Once installed, you can import the ‘win32.com client’ and also import Path from the pathlib module to handle file paths. The pathlib module is standard in Python, so no additional installation is needed.

Next, let’s locate our current working directory. For this example, the Excel file I want to manipulate is in the same folder as my Jupyter notebook. To find the current working directory, use the command Path.cwd(). However, be cautious when running this code from a standard Python file, as it might lead to errors if your current working directory is different.

A safer option would be to access the parent folder of your Python file. This ensures that we correctly locate the file path to the workbook without overwriting the original workbook. I will create an output path by concatenating the original file name with ‘_locked’ and maintaining the same file suffix, .xlsx.

Now that our file paths are set, let’s open the workbook. First, I will launch the Excel application. You can choose to set the visibility to false, which will run Excel in the background.

With the Excel instance active, I can now load our workbook using the defined file path. In this example workbook, I have a few yellow input fields for creating a Waterfall chart. The goal is to lock all cells that are not yellow.

I created a helper function to identify the exact color by taking a tuple of RGB values and converting them to integers. For instance, clicking on a yellow cell and navigating to Font and ‘more colors’ allows me to see the RGB value.

Next, I define a password to unlock the sheet later. I iterate over each sheet in the workbook, defining the last row and column of the used range. Before making any modifications, it’s crucial to unprotect the worksheet first, as I plan to execute this cell multiple times.

After unprotecting the sheet, I loop through all cells to check if they meet certain conditions. For example, if a cell has a bold font, I set ‘locked’ to false. Otherwise, I lock the cell.

Once I’ve checked all cells, I protect the worksheet with the specified password. Before executing the notebook, I will demonstrate that I can currently change every cell in my worksheet.

After running the Jupyter notebook, I can only edit the cells that contain a bold font, such as ‘Input Fields’ or ‘EBIT 2021’. All other cells are now locked, and attempting to edit them will prompt a warning message.

We can also check other conditions. For example, if a cell’s value is “Input Fields”, I want to unlock that cell. After executing this condition, I can only change the specified cell; all others remain locked.

Additionally, I can lock any cell containing a formula. After running the notebook, I can still edit every cell except for those with formulas, like cell B11.

Lastly, I want to unlock all cells with a yellow fill color. Executing this condition will allow editing only of yellow input fields, while all other cells remain locked.

To finish, I will save and close the workbook using the output path for the filename. I chose not to save changes to the current workbook by using ‘false’ when closing it.

After executing this, I can see the new workbook in my folder. Opening it, I verify that only the yellow input fields are editable, while all other cells are locked.

To unprotect the sheet, navigate to Review, then click ‘unprotect sheet’ and enter the password to regain edit access to all cells.

Conclusion

This tutorial covered how to lock cells in Excel using Python, specifically by cell color, font style, formula, or cell value. By following the steps outlined, you can effectively protect your spreadsheets from unwanted edits. Remember, the method discussed is applicable for Windows and requires the pywin32 package.

Thanks for reading, and I hope you found this information helpful!

Further Links

• Source Code: https://github.com/Sven-Bo/lock-excel-cells-python