Okay, so we’ve explored a dataset and have some preliminary results to share with a team member. But our team member doesn’t use Excel much, and we don’t want them to accidentally overwrite a crucial formula.

Excel provides built-in tools for protecting different aspects of a spreadsheet. It is important to note that none of these will stop a malicious actor from accessing or altering data. All anyone has to do to circumvent protected sheets and cells is to open the file in another program like Google Sheets.

However, protected and hidden sheets are still useful for preventing accidental data overwrites by ourselves or by others, controlling input to a spreadsheet, and cleaning up a spreadsheet for presentation.

Three common options for protecting and hiding data in Excel are

• Protecting an entire sheet from editing.
• Unprotecting specific cells to allow user input or data updates.
• Hiding sheets to keep raw data and background calculations out of the way.

We’ve placed a slideshow in the Learning Environment illustrating how to do each of these in Excel.

Best practices for protecting and hiding ranges

Protected and hidden ranges can be very helpful. They can also be very frustrating. Pretty much every Excel user has a story of hours lost hunting for hidden data in a large spreadsheet. We recommend the following practices for avoiding these frustrations:

• Give hidden sheets meaningful names. Nobody wants to spend hours hunting for data hidden on Sheet45321.
• Highlight any unlocked cells in an otherwise protected sheet. Highlighting unlocked cells will both warn users that those cells can be overwritten and will also direct them to the cells they are supposed to change. For example, this is why we included “input your answer here” messages in the exercise spreadsheets for this course!
• Establish norms for spreadsheet hygiene with your team. If everyone follows the same conventions for protecting data, it will be easy to open and use each other’s spreadsheets!