In the previous post Spreadsheets: Risk, I mentioned few of the risks associated with spreadsheets. In this post I’ll try to show some excel tools which can help in detecting errors and frauds in Excel spreadsheets.
In the late 1990’s “Poor control over spreadsheets at Jamaican indigenous banks contributed to management information and external reporting problems (i.e., P&L distortions) that contributed to the banks’ management and external regulators losing sight of the banks’ true positions and exposures. Which led to collapse of entire Jamaican Banking System.
Go to Special
Go To Special is a tool built-in Excel to select cells based on a specified type within a worksheet.
How to access Go To Special function?
Home > Find & Select > Go To Special
Shortcut – F5 or Ctrl + G
Options available in Go To Special
Selects all the cell with constant values. You have further option of selecting Numbers, Texts, Logicals and Errors.
In the picture below you can see the result of go to special constant. If you notice, it has also selected the cell N1. The cell looks blank but if you look at the formula bar it contains value “438”. It’s because the font colour is same as background colour. It’s one of the ways fraudsters manipulate spreadsheets.
Selects all the cell containing formulas. You have further option of selecting formulas which return values as Numbers, Texts, Logicals and Errors.
In the picture below you can see all cells are selected except G12 & G 13, even though they contain the formula. It’s because an embedded image is used on those cells. Another technique used by fraudsters to manipulate spreadsheets.
You can use the objects options to select cells containing embedded objects.
The other options in Go To special are:
- Comments – selects the cells with comments
- Blanks – selects all the blank cells
- Current region – selects the current region
- Current array – selects the entire array, if the active cell is within the array
- Row differences – Selects the cells that are different from the active cell within the selected rows. The cell used for comparison for each row is the cell in the same column as the active cell. This is very useful in highlighting inconsistent formulas in a row.
- Column differences – Selects the cells that are different from the active cell within the selected column. The cell used for comparison for each column is the cell in the same row as the active cell. This is a very useful auditing tool for highlighting inconsistent formulas in a column. It also offers a quick and easy way to spot differences across multiple columns.
- Precedents – Selects the precedent cells for the selected cells
- Dependents – select the dependent cells for the selected cells
- Last cell – selects the last cell containing data or formatting in the worksheet
- Visible cells only – selects the cells which are not hidden (that is visible)
- Conditional formats – selects all the cells with conditional formatting applied
- Data validation – selects all the cells with data validation applied
The Formula Auditing tools are available under the Formula tab in the ribbon.
In the formula auditing tools you have trace precedent, dependent, show formula, error checking and evaluate formula.
Also note trace precedent and dependent are also available in Go To Special. The Go To Special trace precedents & dependents doesn’t show the arrows. By using trace precedent and dependent you can see how data is flowing in the worksheet. In the picture below you can see the pattern how data in cells D3 to D6 are linked to various other cells. You also notice that cell D5 doesn’t follow the same pattern and the same needs to be investigated.
The other tools available in auditing formula are error checking and evaluate formula. By using error checking > trace error, you can trace error by moving backwards in the worksheet. And evaluate formula helps in the understanding complex formulas.
Spreadsheet Inquire is an excel add-in available in MS office 2013 professional plus and Office 365 professional plus.
You can enable Inquire add-in by going to Options > add-ins > com add-ins.
With Inquire add-in you have the following main options; Workbook Analysis, Workbook Relationship, Worksheet Relationship, Cell Relationship, and Compare Files.
It creates a detailed information on the workbook structure, formulas, cells, ranges and warnings.
- In the Workbook tab hidden sheet & Very hidden sheet should be investigated.
- In the Formulas tab investigate formulas with errors, formulas referencing hidden cells, negatives, duplicates, inconsistent and formulas with numeric constant.
- In the Cells tab investigate invisible (font colour same as background), with conditional formatting as cells can be formatted with to show negative number as positive. However note the negative cells formatted to be shown as positive using format cell (ctrl +1) option doesn’t appear here.
- In the Ranges tab you can find hidden rows & columns, named items and named items with errors.
- The final tab is Warnings. All warnings should be investigated.
You can also export the workbook analysis report to an excel spreadsheet. The report is very detailed and each item on the left of analysis report has its own worksheet.
The other options on Inquire tab workbook relationship, worksheet relationship & cell relationship, gives a detailed diagrammatic view of how workbook is linked with different workbooks, worksheet with different worksheets in the workbook and cells with different cells.
It is similar to the word feature of compare documents. You can get a detailed listing of what have been changed in the new file when compared to old. The comparison looks something like this.
Very Hidden Sheets
Very Hidden sheets can’t be unhide through unhide sheet option in Excel. To view Very Hidden sheets you have to first go to Visual Basic Editor (Alt +F11). The Upper left corner lists down all the sheets in the workbook. Click the sheet which is not showing up in workbook and change it visible settings to “-1-xlSheetVisible” in the lower left corner.
Microsoft Discovery & Risk Assessment Server 2013
Microsoft Discovery and Risk Assessment Server 2013 gives a consistent, automatic way to identify, analyze, and track files that are used in an organization. It can be used to create a master inventory of Excel workbooks Access databases, and automatically keep it up to date. It can also be used to categorize files according to predefined risk levels based on criteria, such as materiality or complexity.
So this tool will find all your Excel or Access files and give them a risk level based on criteria that you define and set.
Some additional points to consider:
- Unhide all the hidden rows & columns in the worksheet
- Format all the numbers as “Numbers” instead of keeping them custom or general, as it might be possible they are formatted to show negative as a positive value.
Since errors and frauds in spreadsheets can have major consequences. Adequate time should be spent on auditing and validating them. There is no precise way to audit Excel Spreadsheet. There are also a lot of paid tools available in the market to audit excel spreadsheets.
You can download the Excel file I used for this post here, spreadsheet audit template.
The actual file is created by Paula Guilfoyle, of the Excel club.