Last updated on December 6, 2021

How to lock cells in Excel and Jupyter notebooks

Posted by Felix Zumstein - Comments

Excel and Jupyter notebooks have a big overlap regarding their use case (analyzing data and showing some fancy charts/numbers), which means that you’ll find yourself occasionally looking for some “Excel functionality” in Jupyter notebooks. Recently, this happened to me when a Jupyter notebook user told me that they weren’t able to use their Jupyter notebooks with non-Python savvy users since the risk of them inadvertently changing the code cells was too high.

Now, in Excel, you should be afraid of the same (i.e., users changing formulas), but Excel has an option to lock cells to prevent exactly this: locked cells are an effective way to prevent end-users from changing a value or formula by accident. Turns out that Jupyter notebooks have the same functionality, but unfortunately, this functionality is a bit hidden and requires a few manual steps. In this tutorial, I’ll first look into how to protect cells in Excel before I’ll explain how the same works in a Jupyter notebook.

Table of Contents

  1. Locked cells in Excel workbooks
  2. Locked cells in Jupyter notebooks

Locked cells in Excel workbooks

By default, cells in Excel are locked. However, this will only come into effect once you go to the ribbon tab Home > Review and click on the Protect Sheet button:

locksheet

When you click that button, there’s a pop-up menu where you can change a few options and decide whether users need a password to unprotect the sheet or not. Once a sheet is protected, by default, none of the cells are editable anymore and you’ll get the following error whenever you try to edit one:

locksheet

That’s usually not what you want: to leave some cells editable for the user to provide the inputs, unprotect the sheet again (the Protect Sheet button from before has changed its label to Unprotect Sheet), then right-click a cell and select Format Cells. On the Protection tab, uncheck the Locked checkbox:

locksheet

Once you have removed that checkbox on all cells that should remain editable, go back to Home > Review and protect the sheet again by clicking the Protect Sheet button. After this refresher on how to lock cells in Excel, let’s see how it’s done in a Jupyter notebook!

Locked cells in Jupyter notebooks

Unfortunately, Jupyter notebooks don’t have an easy way of locking cells at the moment, but there’s some discussion about it on their issue tracker. To lock a cell from being changed or deleted, you’ll need to edit the cell’s metadata. Let’s see how it’s done!

  1. In the menu, click on View > Cell Toolbar > Edit Metadata:

    locksheet

  2. This will add the Edit Metadata button to every cell. Click the Edit Metadata button of the cell that you want to protect:

    locksheet

  3. A pop-up with an empty JSON string appears. Edit it as follows:

    locksheet

    To allow you to copy/paste the text, here it is again:

    {
      "editable": false,
      "deletable": false
    }
    

    To confirm, hit the Edit button and exit the pop-up. These cells are now still runnable but can’t be edited or deleted anymore.

  4. Once you’re done editing all the cells that you want to lock, disable the Edit Metadata again by going to View > Cell Toolbar, and selecting None.

Note: Since Jupyter notebooks—unlike Excel—don’t show an error when you try to edit or delete a protected cell, it’s probably a good idea to make a comment in the cell explaining that it is protected.

xlwings Newsletter

We help you excel with Microsoft Excel, see Past Issues.