The Importance of Auditing A Spreadsheet

This article will give you the information needed to perform an effective spreadsheet audit.

Whether creating a new spreadsheet based model or inheriting a complex Excel file from a colleague, being able to audit a spreadsheet effectively is a useful skill for insuring the data needed to make decisions is reliable. This article outlines how spreadsheets work and how to conduct an effective spreadsheet audit, comparing the manual in-built Excel tools with an automated spreadsheet management platform.

Errors and flaws within spreadsheets occur all the time. Auditing the vital spreadsheets across a business can help to mitigate the risk of errors, making data processes more robust and reliable; understanding exactly what resources are available as a business is vital to making the right strategy choices. Without information and data correctly laid out before decision makers, there is a real danger of making mistakes based on wrong numbers.

What Is Spreadsheet Auditing?

In simple terms, spreadsheet audits are designed to ensure a spreadsheet performs the way it was built to. This involves checking all cells are doing what was planned for them, the data is processed as expected, and there are no errors inputted into the sheet.

There are processes available to make the audit easier to complete, as well as excel auditing tools that can help explore the way the data is structured in the sheets, and the calculations that are being made. We’ve outlined the processes available below.

How Do You Actually Audit A Spreadsheet?

Manual Spreadsheet Audit

Manually auditing a spreadsheet is a time consuming process involving checking for hidden rows, columns or worksheets and individually verifying cell inputs, one sheet at a time, to check data, calculations and formatting.

Additionally, a typical spreadsheet audit would delve deeper, to understand the ordering and flow of data as it moves through a spreadsheet. A number of tools in Excel can be used to take a closer look at individual cells and sheets:

If the cell highlighted has a formula in it and you use this function, then arrows will be drawn onto the sheet, highlighting all the cells used to make the formula.

Trace Dependents

This function will draw arrows from a highlighted cell to any other cells that use it within a formula.

Remove Arrows

This will remove any arrows that the functions Trace Precedents or Trace Dependents have added onto the worksheet.

Show Formulas

This will show all the formulas that are within the current worksheet.

Error Checking

This function contains 3 options.

• The first ‘Error Checking’ is used to highlight cells that have errors within the, such as #VALUE!, whilst suggesting fixes for them.
• The second option is ‘Trace Errors’, if a cell has an error within it, using this will add arrows to the sheet, pointing to all the source cells.
• ‘Circular References’ highlights any cells with formulas that have a range that includes itself.

Evaluate Formula

This brings up a dialogue box that helps to unpick the different sections of complex formulas, helping the user to troubleshoot them.

Data Validation

Data Validation can be found within the Data ribbon and allows for the setting of rules to a range of cells. When these rules are defined, users are only able to input qualified values. By utilizing the ‘Circle Invalid Data’ option, all cells that do not meet the rules defined by Data Validation will be highlighted. Whilst Data Validation should prevent certain numbers from being inputted, a formula can sometimes over-right these rules.

When working on a large spreadsheet, it is not uncommon for the data to be split across multiple workbooks. From time to time, a dialogue box will pop up, notifying that the workbook contains information from another source. To find the information that is pulled from another workbook, go to the Data Ribbon, and then the ‘Queries & Connections’ section.

Automated Spreadsheet Audit

Understandably, performing all the checks above takes considerable time, which can be saved by using a Spreadsheet Management software platform. Our solutions enable organizations to expose and understand the data lineage of spreadsheets, identify any changes made to specific files, when, and by whom to avoid the risk of inconsistencies and errors.

By implementing a spreadsheet automation solution, you will be able to:

• Compare multiple versions of a file to identify the changes made.
• Apply a control framework to your spreadsheets and Access databases, to create an audit trail of changes, and see how your files evolve over time.
• Manage the review and approval process for critical cells in high-risk spreadsheets.
• Automate the production of documentary evidence, e.g. for SOX compliance.

The increased management control ClusterSeven delivers will help you better manage spreadsheet-dependent processes and reduce the risk of errors.

The software works unobtrusively in the background, monitoring, recording and analyzing changes to files (even if they get renamed), and alerting you to any anomalies so that you can take action. All without changing anything about the agility offered by spreadsheets, or the way people work with them.

Spreadsheet audits don’t have to be a pain. Take a look at our Spreadsheet Risk Management guide to learn more about managing the risks of Excel files and other End User Computing applications.