Why Document an Entire Excel Workbook
You already know the story…. or do you…..?
So the internal accountant produces a monthly pack for management, which contains financial information. These reports are fantastic. Detailed Sales reports show sales against budget, against prior year, as a 12 month rolling total. They are also further sliced against sales rep and regions. Working capital reports highlight results against KPIs, and so on. Over time management has requested some changes here and there, but month on month, this pack is produced on time by the accountant. The management team see this reporting pack as critical to operations. So critical that they leave it in the hands of the accountant to, well let’s just say, get it done!
And then the accountant leaves.
No problem right. A new accountant will just continue on!
Sure they will…..but….
If you have no Passover on spreadsheets and workbooks like these, then the learning curve might be very steep. Trying to figure out complex workbooks created by someone else can be a nightmare, causing delays in reporting, and even inaccurate and incomplete reports. Unless of course you have a spreadsheet succession policy…..which companies don’t seem to have!!
Look Excel spreadsheets are rampant in companies of all sizes. Used for everything from quick calculations to complex financial models. There also seems to be a tendency in both large and small companies to reused key workbooks again and again.
These key workbooks tend not to change much in terms of structure and format. They have been developed to report on something, or to track something.
So why don’t companies bother to make sure simple steps are taken?
Steps such as documenting key workbooks?
There are many other reasons why you should document your workbook. My example of one accountant taking over from another is very common, something I have experienced myself.
I have also been misfortunate enough to be asked to re-create a spreadsheet from a hard copy because the excel file was corrupt……Sh*t happens right!
Microsoft have made it really simple for us to document an entire workbook. So simple I know that someone reading this will kick themselves.
Meet Spreadsheet Inquire. Spreadsheet Inquire is an ad in available for Office 2013 & 2016 Professional Plus. It’s a nifty little ad in that will allow you fully document any workbook with a few clicks of a mouse button.
If you don’t see the Inquire tab, you just need to turn on the ad in. To do this, go to File, then Options, then Add in’s. You then need to select COM add in’s in the Manage drop down box. This will bring you to a dialogue box with all the COM add in’s available. Select Inquire and then okay. The inquire tab should now be available.
Documenting the workbook is very simple.
First select Workbook analysis from the Inquire tab. This may take a few seconds depending on the size and complexity of your workbook.
This will then open a Workbook Analysis Report dialogue box. On the left of this dialogue box you will find all of the items with a tick box and also a count. You can chose to select one, multiple or all of these items for your report. I don’t normally bother with items that have no count. Once the items are selected, the next step is to simple press Excel Export.
Such simple steps really, to protect critical reporting systems in your organisation.
In the video below you will see how to turn on the Inquire Excel ad in and how to run and download the workbook analysis report.
Don’t forget to give this video the thumbs up