Introducing spreadsheet controls in the new Office

This blog post is brought to you by Steve Kraynak, Program Manager on the Excel Services team.

We have 5 brand new Excel, Web Excel and SharePoint features to introduce to you in the new Office, all designed to help you manage the use of spreadsheets and Access databases. I’ll tell you about each of them in more detail, but the names really speak for themselves:

· Audit and Control Management Server

· Discovery and Risk Assessment

· Spreadsheet Inquire

· Spreadsheet Compare

· Database Compare

Some background – EUCs

The acronym “EUC” (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 applications I’m introducing to you 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 Excel gives the end-user a fantastic amount of ability, agility, and efficiency. However, with great power comes the chance to make some really awful mistakes. In many situations, these EUCs are very important applications that are designed, developed, tested, accepted, and used by a single person or a small group of people. It’s the norm that EUC’s 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 EUC’s. Actually, I think it’s a good bet that most EUC’s will have errors, and often the errors and mistakes will go unnoticed until something really bad happens.

The data produced by the EUCs is then trusted and accepted by management and other end-users, and becomes the basis for critical business decisions and reporting. Without proper controls, use of EUC’s can lead to significant financial and operational loss, through undetected errors, unexpected mistakes, and even fraud. A quick Bing search will find you many stories about mistakes in spreadsheets.

In 2011, Microsoft acquired the Prodiance Corporation, which was one of the leading providers of software for spreadsheet controls, and since then, the Excel Services team has been working hard to integrate and adapt the Prodiance technology as a major new offering for Microsoft Office 2013. Through the acquisition of Prodiance, Microsoft will deliver in our upcoming Office 2013 release a comprehensive solution to help organizations establish proper safeguards and controls over mission critical EUCs. You can find out more about the Prodiance acquisition here.

What’s new in Office 2013

With the five new applications mentioned above, we’re offering some much needed tools to help organizations begin to gain control of their EUCs. Now, organizations can find and assess the complexity and risk of their EUCs using Microsoft Office Discovery and Risk Assessment 2013. End-users, auditors, spreadsheet developers, and analysts now have a powerful analytical tools for spreadsheets with the introduction of Microsoft Office Spreadsheet Inquire 2013. Anyone can quickly and easily determine differences between any 2 spreadsheets with Microsoft Office Spreadsheet Compare 2013, and likewise for Access databases with Microsoft Office Database Compare 2013. And, to round out the suite of EUC control applications, organizations can monitor and track changes down to the cell level using Microsoft Office Audit and Control Management Server 2013.

How to get the new stuff

You can preview all of the new applications today by trying the Office customer preview.

Three of the new tools will be available on the desktop with Office ProPlus. This includes Spreadsheet Inquire, Spreadsheet Compare and Database Compare. Since Spreadsheet Inquire is an Excel Add-in, you just 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.

Then we have Audit and Control Management Server and Discovery and Risk Assessment, which are server products available in the Office customer preview.

Where do I begin – understanding the scope of your risk.

One 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 of a problem they have with EUCs, or even how many EUCs they have. Microsoft Office 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! A comparison tool for Excel (and Access, too)

The 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, but the point is that there are different types of changes, and Spreadsheet Compare makes it easy to distinguish one from the other. 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.

Database 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 gives you the ability to perform a detailed analysis of a workbook right in Excel. We often refer to 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, this feature gives you a fast and important view 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 just developing a 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 your EUCs

The workhorse of all these new products is our Audit and Control Management (ACM) Server, which provides powerful change management features for Excel and Access files, and it is complemented by Microsoft Discovery and Risk Assessment, mentioned earlier. ACM Server will keep a close eye on your critical EUCs and gives you the ability to see who has made changes to your EUCs, and when, as well as showing you exactly what the change was, down to the cell level. You can see if someone has modified a formula or VBA code, and ACM Server will show you the old value and the new value after the change. ACM Server also keeps a version history for your files, so you can easily compare any 2 versions, or even restore a prior version. There are lots of other features, too, like the email alerts for important changes to your EUCs. To try ACM Server, please visit this information page.

I’m really excited about the upcoming release of these products, and we as a team are looking forward to making an impact in the world of EUCs. We would love to hear your feedback about these new features. Please reply below and let us hear you! Are these features hitting the mark? What do you think, what do you love, what’s not so great? What else would help?