Sunday, April 14, 2019

Security with Excel Workbooks

It's a great feeling when you've created your amazing spreadsheet! All the formula are working perfectly, the formatting is looking snazzy and those graphs are just populating nicely. You're feeling pretty pleased with yourself and then you remember that you will be sharing this workbook with others....

It's not that you don't like sharing, hey you're a great team player and being able to collaborate on a project is right up your alley. But there are those on the team that have the potential to ruin your good work! It's happened before and can be really frustrating. Why oh why can they not just do what they need to do in there and leave your precious formula alone. They don't need to touch it!

All is well though, we have a way to protect your worksheet and allow collaboration on the parts that we give permission to collaborate on. The good news is that we can Secure Portions of a Worksheet.

With the cells that we don't want touched (e.g. formula) we will use a process of unlocking the cells and then protecting the worksheet. This will allow the user to only enter values into the unlocked cells. By default every cell in a worksheet is locked. The only reason you can enter data into the cells, is because the worksheet isn't protected.

Steps:

  1. Select all the cells that users are allowed to enter data into. Right click and select Format Cells.
  2. Click the Protection tab and clear the Locked Checkbox and click OK.
  3. Click the Review tab and from the Changes group, select Protect Sheet
  4. Uncheck the Select Locked Cells checkbox
  5. Protect the worksheet by typing a Password. Click Ok and retype the password again. Click OK once more.
And you're done. Now you can sleep at night knowing that people aren't going to mess with your work!

For teaching resources on Microsoft Excel here are some links:

https://www.teacherspayteachers.com/Product/Microsoft-Excel-for-Beginners-Training-Manual-3186843