Light touch, Audit quality spreadsheet compliance & governance.

Diligence for Excel

The Table of Contents Worksheet; List of Features ….

The Table of Contents (ToC) worksheet contains a great deal of information in an easy to understand format.

Workbook Summary Information

· File Name, Path and Size

· File Complexity Score

· Number of Visible and Hidden Worksheets

· Number of Macros/User Defined Functions & Lines of Code

· Number of Links & References to Other Files

· List of Excel Legacy Worksheets, e.g. Excel 4 Macro Sheets

 

Worksheet Summary Information

For each worksheet ...

· Sheet Name (with hyperlink to jump straight to the sheet)

· Visibility Status (Visible, Hidden or Very Hidden)

· Overall Complexity Score

· Sheet Size (Rows x Columns)

· Number of Hidden Rows and Columns

· Whether or not the sheet contains Formulas

· How many Formulas are Protected

· List of Other Files Referenced

· List of Worksheets within the File Referenced

· List of Functions Used e.g. SUM(), IF(), VLOOKUP()

· List of Compound Functions Used e.g. SUM(IF(VLOOKUP)))

· Number & Location of any Cell Errors e.g. #REF(), #NA()

· Number & Location of ‘Orphan’ numbers (not included in any formula)

· Number & Location of Value Cells that may be Overwritten Formulas

· Number & Location of Cells Containing Numbers Formatted as Text

· Number & Location of Cells that have an Inconsistent Formula

· Location & Complexity Score of the Sheet’s most complex function

· 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 these cell comments here.

 

Links & References

For each link or reference to another file or program ...

· Link or Reference Name & Path

· Example of Cell That Uses The Reference

· Database Tables mentioned in VBA SELECT- FROM SQL

 

Macros & User Defined Functions (UDFs)

For each macro or UDF ...

· Name & Worksheet / Module Location

· Lines of Code

· Hard-Coded Line Warning (Hard coded macros may need to be changed with the sheet structure)

· Macro Description (If available)

 

Workbook History (Corporate Installation Only—Not available in ‘Diligence For Excel Consultant’)

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 ...

· Worksheet Added, Deleted , Renamed or Visibility Changed

· Worksheet Formulas Added, Deleted or Changed

· Worksheet Values Added, Deleted or Changed (excludes Formula results)

· Hidden Rows or Columns Changed in a Worksheet

· Macros Added, Deleted, Renamed or Changed

· File Name or Path Changed

· Key User Defined Table of Contents sheet Data Changes e.g. Owner, Archive Location

· Links or References Added, Deleted or Renamed

The Update Date, Time & User is also logged.

 

Table of Contents Metadata

· Last Table of Contents sheet Update

· Time Taken for Last Update

· User Name for Last Update

 

User Defined Data

The following fields are not populated automatically and are optional within the Table of Contents, although your own spreadsheet policy may mean some or all are mandatory fields for your users.

· File Owner

· Alternative Contact

· Department

· Alternative/Common File Name

· Level of Importance

· Purpose of the File

· Instructions For Use

· File Output

· Audience or Distribution List

· When and How Often the File is Run

· Approvals; Who, How Often Required and Date Last Approved

· Description of Each Worksheet

· Description of Macros (In-Line macro descriptions are automatically copied across if available)

· Testing Details or Strategy & Location of Test Evidence

· File Archive Location