How To Stop Data Leaks In Excel (8 MUST-KNOW EXCEL TIPS!)
The shocking and sad story of the Afghan data leak has been all over the news recently, and, unfortunately, Excel spreadsheets are central to the narrative. A government employee emailed an Excel file thinking it contained 150 sets of personal details. It actually contained over 18 000. The file was subsequently ‘leaked’ with very serious consequences for the people involved.
Surely, we can do better. How can we avoid this kind of Excel data catastrophe?
WATCH THE VIDEO Here
In this video, I share the exact method I use to understand EXACTLY what data is in a file, and what isn’t. It’s what I do before sharing any sensitive data. From uncovering hidden (and very hidden) sheets and rows, to tips and tricks using the selection pane and Excel VBA editor, we work through the process to ensure Excel data leak risk is minimised.
You can apply this method from today in your work and, as usual, the Excel download files are available to work along with me.
Perhaps more importantly, we deal with the question: should you share data like this in Excel at all? Files attached to emails are easily downloaded and forwarded on, meaning you effectively lose control of the data, which can have dire consequences. At the end of the video, I share the actual approach I would use to share sensitive data – no attaching Excel files to emails.
00:00 AFGHAN DATA LEAK IN EXCEL
01:32 Hidden / Very Hidden Sheets
If I were asked to look at an Excel file with sensitive data, the first question I would ask is, ‘How many worksheets are in this file?’ You might say ‘That’s easy!’ – we just count the sheet tabs. But, as we see, there’s more to it than that. We can only truly understand the number worksheets in an Excel file via the VBA editor, as I demonstrate in the video.
03:25 Filtered Data
Filtered data is a common source of confusion in Excel and it’s my guess that filters were involved in the Afghan data leak. In the video, I show you how to spot filters and quickly remove them. And don’t forget, with the range of functionality available in Excel, it’s possible to avoid filters altogether.
04:48 Hidden Rows And Columns
It’s essential to check for hidden rows and columns beyond filtered data. In the video, I show you how to unhide rows and columns quickly across a whole worksheet.
06:09 Missed Data
A spreadsheet typically houses a single ‘body’ of data, and this seems to have been the case in the Afghan data leak. But, how can you be sure a worksheet only contains your main dataset? And, with more than a million rows, how can you quickly check? In the video, I share practical Excel tips and tricks to get this done quickly.
08:28 The Selection Pane
Data doesn’t just exist in cells! Data could be stored in a chart, shape, on a button or a number of other ‘objects’ in a worksheet. Moreover, any object could be ‘hidden’ from view. The selection pane allows us to quickly view all objects on a spreadsheet, including the hidden ones, and I show you how to access and manipulate it in the video.
09:23 Queries
Next, it’s time to check if the worksheet contains data connections (or ‘queries’) to other files. Power Query is the most common source of this kind of connection, but professionals often use external data services to quickly access information such as currency exchange rates. It’s essential to identify and ‘break’ or delete these connections before we go further.
10:15 The VBA Editor
Next, back to the VBA editor! The VBA editor could be used to store data, or even generate it via a simple algorithm. For peace of mind, I recommend deleting the VBA project altogether by saving the file as .xlsx – as I demonstrate in the video.
11:28 Excel Data Sharing Solution
In the final part of the video, I share the actual way I would share sensitive information using Excel. It’s easy, relatively secure, allows you to retain a degree of ‘control’ of the data, and doesn’t use desktop Excel files at all. I hope you’ll take this approach forward in your work.
But what have I missed? What Excel tips and tricks do you use to avoid data leaks? I would love to hear your ideas, please share them with the community in the YouTube comments!
WATCH NEXT