New Auditing and Compliance Features for Excel and Access

Office 2013 introduces some great new features to help audit and manage Excel workbooks and Access databases – sometimes referred to as EUCs or End User Computing applications. 

Although I’m a huge proponent of enabling knowledge workers with Self-Service BI capabilities, I’m equally a proponent of IT assuming responsibility for managing and securing the environments.  These features, an evolution from the Prodiance Corp. acquisition, are a welcome addition for both end users and IT to manage Excel and Access.  And as a bonus, your auditors will love it!

Office 2013 desktop desktop applications   <—Focus:  1-2 files.  Target audience: Anyone with Excel

  • Inquire
  • Spreadsheet Compare
  • Database Compare

Office 2013 server applications    <—Focus:  many files.  Target audience: IT system admins, auditors, or anyone who wants to monitor changes to a set of files

  • Microsoft Office Audit and Control Management Server 2013
  • Microsoft Discovery and Risk Assessment

Following is a brief review of the capabilities of each item.

Inquire

Applicable to:  Excel 2013 (Office Professional Plus)

Accessed via:  Inquire Ribbon Menu inside of Excel

    image

Capabilities:

  • Workbook Analysis – Report which contains metadata about a workbook such as formulas, cells, ranges, data connections, and links.  Useful for documentation and to aid in understanding a workbook.
  • Workbook Relationship – Diagram which maps out the links to other workbooks & data sources.  Helpful to analyze dependencies, lineage, and the potential effect of changes.
  • Cell Relationship – Diagram which maps out links and formulas between cells within the same workbook or other workbooks.
  • Compare Files – Compares two workbooks currently open; highlights the differences cell by cell. The differences are color-coded and categorized so more critical changes, such as a change to a entered values or calculated values, is separate from an immaterial change like a change to a cell format.  This is actually the same functionality as the Spreadsheet Compare tool, discussed in the next section; this feature is just available within Excel as a convenience.
  • Clean Excess Cell Formatting – Eliminates unneeded formatting to reduce the size of the file and/or improve performance.
  • Workbook Passwords – A Password Manager which stores passwords for Inquire so it can open and perform analysis on password-protected workbook(s).

More info: What You Can Do With Spreadsheet Inquire

Spreadsheet Compare

Applicable to: Excel 2013 (Office Professional Plus)

Accessed via:  Start > All Programs > Microsoft Office 2013 > Office 2013 Tools > Spreadsheet Compare 2013

Capabilities:  Compares two workbooks currently open; highlights the differences cell by cell.  The differences are color-coded and categorized so more critical changes, such as a change to a entered values or calculated values, is separate from an immaterial change like a change to a cell format.

More info:  Basic Tasks in Spreadsheet Compare

Database Compare

Applicable to: Access 2013 (Office Professional Plus)

Accessed via:  Start > All Programs > Microsoft Office 2013 > Office 2013 Tools > Database Compare 2013

Capabilities:  Compares two Access databases and highlights the differences in a query, form, report, or code.  What it will not do is compare the difference in data stored within the tables (workaround: export the data to excel and use Spreadsheet Compare).

More info:  Basic Tasks in Database Compare

Microsoft Office Audit and Control Management Server 2013 (ACM 2013)

Capabilities:

  • Logs and reports on changes made to critical spreadsheets.
  • Cell level auditing, versioning, and audit trails.
  • Change control and data integrity.
  • Segregation of duties.

Microsoft Discovery and Risk Assessment

This is actually part of the ACM 2013 suite of products (discussed in the previous section).  This product was formerly known as Prodiance eDiscovery.

Capabilities: 

  • Creates an inventory of Excel workbooks and Access databases within the file system and/or SharePoint document libraries.
  • Rates the level of complexity, materiality/impact, and risk to the organization.
  • Identifies errors in formulas.
  • Identifies broken links.
  • Creation of custom rules for compliance purposes.

More info: Use Microsoft Discovery and Risk Assessment

Finding More Information

Excel Blog – Introducing spreadsheet controls in Office 2013!

Access Blog – Feral Cats:  Managing Access databases in your organization