Diligence for Excel
Online User Manual
Table of Contents (ToC) Function
For Diligence v2.4
This online PDF manual is best navigated by clicking the relevant Contents Index item below which will take you directly to the chapter of interest, or alternatively finding the topic required by pressing Ctrl-F and entering the appropriate text at the bottom of the screen.
The Full Diligence for Excel Manual is available in PDF format HERE
Copyright 2016. DiligenceForExcel.com. All rights reserved.
This manual may not be copied in whole or part without the express prior written permission of the copyright holder.
Contents
The Table of Contents (ToC) Worksheet
Installing a Table of Contents
Understanding the ToC’s Automated Fields
Links & References to Other Files or Data
Completing the ToC’s User-Defined Sections
Automatic Recording of Structural Changes
Table of Contents Userform Options
Option to Record Structural Changes
Location of the Table of Contents Worksheet
Removing the Table of Contents Worksheet
Formulae Referencing Other Workbook: Open vs. Closed Files
Tests for Non‑Compiled Workbooks and File Corruption
Table of Contents: Further Reading & Associated Articles
Workbook, Worksheet & Formula Complexity Algorithms
Typical workbook complexity scores
The Table of Contents (ToC) Worksheet
Installing a Table of Contents
A Table of Contents (ToC) can be installed onto any Excel file by pressing the ‘Table of Contents’ button on the Diligence ribbon, and then pressing ‘OK’ from the resultant userform. If no ToC currently exists then one will be added, and if a ToC already exists then it will be updated.
 |
Simple ToC Example
Below is an example of a Table of Contents worksheet produced for an empty workbook.
The grey coloured cells are automatically created and populated by Diligence; they describe the physical contents and attributes of the workbook.
The lemon coloured cells are user-defined fields which can (optionally) be populated by the user to aid with compliance and documentation.
The ‘Structural History’ section at the very bottom of the sheet is a placeholder for the list of structural changes to the file which will be automatically recorded each time the ToC is refreshed.
It is important to consider that the ToC is a snapshot of the workbook contents at the time the ToC was created or last run.
Changes made since the ToC was last updated will not be reflected in the automated fields of the ToC until it is next updated.
Understanding the ToC’s Automated Fields
Much of the ToC is created or updated automatically, based on the contents of the file, the methods by which it reaches out to other files or programs and the file’s complexity or features. These fields are coloured grey or white and are not editable by the user.
File Complexity
This is a number between 0 (empty workbook) and 10 (highly complex workbook). The key driver for the file complexity score is the complexity of the individual worksheets, but other factors such as the number of hidden sheets, links, references and macro code are also taken into account. More details on how the figure is calculated and typical scores can be found in the ‘Further Reading’ section of this chapter.
Visible Worksheets
This section lists all the visible worksheets within the file and has several fields per sheet …
 |
Sheet Name
As specified on the worksheet’s tab. The text is also a hyperlink. Users can click the worksheet name and jump straight to that sheet.
Complexity Score
A figure between 1 and 10 representing the overall complexity of the worksheet, where 1 is the least complex and 10 the most complex. A cell note (the red marker in the top right hand corner of the cell) indicates items of interest that have contributed to the complexity score, such as complicated formulas and links to other worksheets.
Empty worksheets do not have a complexity score.
See the ‘Further Reading’ section of this chapter for a full list of the factors used to calculate the Worksheet Complexity score.
Any worksheets found to have a significant contributory factor to its complexity will have a cell note. This may be useful for a user to ascertain immediate areas of concern
Typical example of (fairly complicated) automated Sheet complexity score cell note …
Any cell note will contain one or more of the following comments …
· A list of the Excel functions and any User Defined Functions (UDFs) used in the sheet. If there are complex formula, then the most complex formula with its complexity score is included. An example of where the formula can be found is indicated.
· Where nested formulae are used, these are shown in addition to the formulae, for example IF(, SUM(, IF(SUM))) but, for brevity these details do not contain the formulas themselves, just the Excel function used. E.g. SUM() not SUM($A1:$A2)
· Where combined or compound formulae are used, up to 5 (the most complex) are displayed.
· If there is more than three ‘ranges’ of hidden rows or columns
· If there are more than 10 contiguous ranges of data
Formulae
Ticked if the worksheet contains formulae.
If any formulas evaluate to an error, or design issues or anomalies are found the tick is coloured red, and a cell note indicates areas of interest, for example ....
Any cell note will contain one or more of the following comments …
· Cells that evaluate to an error (#N/A, #DIV/0!, #NAME?, #REF, #VALUE!, #NULL!, #NUM!) are highlighted and the sheet name is displayed in red.
· Worksheet Design, Anomalies & Cell Contents
· Inconsistent Formulae, such as if a formula cell is sandwiched between two cells with the same formula.
· Numbers Formatted as Text
· Number in a Formula. If a formula contains both a function AND a number (E.g. Sum(A:A)*1.2 ) this is considered to be bad practice (The number 1.2 should ideally be in its own cell)
· Duplicated Formula: If a series of cells contain two formulae that are precisely the same, e.g. SUM(A:A) this is highlighted. If they should be the same, best practice suggests one should refer to the other rather than both be calculated.
· Last Rows or Columns in a Pre-2007-Excel Spreadsheet: Formulae calculating cell references on or approaching the limitations of old format Excel files (256 columns, 65536 rows) are highlighted in case those formulae need updating.
· External References and Personal Network Drives
· Cells that contain links to other files. Special notice is taken of files located on a ‘C:\’ drive, and corporate users can specify an additional drive (E.g. ‘G:\’ or ‘H:\Personal’) which will also be highlighted in the links section as being potentially unavailable to all users.
Formulae Protected
This field can contain of three options …
No (None of the formulas are protected)
X of X E.g. 45 of 100, some of the formulas are protected.
Yes All formulas on the worksheet are protected.
No differentiation is made as to whether the protection includes a password.
The field on this row is left blank if the sheet contains no formulas.
Size
The number of rows by the number of columns, defined by the top left populated cell to the bottom right populated cell. If applicable a cell note indicates how many rows and/or columns are hidden or zero height/width.
Hidden Worksheets
This section is identical in content to the Visible worksheets section above, but no hyperlink is included on the worksheet name (i.e. you cannot navigate to the worksheet)
This list will also include ‘Very Hidden’ worksheets (hidden via the VB Editor or code) indicated by a cell note.
Links & References to Other Files or Data
Dependencies on other program files and/or workbooks and/or data tables are listed in this section, starting with a title that includes the total number of links and references.
Where the dependency is defined as a programmatic reference, such as a .dll, .exe or .xlam add-in then the file location starts with the phrase ‘REFERENCE to’.
Where Diligence has assessed that there is a likelihood of VBA requesting information from a database table, the name of the suspected table is listed, together with the identity of the routine and code module.
Please note Diligence is unable to ascertain with certainty whether the VBA is pointing to a database table. Database Table entries are listed as indicative only.
Where the dependency is a direct link, such as a formula, to another file then just the file path and name are listed. Where possible an example of where the link is used in the file is displayed as a cell comment, such as this example shown below.
Please note that it may not always be possible to give an example of a linked file location. Links can be made (often accidentally) to objects and properties. In addition, links to hidden, protected cells cannot be ascertained.
No testing is undertaken on the existence of, or access to the linked file. Broken links are identified on the ‘Health and Risk’ sheet of the File Analysis Report.
Runt-Time References
Where Diligence has access to the VBA, the code is analysed for the creation or manipulation of references created by code. If it is deemed this happening a red message Reference added at runtime ?? together with the module, function name and suspected line of code are indicated on the ToC (as below). Due to the many ways in which VBA can be written, this reference is indicative only.
Access to VB Project Denied
If the system has not been given access to the workbook’s VB Project, then references and database tables will not be available in this section; Formula‑links to other workbooks will still be listed where applicable.
A message similar to the one below will be displayed on the ToC. See ‘Access to VB Project’ section at the start of this document for more details.
Macros & VBA
Where the system has access to the VB Project this section will list any macros or VBA included anywhere in the file.
If Access to the VB Project is denied, a warning will be displayed …
If applicable the number of routines and lines of code are shown within the header title, followed by a list of each routine with their additional fields. …
Routine
The Function or Subroutine name as specified in the VB Project.
Hard Coded
A macro is defined as being hard coded if changes to the structure of the worksheet or workbook may necessitate changes to the macro. For example, if a macro sums the values in cells A1:A5 then if a row is inserted at row 4 then a hard‑coded macro will not reflect that change.
Please note it is not possible to ascertain whether a macro is hard coded with absolute certainty and this field is indicative only.
Special attention should be paid to macros created using Excel’s Macro Recorder, which are usually hard coded.
Location
The name of the ‘object’ that contains the routine. This field can have one of three entries
· ‘ThisWorkbook’
· The actual name of the container worksheet
· The actual name of the container module, class module or userform.
Lines of Code
Excludes lines dedicated to comments.
Descriptions
This is a user defined field, however when the ToC is first created or if the description is empty, any comments between the routine/function name and the start of the code are copied to this field. For example, the comment (in green) below have been automatically copied into the ‘Description’ above.
Completing the ToC’s User-Defined Sections
When a new table of Contents is created, a complete set of user-defined fields are created for the user to complete if desired or required by policy.
User‑editable cells are coloured lemon, and can be filled or amended at any time.
In keeping with the Diligence policy of non-interference, Diligence does not enforce the completion of any user defined cell at any time, even if the field is mandatory in your particular organisation’s spreadsheet policy. This also enables a user to insert a ToC, then re‑visit it at a later date to complete any required additional user‑defined fields.
For ease of use, many of the user-defined fields include example text of what should be entered. These examples are presented in grey text on the standard lemon background, as shown below…
The text will automatically revert to the standard colour when the text does NOT start with the phrase ‘E.g. ‘
File Owner
When first created, or if this field is blank, then the name of the user as defined in the ‘My Diligence’ options is entered into this cell. In addition, a cell note is created with the owner’s specified email address.
These details can be changed at any time, but if the File Owner is changed and the email address is not changed, then the next time the ToC is updated, an alert will appear similar to that shown below highlighting to the user that the owner and email may now not match. Users are advised to amend the email address to coincide with the file owner as soon as possible.
Alternative Contact
This should be the name and/or email of the person to whom questions should be directed about the file, should the file owner be unavailable.
Department
This is a drop down list as specified by your organisation. By default, the department name as specified in the ‘My Diligence’ option for the file owner is entered.
The department list is not user editable. Please contact us if you require amendments.
The Department name can be overwritten by the user with any text, however this will result in the department name being highlighted in Amber, as shown below…
File Commonly Known As
This is the colloquial name of the file, as would commonly be referred to in your office or department. For example, ‘Monthly Managers Report’ or ‘Weekly Finance File’
File Categorisation
This is a drop-down comprising the three levels of importance that can be designated to each file. By default, the three options are …
· 1. Critical
· 2. Important
· 3. Non-Important
These names represent the three levels of governance and compliance requirements that need to be applied in the organisation. Please note that the actual name/phrases are specified by each organisation and so may differ from those shown, but will always start with 1,2 or 3. The list is not user editable and no alternative may be entered by the user.
If this field is left blank, it is reported as ‘Unspecified’ in any governance report.
Please see your own company’s documentation for what compliance and governance procedures are associated with each file categorisation.
File Purpose
A brief summary of the purpose, justification or reason that this file is used.
Instructions for Use (Extended ToC only)
For a complicated file, this is likely to be a reference to the location of the instruction documentation, however for less complex files this field should hold brief but meaningful instructions on what steps are required to update or run the report or file.
Data Sources (Extended ToC only)
All spreadsheets should have at least one data source. For companies the various data sources can be divided into four distinct categories…
1. Manual Entry
Data that is entered by hand. For example, holiday dates or sick days.
2. Intra-Department
Data that is copy/pasted or linked, or otherwise acquired from files or workbooks inside the user’s department. For example, a Personnel List in an HR Department.
3. Extra‑Department
Data that is copy/pasted or linked, or otherwise acquired from files or workbooks inside the user’s company but outside of their department. For example, a Personnel List from the HR department when the file is based in the Accounts department.
4. External
Data sources acquired from outside the company, such as stock prices or interest rates.
For each of these categories, the user should select Yes or No from the ‘Required’ dropdown, and enter brief details of the source(s). The example above indicates that the file has one inter‑department data source, and two external data sources.
Output
The aspect or part of the file required or consumed by other users should be entered into this field. Typical examples could be the identity of a particular figure, a print off of sheet x, or the entire workbook.
Audience
The specific names or generic job titles of the people and/or departments and/or companies that consume the data. Alternatively, this field could reference the location of a distribution file.
Run Frequency
A drop-down to indicate the approximate number of times in any given time period that the file is updated or run, not just opened.
The list is not user editable.
Approvals Fields (Extended ToC only) …
Most company compliance and governance procedures will contain a minimum frequency that important spreadsheets need to be re‑approved as ‘fit for purpose’. The three following fields relate directly to this requirement, and should be viewed in conjunction with the ‘Testing or Approval Strategy’ and ‘Test Evidence Location’ fields towards the bottom of the ToC.
Last Approved Date (Extended ToC only)
The date (d mmm yyyy format) that the file was last officially approved.
Last Approved By (Extended ToC only)
The name (not position) of the owner or manager that made the approval.
File Approvals
Select from the drop-down list the frequency at which the file needs to be (re)approved.
To help users remember that an approval is required shortly, a coloured title is used when the 6 month or 12 month frequencies are selected. The file approvals title changes colour from White to Amber 30 days before approval and to Red if the approval becomes overdue.
The following logic is used to colour the title …
|
Approval Frequency |
Days since last approved |
Title Colour |
|
12 Months |
>182 Days |
Red |
|
>152 Days |
Amber |
|
|
< 152 Days |
White |
|
|
6 Months |
>365 Days |
Red |
|
>335 Days |
Amber |
|
|
<335 Days |
White |
|
|
Any other Frequency |
Any |
White |
Worksheet Notes or Description
This field is available for each visible, hidden or very hidden worksheet and is used to describe the contents of the sheet.
Testing or Approval Strategy (Extended ToC only)
This should contain details on the strategy used to validate the correctness of the data and what steps are taken to ensure the correctness of any changes. For large or complex files this may be a reference to a testing document.
Test Evidence Location (Extended ToC only)
Auditors will usually want to see documentary evidence, including screenshots and comments, of the testing undertaken after a structural change has been made. The location (path and file name(s)) of the evidence should be recorded here.
File Archive Location (Extended ToC only)
This is the file path where any archives for this file should be stored.
If this field is populated with a valid, accessible location, this path location is also used by the Diligence Archive function to store archives.
File ID (Extended ToC only)
The unique file ID is used to track the file within Diligence.
Users often change the name of a file, for example to reflect the date of a report. The unique ID enables Diligence to ascertain that the file has changed name, and evaluate it correctly in any inventory and governance reports.
Initially the unique ID is assigned the value of the user’s name plus the date & time of its creation, but any unique text and/or numerical value is valid.
You will not normally need to change the default (unique) File ID value unless you ‘split’ the file into two or more ‘trackable’ files.
If a new file is created on the basis of an existing file that already contains a ToC, then ideally the ToC sheet should be deleted in the second file and a new ToC created.
However, should it be desirable to keep the existing ToC (for example to preserve previous change history) then the unique ID should be changed to a new, unique value by the user.
For example, the File ID SMITHK.160131.122418 could be changed to SMITHK.Report1
Failure to maintain a unique code for each file will result in unexpected governance and compliance reports and an incorrect inventory.
Lifetime of Changes
By default, structural changes are stored indefinitely (Recorded as ‘Unlimited’ on the ToC). However, for practical reasons users may choose to limit the number of changes or length of time the changes are recorded by selecting one of the alternatives from the drop down.
If a ‘Lifetime of Changes’ change is made, then that fact is also recorded as a change, as shown ....
For formal audit purposes any recorded ‘Change in Lifetime’ setting change is NOT deleted for 7 years, irrespective of any setting chosen by the user.
The new Lifetime selection will be applied, and any appropriate records deleted (starting with the oldest), the next time the ToC is updated.
Structural Change History
A small comment or ‘initials’ field is assigned to every structural change row. This is commonly used by the file owner or approver to indicate that they have acknowledged and accepted the change. See below for more details on the change history.
Automatic Recording of Structural Changes
Diligence records structural and other important changes to any spreadsheet containing a Table of Contents worksheet every time the ToC is updated by the user.
Notes of the changes are placed at the top of the ‘Structural Change History’ section of the worksheet, deleting the oldest changes if required. (See Lifetime of Changes.
The summary information gathered during the previous ToC is compared to the current file contents, and a change is logged whenever the following changes are detected…
· A worksheet is added, deleted, renamed or its visibility changed.
· Worksheet Contents change (See below for more details).
· Macros or VBA is added, deleted, renamed or changed.
· The file name or path is changed.
· Links or References are added, deleted or renamed.
· Key User Defined fields in the ToC (e.g. Owner, Archive Location) are changed.
Worksheet Content Changes
When a Table of Contents is created, Diligence separates non-blank cells into four sections
· Cells containing a formula (Formula Cells)
· Cells containing a number and formatted as a date or time (Date Cells)
· Non Date-Cells, containing a number (Numeric Cells)
· Cells containing text or a mixture of text and numbers (Text Cells)
When Diligence detects changes to the number or contents of any of these cell groups it records a single line change record summarizing the change(s). For brevity each change is limited to a single line of text on the worksheet, with additional information shown as a cell comment, for example ….
or …
· In all instances where a new or deleted formula is identified, the formula is indicated in the cell commen.t
· In all instances where a new internal worksheet references is used or deleted the sheet (or if applicable the range name) is identified in the cell comment.
· In all instances where a new reference to another workbook is used or deleted the workbook, and the worksheet/named range is identified in the cell comment.
The following example of changes within a single workbook as shown in Excel and its associated cell comment shows a host of changes being identified, and approaches the limit of the number of changes that would be shown….
Change Log Details
When changes are logged, an incremental change ID and the update date are recorded, together with the details of the change. One row is added to the list for each change found. The example below shows that on one update occasion, no changes were found, and on the other two ToC update occasions a single recordable update was found and logged.
If no structural changes are identified, then this is also recorded as a single line for auditing purposes.
The name of the user that ran the update and the time of the update are recorded in a cell note on the ID/Date field
Background Colour
The background colour alternates between blue and white. Each time the ToC is updated the alternative colour is used for all the changes logged.
Font Colour
More important structural changes such as new worksheets or changed formulas are displayed in red text. Less critical changes such as the number of hidden columns or a change in owner name are displayed in dark blue.
Depending on the company’s specific compliance procedures and the importance setting of the file, red text may denote that testing, approval and/or a comment on the change is mandatory.
The following changes are listed in red text (meaning it is advised as worthy of comment and/or testing by the spreadsheet owner)
· A worksheet is added, deleted, renamed or its visibility changed.
· Macros or VBA is added, deleted, renamed or changed.
· A link or reference to another workbook is added, deleted or renamed.
· Change in the number of formula, numeric, text or date cells.
· New or deleted references to internal or external worksheets.
· Changes in the structure of formula cells, for example SUM() changed to SUMIF()
· New formulas used or formulas deleted.
The following changes are shown in black text, and primarily for information purposes; They may or may not be worthy of comment and/or testing
· The file name or path is changed.
· A link or reference to a sheet within the workbook is added, deleted or renamed.
· Key User Defined fields in the ToC (e.g. Owner, Archive Location) are changed.
· Content changes to existing worksheet data, numerical or date cells. For example, 3 changed to 4, or March 1999 changed to April 1999
· Changes in worksheet formula ranges or constants, for example Sum(3,4,5) to Sum(4,5,6) or SUM(Data!A1:B2) to SUM(Data!A1:B100)
· The hiding or un-hiding of rows and/or columns of worksheets.
The following changes are not deemed of importance and are not identified
· Insertion or deletion of blank rows or columns.
· The moving of cells where the general structure of the worksheet is maintained.
· Worksheet formatting changes.
· The addition, change or deletion of shapes, graphs, cell comments or other objects.
Advanced ToC Usage
Table of Contents Userform Options
When the Table of Contents button in the Diligence ribbon is pressed, a userform is displayed. Normally a user can simply press the ‘OK’ button to create or update a ToC on the active workbook, however three options are available …
Include This File in ‘My Inventory’
If checked, when a ToC is added or updated, that workbook is automatically included in the user’s inventory.
If left unchecked then the decision to include, preserve or remove the file from the inventory is specified by the user option in ‘My Diligence’ / ‘My Inventory’
Analyse Workbook
In addition to the ToC, a full, extended workbook analysis will be created in a separate file.
See ‘Analyse Workbook’ for more details.
Show Extended Details on the ToC Sheet
The sections marked in red, below, represent the ‘Extended user defined details’. These fields offer additional compliance information and can be displayed or hidden via the ToC options userform.
Some users prefer the cleaner ‘feel’ of the ToC with the extra fields hidden for normal operation, and show the extended fields only when required. Toggle the option at any time a ToC is the active sheet to show or hide these extended details.
The default setting for this option has been decided by your organisation and cannot be changed.
For security
purposes these fields have been designed to only be hidden/shown via the ToC
options form and not manually.
 |
Option to Record Structural Changes
By default, Diligence will automatically save the file once a ToC has been updated. This is by design and ensures that the changes are logged on the file.
However, it is possible to use Diligence to simply ‘check’ whether any structural changes have been made to a file, by disregarding its findings after performing the function.
This may be desirable, for example when extra formulas have been added to a file to test a scenario, and subsequently deleted. You can use the Diligence ToC to check that all the added formulae were successfully removed.
To offer this choice, once Diligence has finished its analysis, a userform similar to that displayed below will be shown, giving the user three options …
· YES …………….. Record any changes on the worksheet and save the file. (Default)
· NO…………….. Discard the findings. Do not amend the file.
Please note this option is not displayed where there is currently no ToC on the workbook or the file has never been saved. In this situation the ToC is automatically added/updated to the file and, if possible, saved.
Note that if the file is read-only or in a read only path, then Diligence will be unable to save the file. If this occurs the changes will be sent to the file but an error message similar to that below will be displayed.
File Size & Processing Time
There is no limit to the size of worksheets being analysed, however a warning is issued when one or more worksheets exceeds a used range in excess of 50 000 cells. The warning gives the user the opportunity to cancel the instruction, as in the example below warning the user of two large worksheets in the file …
The actual time taken to analyse worksheets varies considerably based upon the number and type of formulas and the sheet’s overall complexity, together with the computer and memory/processing resources available.
As an order of magnitude expect a small worksheet to take a few seconds, a medium worksheet 10-20 seconds and a complex sheet about a minute. Extremely large and complex worksheets can take an hour or more.
Location of the Table of Contents Worksheet
By default, the ToC is entered as the first visible worksheet on the file. However, full system functionality is retained if the sheet is hidden or moved by the user. Users are free to move, hide or ‘very-hide’ the ToC at any time.
Removing the Table of Contents Worksheet
The ToC can be removed by any user at any time using the same method as other worksheets. No legacy of its previous existence will remain on the workbook.
If the ToC is first saved before removal, it can be re-inserted (copied back) at a later date and the ToC subsequently updated to expose any structural changes. This can be useful if a user wants to ‘silently’ uncover changes being made by other users, or if there is a need to have multiple ‘start points’ for any change analysis. For example, one ToC updated yearly for audit purposes, and one update quarterly for change analysis.
Please be aware that a workbook can only have one ToC sheet at any given time.
Limitations, Issues & Tips
VBA is Password Protected
If the VBA of the source file is password-protected, then the user will be prompted for the password, similar to the example below …
If the user does not know (or chooses not to enter) the password, then a confirmation user form will be shown …
If the user
continues without entering the password, then a note is made in the Structural Change
History section that the VBA was not assessed.
Hidden Protected Formulae
When cells have been hidden AND the worksheet has been protected to prevent cell changes, then Excel denies all requests by another Excel file, such as Diligence to query its contents or formula.
If this scenario is encountered, then the user is presented with a userform explaining the limitation, and where it has occurred, for example …
If the user opts to continue, then the Structural Change History will reflect that these cells could not be assessed, similar to the entry on the second row below …
Formulae Referencing Other Workbook: Open vs. Closed Files
When formulas referencing external formulas are included in a file, the formula itself changes if the file is closed or open …
For example, with Book4 opened, a formula might look like …
But with Book4 saved and closed, the exact same formula might show …
Depending on when the ToC is run, the recorded formula may therefore be stored differently. In these cases, the ToC will show the following message (assuming no other changes)
Tests for Non‑Compiled Workbooks and File Corruption
Diligence queries Excel about the file(s) upon which it is working.
If Excel fails to answer Diligence’s ‘questions’, then Diligence cannot accurately complete all of its tasks. In some situations, it cannot proceed at all.
Apart from password protected hidden cells, and denial of Access to the VB Project (covered separately above) the most common reasons for failure to ascertain information are non‑compileable and/or corrupt files.
Diligence tests for these scenarios in a number of ways in an attempt to anticipate them. If any file fails these tests a warning is issued to the user, similar to that below …
If the user receives this warning they are strongly advised to take the action recommended, which is to say compile the file manually to resolve any errors, and/or save the file to the latest file format, and/or revert to a backup.
However, it is accepted that these options are not always available and the user has the option to ignore the warning (by pressing ‘Yes’ on the above form)
In many cases the file problems will not impact the Diligence process, but should the user choose to ignore the warning, a record that a warning was issued is kept for the duration of the Excel session and will be included in any Diligence error logs.
………continued
On some occasions however, it is known that the system will be unable to continue with the file. In this case a warning similar to that shown below is displayed to the user …
Too Many Cell Formats
Due to a legacy Excel issue dating back to Excel 2003, some workbooks cannot accept a new worksheet because it would exceed Excel’s style limitation.
This is anticipated by Diligence, and users are alerted to the problem. Diligence also contains a utility to remove superfluous styles. (see ‘My Diligence / ‘Utilities’)
Search ‘Excel too many cell formats’ for much more information on this problem.
Other Limitations & Issues
Hyperlink Font Size
In rare situations Diligence may be unable to set the hyperlink font size and colour. This is an issue outside of Diligence’s control and there is currently no plan to address the problem further.
However, if the font size makes the hyperlinks (to the visible worksheets) too small then a message is issued to the user, similar to that shown below. In this case if the user deems the size/colour unacceptable the hyperlink font will need to be changed manually.
Invalid email address
If an invalid email is detected in the file owner field, then a userform is displayed and the issue must be corrected before a ToC is created or updated.
Also …
· Changes to Comments, Text Boxes, Charts, Shapes and other objects are not recorded not recorded on the ToC.
· A Table of Contents cannot be added to a Diligence Summary report.
· Diligence will not assess and/or report on more than 99 contiguous ranges per worksheet.
· Diligence will not record more than 99 references to other workbooks or program files or databases.
FAQ…
My file is extremely confidential…Is it safe to have a ToC?
It is entirely safe. The ToC contains a snapshot (metadata) of the file’s contents when the ToC was last updated. The names of the worksheets, names of any macros, and names of any links or references to other files are logged and displayed, but no cell formulas, contents or comments are stored.
Does the ToC monitor what changes I make?
No. The ToC is completely benign. It only compares the current workbook contents to its previous summary when the ToC is updated.
How often should I update the ToC?
As a minimum, it is recommended that the ToC is updated whenever important changes have been made to the file, such as after a design change or changes to accommodate new data, although many users update the ToC after each cycle of the report, to check that no changes have been made.
In addition, if key user‑defined fields on the ToC have been amended a warning is displayed in red, just below the company logo on the ToC …
“Some Important User-Specified Details Have Been Amended On This Table Of Contents Sheet: Please Re-Run the ToC From The Diligence Ribbon To Log These Changes”
Can the ToC be updated automatically?
It is against the ethos of the Diligence system to take control of users files and update (and hence record) changes on an automated basis. However, on request we can provide users with a small macro that can be attached to any Excel file that will automatically update the ToC. Please contact us for more details.
Professional Tips
· The ToC can be a useful aid to prove to auditors that no structural changes have been made in a file. something that is otherwise quite difficult.
· Sheet names in the Visible Worksheets section are also hyperlinks. Click the name to navigate directly to the worksheet.
· If you find that the ToC is logging numerous changes each time it is run, and the worksheet is becoming clogged with information consider limiting the number of changes recorded by selecting an appropriate option from the ‘Lifetime of Changes’ dropdown at the top right of the ‘Change History’ section.
· The length of time last taken to create(update) the ToC is shown just under the ‘Last ToC Update’ date at the top of the ToC worksheet.
· Legacy sheets such as Excel 4 Macro Dialog & Macro Sheets, plus dedicated Chart sheets are not analysed on the ToC, but are listed in red text at the top of the Visible Worksheet section.
· If a ToC is created on a previously unsaved file, then by default it will not be saved into the user’s inventory, even if the user’s default setting is to save all ToC files to the inventory. This is because it would have a fairly meaningless title (e.g. Book2.xlsx) and it is unlikely that the file with that name would/could ever be re‑opened.
Table of Contents: Further Reading & Associated Articles
Full List of ToC Fields
Workbook Summary Information
1. File Name, Path and Size.
2. File Complexity Score.
3. Number of Visible and Hidden Worksheets.
4. Number of Macros/User Defined Functions & Lines of Code.
5. Number of Links & References to Other Files.
6. List of Excel Legacy Worksheets, e.g. Excel 4 Macro Sheets.
Worksheet Summary Information
For each worksheet ...
7. Worksheet Name (with hyperlink to jump straight to visible sheets).
8. Visibility Status (Visible, Hidden or Very Hidden).
9. Overall Complexity Score.
10. Used Sheet Size (Rows x Columns).
11. Number of Hidden Rows and Columns.
12. Whether or not the sheet contains Formulas.
13. If / How many Formulas are Protected.
14. List of Other Files Referenced.
15. List of Worksheets within the File Referenced.
16. List of Functions Used e.g. SUM(), IF(), VLOOKUP()
17. List of Compound Functions Used. E.g. SUM(IF(VLOOKUP)))
18. Number & Location of any Cell Errors. E.g. #REF(), #NA()
19. Number & Location of ‘Orphan’ numbers (not included in any formula).
20. Number & Location of Value Cells that may be Overwritten Formulas.
21. Number & Location of Cells Containing Numbers Formatted as Text.
22. Number & Location of Cells that have an Inconsistent Formula.
23. Location & Complexity Score of the Sheet’s most complex function.
24. Number & Location of Cells Containing 6 or More Functions.
Many of the formula details are not overtly displayed, but contained in a cell comment. You can read more about why these are highlighted in the Workbook Analysis section.
Links & References -Requires Access to VB Project
For each link or reference to another file or program ...
25. Link or Reference Name & Path
26. Example of Cell That Uses the Reference (if possible).
27. Database Tables mentioned in VBA.
Macros & User Defined Functions (UDFs) -Requires Access to VB Project
For each macro or UDF ...
28. Name & Worksheet / Module Location.
29. Lines of Code, excluding comments.
30. Hard-Coded Line Warning (Hard coded macros may need to be changed with the sheet structure).
31. Macro Description (If available).
Workbook History
Each time the Table of Contents is updated one Change History Row is created, indexed and appended at the base of the Table of Contents sheet for each of the following changes...
32. Worksheet Added, Deleted, Renamed or Visibility Changed.
33. Worksheet Formulas Added, Deleted or Changed.
34. Worksheet Values Added, Deleted or Changed (excludes Formula results).
35. Hidden Rows or Columns Changed in a Worksheet.
36. Macros Added, Deleted, Renamed or Changed.
37. File Name or Path Changed.
38. Key User Defined Table of Contents sheet Data Changes E.g. Owner, Archive Location.
39. Links or References Added, Deleted or Renamed.
40. The Update Date, Time & User is also logged.
Table of Contents Metadata
41. Last Table of Contents Update date.
42. Time Taken for Last Update.
43. User Name for Last Update.
User Defined Data
The following fields are not populated automatically and are entirely optional within the system, although the company spreadsheet policy may mean some or all are mandatory fields.
44. File Owner.
45. Alternative Contact.
46. Department.
47. Alternative/Common File Name.
48. Level of Importance.
49. Purpose of the File.
50. Instructions for File Use.
51. File Output.
52. Audience or Distribution List.
53. When and How Often the File is Run.
54. Approvals; Who, How Often Required and Date Last Approved.
55. Description of Each Worksheet.
56. Description of Macros (In-Line macro descriptions are automatically copied across if available).
57. Testing Details or Strategy & Location of Test Evidence.
58. File Archive Location.
Workbook, Worksheet & Formula Complexity Algorithms
Complexity Score Metrics
Level of Complexity is a big topic amongst Spreadsheet Auditors and Analysts. The level of spreadsheet complexity will often dictate the likelihood of an audit investigation, the level of support required or the need to fully test and document the spreadsheet.
By its very nature ‘complexity’ is a subjective matter, and what may be simple to one user may appear complicated to another. Diligence uses a set of standard metrics to calculate its formula, worksheet and workbook complexity results. The figures produced are therefore consistent and can be used confidently to compare one workbook against another.
This section is designed to give readers a meaningful understanding of the structure and implications of the Diligence complexity scores.
Workbook Complexity Overview
The overall workbook complexity score is a number from zero to a theoretical maximum of 10, where zero represents an empty workbook and 10 an extremely complicated workbook.
The number is calculated using an exponential method, such that the score reaches mid-levels with a small number of complicated features, but as the score approaches 10 a significant increase in complexity will result in only a small increase in the score.
The key driver for workbook complexity is the complexity of the individual worksheets, but other factors such as hidden sheets, links, references and macro code are also taken into account.
Typical workbook complexity scores
Complexity Score 9.0+
Typical example: Report with several data feeds, macros & complex functions.
It is likely that the workbook will consist of numerous worksheets containing abundant intricate formulae. Some sheets may be hidden. It may have real-time data feeds, VBA, references to extended or third party functions & links to other workbooks. It is unlikely a lay person would easily understand this workbook.
Complexity Score 7.0
Typical example: Financial report with a data feed and summary sheet
This is the level of advanced spreadsheets designed by in- house experts or third party
consultants. These workbooks may consist of numerous worksheets that are likely to
contain advanced/nested Excel Functions. It is quite possible they will include macros/VBA or enhanced functionality via addins or third party references. It would take a lay person a significant amount of time to understand this workbook.
Complexity Score 5.0
Typical example: Financial statement
This workbook is likely to contain a few data sheets with formulae. Some sheets may be hidden and there are likely to be one or two links to other files or some basic macros.
Complexity Score 3.0
Typical example: Basic expense form
This workbook is likely to contain one or two data sheets with simple data structures
and perhaps a few basic formulae. The file will not have macros, and is unlikely to have a pivot table or link to another file.
Complexity Score 1.0
Typical example: Inventory list
Single worksheet with one range of data. No formulae.
Worksheet Complexity Overview.
Individual worksheets are also given a complexity score. Unlike the overall workbook complexity score this number is a simple addition of many weighted factors, such as the number and type of formulae, the use of hidden rows, and links to other worksheets and workbooks. In reality it is most unlikely that any one worksheet would score above 6.0. The overall workbook score is based heavily on the number and spread of the individual worksheet scores and will always be a figure the same or higher than the most complicated worksheet score. A good analogy to worksheet scores is the relevant amount of time it would take for a lay person to completely understand the working of that worksheet.
Formula Complexity Overview.
Each cell formula is also allocated a complexity score. Unlike the worksheet and workbook complexity score this figure has no upper limit, and is calculated according to the Functions used within the cell. A higher complexity score is allocated for the use of User Defined Functions (UDFs) and Array Formulas (also known as Brace or Curly Brackets Formulae) In addition, weighting is given to the level of function‑nesting within the formula plus the number of additional arguments.
|
Example Formula Complexity Scores |
Complexity Score |
|
=IF() |
1.2 |
|
=VLOOKUP() |
1.5 |
|
=IF(SUM(SUM())) |
2.0 |
|
=IF(ISERROR(VLOOKUP()),AVERAGE(ROUNDUP()))) |
6.0 |
A score above 6 is defined as complex for the purpose of displaying in the complexity commentary on the ToC worksheet comment and the Analysis Workbook.
Factors Used in the Complexity Scores
Factors used in the calculation of worksheet complexity…
· The number of formula cells on the worksheet
· The number of different formulae used
· The complexity of the formulae used (Including nested formulae)
· The use of array formulas
· The number of different formula groups used (e.g. Statistical, Logical, Data)
· The number and complexity of any macros or VBA attached to the worksheet
· The number and spread of hidden rows and columns
· The number of linked worksheets within the workbook
· The number of linked worksheets in other workbooks
· The number and structure of contiguous ranges within the worksheet
· The overall used range of the worksheet
Factors used in the calculation of workbook complexity…
A figure is calculated based on the three highest worksheet complexity scores, the spread of complexity scores and number of worksheets (the base score). This figure is then augmented with the following factors …
· The number of hidden and very hidden worksheets
· The number of references to additional or third party functions (E.g. Bloomberg)
· The use of Real-Time-Data
· The amount and complexity of any macros or VBA, not included in the Worksheets above.