|
Light touch, Audit quality spreadsheet compliance & governance. |
|
Diligence for Excel |
|
Worksheet Comments Just to the right of the worksheet names on the Table of Contents worksheet, there are several cells indicating each sheet’s complexity score, existence of formulae in the sheet and sheet size. Accompanying these are cell comments that contain information we find useful when reviewing or understand a worksheet ... Formulae A list of the Excel functions and any User Defined Functions (UDFs) used in the sheet is shown as a cell comment on the worksheet’s complexity score. Where nested formulae are used, these are shown in addition to the formulae, for example IF(, SUM(, IF(SUM))) Note that for brevity these details do not contain the formulas themselves, just the Excel function used. E.g. SUM() not SUM($A1:$A2) Where complicated formulae are used, up to 5 of the most complex are displayed along with their individual complexity score. Where there are more than 6 functions used in a single formula, this is indicated. Material Errors The Formulae box adjacent to the worksheet complexity score is ticked if the sheet contains at least one formula, with a formula being defined as any cell whose contents start with the equals sign. A cell comment often accompanies this cell, which is used in the following situations … 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 If a formula cell is sandwiched between two cells with the same formula this is highlighted. Numbers Formatted as Text Numbers that are formatted as text can have significant impact on other formulae, and cause incorrect calculations. These cells are highlighted. Number in a Formula If a formula contains both a function AND a number (E.g. Sum(A:A)*1.2 ) this is highlighted as bad practice, because the number 1.2 should 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 refers to the other. 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 are noted. 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 should will also be highlighted in the links section as being potentially unavailable to all users, |
