How secure are your spreadsheets?

Before I start this blog, I need to make it clear what I am talking about. Spreadsheets are merely documents, so their overall security is mostly dependant on where they are hosted. I’m not talking about that. I’m talking about when you hide some columns of data, lock the worksheet, and then send the spreadsheet to someone, assuming that the hidden information is safe and secure. It has come to my attention, of late, that some people assume that this is secure. It is not 100% secure. Here are some steps that you can take to make your spreadsheets more secure (never 100%), as well as some things you can do to make sure that your sensitive data doesn’t fall into the wrong hands.

How to make your spreadsheets more secure

Even though you can never make the content in a spreadsheet 100% secure from those who have access to the spreadsheet, there are a few ways to make it more secure.

1. Hide the sensitive data, and then lock the worksheet, making sure you have not given permission to unhide the hidden data. This is the absolute bare minimum. If you don’t do this, you may as well just post the sensitive data on social media. Also check the settings of the cells involved. The default is set to lock cells, but not hide the formula. Locking the spreadsheet only enforces the preferred settings, so make sure the cells are formatted correctly.

2. Use a different password for ‘Protect Sheet’ and ‘Protect Workbook’, and put the sensitive data on a hidden, locked tab. Hacking a Protected Sheet password is often easier that hacking a Protect Workbook password. If you make them different, it at least delays the hacker.

3. In the Visual Basic section of the Developer tab (which you may have to add), there is an obscure setting to password-protect the VBA side of Excel (VBA Project Properties). If you password-protect this, people can’t use macros (well not easily anyway) to unlock the spreadsheet. This may not stop top hackers, but it will throw a huge spanner in the works for the casual hackers who have googled how to unlock a spreadsheet.

If you do these 3 things, you will stop many attempted hacks to get to the data in your spreadsheets, at least the casual ones. If you come up against someone who knows what they are doing, they can often bypass all of this. Are there other ways to have multiple users on spreadsheets without compromising your sensitive data? Yes, there are, but it depends on what you need to achieve. Here are a few suggestions which I have done for my clients.

Don’t have your sensitive data on the shared spreadsheet

This seems like an obvious comment to make, and many of you are thinking that you need the data there to provide the spreadsheet formulas with the right information. There are often ways around that; here are some examples.

1. Do they need a spreadsheet or will a PDF do?

I often see people sending a spreadsheet to others, and when I ask why they needed the spreadsheet, I was informed that they need to see the figures. They don’t change the anything they just need to see what is going on. Why send them the spreadsheet then, why not just a PDF? If you look at the spreadsheets in our Basic Range, they almost all have a report page, which can be saved as a PDF and sent out as required. No formulas, no hidden data, only what you want the person to see.

2. Export the required data to another spreadsheet.

We do this often. We have done some price calculator spreadsheets, where our clients can send the spreadsheet to their clients, who can adjust the products or services required, to see the quoted price. In some cases, our clients have used extremely valuable data to work out the prices. They did not want anyone seeing this data. I created an internal spreadsheet for them to use, which would work out the price per section per day. It would have all the calculations and setup data. I then made a second spreadsheet which would go out to the client. Once the first spreadsheet was completed, a simple copy and paste (values) of one block of data would completely populate the second spreadsheet. This would convert the sensitive figures to a simple day rate. The client could use this spreadsheet to state which days they required the service, which would calculate the price. If they did hack into that spreadsheet, they only saw the day rate (which was shown anyway), rather than the broken-down charges that my client wanted to keep a secret. They can’t see those figures, as they are not even on that spreadsheet.

3. Collection sheets.

People often send spreadsheet to collect data, but then they have all the formulas (often sought-after formulas) on the same spreadsheet. I have a client who collects weekly working hours (and jobs done) per staff member, so I made a sheet to collect the data. There is another master sheet in the office, where you can copy and paste (again, values) from the collection sheet to the master sheet, which performs the calculations. The important formulas don’t leave the office, so if the collection sheets are hacked, there is no sensitive data to be found.

4. Two-way interactive sheets.

These are rather useful, as they do points 2 and 3. You can set up using the master spreadsheet to create the exportable data and send it to another sheet that can get sent out to whoever needs to complete it. When you get the sheet(s) back, simply transfer the collected data back to the master spreadsheet, and your work is done. This is perfectly illustrated in our 360 Staff Appraisal product. You set up the questions, points and possible answers, transfer the data to sheets, to send to the people completing them, then collaborate all the returned data back to the master copy. That then generates the reports. So, if someone hacks into your spreadsheet, they don’t see all your data, scores, other people’s scores, staff details, etc. They just see what you deem safe for them to see.

I hope this has given you some ideas, or at least just made you aware of the data you are sending out. I love spreadsheets, and use them for everything, but I am aware that they are not 100% secure. I take every precaution to make sure that any sensitive data is protected, and that your spreadsheets work as safely as possible within your required process. As always, if you would like to discuss any bespoke solutions or have me review any of your spreadsheets, please get in touch.

Richard