Back
Excel

New server release: Spreadsheet controls in Office 2013

This blog post was originally published in September 2012, and we’ve updated it to announce the recent release of the server applications. It is brought to you by Steve Kraynak, Office Program Manager focusing on spreadsheet management features.

Excel spreadsheet controlsIn April, we released 2 important new spreadsheet management server-based applications to complement the desktop spreadsheet management features we introduced with the release of Office 2013.   

Now available are Audit and Control Management Server (ACM), and Discovery and Risk Assessment, which are both designed to help you manage the use of spreadsheets and Access databases. Now there are 5 features designed to help you manage the use of Excel spreadsheets and Access databases:  We talked about these applications in a post last September, and now they’re available to customers:

  • Audit and Control Management Server (new)
  • Discovery and Risk Assessment (new)
  • Spreadsheet Inquire
  • Spreadsheet Compare
  • Database Compare

Some background: EUCs

The acronym EUC, which refers to End-User Computing applications, has become a common way to say “critical spreadsheets and databases created by end users without the involvement of IT.”  The 2 new applications are aimed at helping organizations deal with the significant risks introduced by EUCs.  
 
In many ways, EUCs are a very good thing.  A powerful tool like Microsoft Excel gives the end user a fantastic amount of ability, agility, and efficiency.  However, with great power comes the chance to make some mistakes.  In many situations, these EUCs are very important spreadsheets or databases that are designed, developed, tested, accepted, and used by a single person or a small group of people.  It’s the norm that EUCs are created on the fly, without a structured development process (design, develop, test). Since we know that humans occasionally make mistakes, it’s a good bet that mistakes will make their way into the EUCs and that these mistakes will go unnoticed until something bad happens. 
 
The data produced by the EUCs is typically trusted and accepted by stakeholders, and often becomes the basis for critical decisions and reporting.  Without proper controls, use of EUCs can lead to financial and operational loss, through undetected errors, unexpected mistakes, and even fraud. 

What’s new in Office 2013

With the 5 new applications mentioned above, we now offer much-needed tools to help organizations begin to gain control of their EUCs: 

  • Now, you can find and assess the complexity and risk of your EUCs using Microsoft Office 2013′s Discovery and Risk Assessment.
  • End users, auditors, spreadsheet developers, and analysts now have a powerful analytical tools for spreadsheets with the introduction of Spreadsheet Inquire.
  • Anyone can quickly and easily determine differences between any 2 spreadsheets by using Spreadsheet Compare, and likewise for Access databases with Database Compare.
  • And, to round out the suite of EUC control applications, your organization can monitor and track changes down to the cell level using Audit and Control Management Server.

How to get the new stuff

Three of the new tools are available on the desktop with Office 365 ProPlus or Office Professional Plus 2013.  These include Spreadsheet Inquire, Spreadsheet Compare and Database Compare.  Because Spreadsheet Inquire is an Excel Add-in, you need to turn it on in your Excel Options to start using it.  Spreadsheet Compare and Database Compare will show up as shortcuts along with the rest of your Office tools in the Windows Start Menu.
 
The server products, ACM Server and Discovery and Risk Assessment, are available through volume licensing and enterprises can talk to their Microsoft sales representative to learn more about licensing with SharePoint and Office.

Where do I begin? Understanding the scope of your risk

DRA iconOne of the first steps in tackling any problem is to understand the problem. If an organization hasn’t taken stock of their EUCs, and hasn’t put in place adequate procedures and controls, then chances are good that no one knows how much risk they have with EUCs, or even how many EUCs they have.  Discovery and Risk Assessment gives organizations the ability to create an inventory of their EUCs and analyze them so they can determine which EUCs are the most complex, impactful, and risky. 

Simply having an inventory is one important aspect of controlling EUCs in an organization.  The new Discovery and Risk Assessment application also helps you determine which are the most risky, so you can start taking further steps to reduce your risks.  It does this by crawling specified network paths and SharePoint sites to find the EUCs, and then it analyzes each EUC and gives them a rating for complexity, materiality (impact to the organization), and risk based on configurable criteria.

Finally! Comparison tools for Excel (and Access, too)

SC Compare iconThe new desktop tools provide some great efficiency improvements.  If you ever wanted an easy way to compare 2 spreadsheets, now you have it.  Spreadsheet Compare lets you pick any 2 workbooks and compares them in a fraction of the time it would take to you to do it manually.  Also, the differences between spreadsheets are categorized so it’s easy to focus on important changes, like changes to formulas or VBA.  You can also see changes to the data in cells.  Spreadsheet Compare makes it easy to distinguish between the different types of changes that can occur in a spreadsheet.  Also, Spreadsheet Compare is able to determine when rows or columns have been inserted or deleted and it factors those changes into the equation before comparing the cells that may have shifted as a result. Rather than showing differences just because thousands of cells moved down by one row, Spreadsheet Compare can simply report that a row was inserted.

DB Compare iconDatabase Compare provides similar capability for Access databases.  Now you can pick any 2 Access databases and get a report of the differences between the tables, queries, modules and more.  If someone changes an important query, now you can easily see exactly what was changed.

Powerful analytical capabilities

Spreadsheet Inquire iconSpreadsheet Inquire gives you the ability to perform a detailed analysis of a workbook in Excel.  You can think of the Workbook Analysis as an MRI for spreadsheets. The Workbook Analysis feature gives you the ability to find and report on almost 50 different aspects of your spreadsheet.  For example, you can easily find all the formulas with errors, inconsistent formulas, or invisible cells, and you can create a report with all this information so you can begin to document and understand your spreadsheets better, which is a critical aspect of any good EUC control program. 

Another highlight of Spreadsheet Inquire is the Workbook Relationship Diagram.  This feature maps out the data lineage of your workbook.  If you have linked workbooks or data connections, now you can quickly see of where all the data is coming from, whether you have broken links, and whether the data is current. On the other hand, maybe you’re in the process of developing a new spreadsheet.  In that case, you can use these same tools to help you build a better spreadsheet from the start. 

Keep track of changes in all of your mission-critical EUCs

The workhorse of all these new products is Audit and Control Management (ACM) Server, which provides powerful change management features for Excel and Access files.  ACM Server keeps a close eye on your critical EUCs and gives you the ability to see the “who, what, where, and when” for changes in EUCs down at the cell level.  You can see if someone has modified formulas or VBA code, and ACM Server will show you the old value and the new value after the change.  ACM Server also keeps the revision history for your files, so you can easily compare any 2 versions, or even restore a prior version. 

To get more information about ACM Server and the rest of the spreadsheet control tools, please read Spreadsheet Management with the new Office