Many Excel users don’t realise that they can leave a trail of evidence in a workbook, showing where the content comes from. In this particular case there was evidence suggesting that content in the templates had been taken from 14 different projects, five advisory firms and from five different countries.
There is a lot you can learn from a little ‘forensic examination’ of an Excel workbook about who has been involved in its creation and where some of its calculations may have originated. If you are sending out workbooks, a little ‘house-keeping’ can tidy up your workbook, removing this information.
The most obvious place to look is the workbook properties. These can be found in the ‘backstage’ view – which you can see by clicking the File tab in Excel 2010 or the Office button in Excel 2007. The Author will be displayed (unless this has been removed by the author – more on this later)
Styles in Excel
There are 47 styles (including 5 number format styles) by default in a new workbook. In common with many professional firms, Numeritas has a suite of tools to help us build models efficiently. Part of this involves using “styles” for consistency amongst other things (to find out more about styles, watch our video blog on the subject). Most firms include their name or some identifier in the style name and these are clearly visible in the styles gallery on the Home tab of the ribbon. In versions of Excel up to 2010, copying a sheet from one workbook to another, will copy all the styles in the origin workbook to the destination workbook. This can lead to large numbers of styles in a workbook.
A useful feature in Excel is the ability to put comments on particular cells (on the Review tab of the ribbon). When you do so, your Excel user name will be inserted in bold at the top of the comment box. Even if you delete this, it is possible to find the user name using VBA.
If a workbook contains any recorded macros, the Excel user name of the person who recorded the macro is stored at the top of the VBA code. This can be accessed using the Alt+F11 keystroke combination and viewing the modules in the VBA project for the workbook.
There are many things that can be hidden in Excel and it is easy to inadvertently leave information in a hidden area that you would prefer not to be issued. The more obvious things that can be hidden are Rows, Columns, Worksheets and Chartsheets (both of which can be VeryHidden in the Visual Basic Editor so that it doesn’t show up when you use unhide in the format menu).
Objects can be formatted so that they are not visible and can contain text.
Text can be formatted so that it is ‘invisible’ by being the same colour as the cell background.
Ranges of cells can be named in Excel and again, because you can include an identifier in a name it could provide information about its origins.
There are a number of reasons for avoiding external links, but in the context of this blog, the path and filename of an external link can give away a surprising amount of information. External links can also be embedded in named ranges.
How important is all this?
There are legitimate reasons why you would want this sort of information to be in a workbook, and legitimate reasons why there might be third party styles or other legacy information in your workbook (if you have worked on someone else’s file or they have worked on yours for example). There may however be reasons you don’t want this sort of information to be passed on to other users of your file.
Removing the traces
Excel has a facility for removing some of this information (including the author’s name). To do this, switch to backstage view (Windows button in Excel 2007 or File tab in Excel 2010) and find the “Check for Issues” button. Clicking this will look for and (optionally) delete some of the hidden information in your workbooks. This however won’t catch all the possible hidden trails listed above. Knowing where information can be hidden as described above will enable you to clean up your workbook more thoroughly.
If nothing else, looking for the above tell-tale signs will tell you a lot about the history of a workbook you