News Stay informed about the latest enterprise technology news and product updates.

Excel in enterprises: How to deal with data integrity

Microsoft Excel in the enterprise can present problems, but there are tools and techniques for managing Excel data integrity that keep both business users and IT departments happy.

Of all the technology ever deployed in enterprises, few programs seem to inspire as much passion as Microsoft...


Any mention of the ubiquitous spreadsheet program inspires reader mail to, and the responses generally fall into two categories. Business and finance users like Excel's familiar features and say IT hasn't yet been able to give them a better tool. On the flip side, IT users lament Excel's ability to exacerbate data integrity problems and wonder why business users insist on using spreadsheets rather than the business intelligence (BI) tools they're given. Arguments on both sides are eloquent and earnest, leading one to wonder whether there is a happy medium.

For more information on Microsoft Excel data integrity:

Read more in Rick Sherman's column: Microsoft Excel: The king of BI

Learn how business intelligence software vendors say they can help Excel data integrity problems

The short answer is yes, according to Rick Sherman, founder of Stow, Mass.-based consulting firm Athena IT Solutions. He's all too familiar with the Excel data integrity debate. As a BI consultant, he frequently sees what he calls "data shadow systems" at many companies. These informal data management systems often include massive collections of Excel spreadsheets, Access databases and other tools. Data shadow systems and other informal Excel-based BI or reporting systems usually grow out of genuine business need, Sherman said. But the longer these systems persist, the greater the risks.

"There's no way to audit where or how that data is manipulated, nothing is documented and it's difficult to maintain data integrity," Sherman said.

More importantly, he said, Excel-based processes and data shadow systems put business people in the IT role of extracting and manipulating data from source systems, which takes time away from real data analysis. But Sherman and other experts agree that the "Excel problem" can be solved diplomatically.

Open the lines of communication and listen well

When IT people lay out the inefficiencies of Excel-based systems -- such as the potential for data integrity problems and the risk of hampering regulatory compliance or other revenue-impacting processes -- business users will usually listen, Sherman said. It's about putting the problem in a business context rather than a technology context. But as one reader pointed out, it's important that IT truly listen in turn. The reader wrote that she's witnessed some IT folks with an adversarial attitude toward business people who don't understand technology. This creates even more communication problems, she said.

"The fact that end users are probably not familiar with the various fields contained in the system and the system capabilities that go along with them simply means they have a different area of expertise, not that they're dumb," the reader wrote in an email to "End users get frustrated with their IT departments when they don't get the data they need, and stereotype IT staff as techies without a lick of common sense and being unable to produce the data they need from the systems."

Use Excel-based projects to determine requirements for replacement systems

One benefit of Excel projects and data shadow systems is that they provide an "instant set of requirements" for IT people looking to replace them, Sherman said. By examining what the business user is doing in his or her Excel project, IT people can get a better idea of what system would make the job easier. For companies with many Excel systems, he suggests prioritizing which to replace first.

"The ones that have a large number of users, or where specific business decisions are being made, are the ones you want to pay attention to," Sherman said.

Evaluate tools that can help -- and consider Excel as a "front end"

Excel spreadsheets shouldn't store corporate data, Sherman explained. But some tools can use Excel as a front end, or user interface, for data that's managed by IT in an analysis cube or other source, he said. That means that Excel can be a component of a BI system without being the BI system. BI vendors are now offering tighter integration with Excel, he added.

Santa Clara, Calif.-based Hyperion Solutions Corp. was one of the first vendors to offer an Excel front end for its Hyperion Essbase and recently released Hyperion System 9, Rel 9.3, an update to its performance management system that offers even tighter integration with Excel and other Office tools. In September, South San Francisco-based Actuate Corp. released Actuate 9 e.Spreadsheet, an upgrade to its server-based system for managing spreadsheets, which includes new security features for reports going to users outside a corporate firewall.

Other vendors that play well with spreadsheets are AnalySoft, Applix, Business Objects, IntelligentApps, MicroStrategy, OutlookSoft and QlikTech, Sherman wrote in a column, and there is a slew of smaller vendors as well. Then there's the longer-term promise of Microsoft's Office 2007, which will include updates to Excel Services and Microsoft Office SharePoint Server 2007 that will help companies better manage spreadsheets, according to the Office 2007 preview Web site.

When evaluating potential systems, the most important criterion is understanding how the company will maintain data integrity, Sherman said. Other considerations are user security and how to protect sensitive data.

Set ground rules about when it's OK to use Excel -- and when it's not

Excel was originally designed as a personal productivity tool and it's still good for that, according to Robert Kugel, Chartered Financial Analyst (CFA) and senior vice president and research director with San Mateo, Calif.-based analyst firm Ventana Research. But it's not appropriate for enterprise usage because it's not set up for collaborative work environments and it's not auditable, Kugel said. But there is a middle ground, he said. Companies can implement technology tools and frameworks to manage enterprise spreadsheets -- and if business users want to use Excel personally, essentially as scratch paper, they can do that too.

Ultimately, Kugel said, IT departments will be able to allow people working on enterprise projects to use spreadsheets within an enterprise framework but not force them to follow those frameworks when they're working "off the reservation."

Keep the end goal in sight

The bottom line is that the conversations about Excel between IT and business users don't have to be a battle, Sherman said. Rather than tell business users that IT is trying to "rein in" use of Excel or "get them under control," he recommends a more diplomatic approach that won't make people defensive.

"I explain to business users that what we're really trying to do is focus their efforts on data analysis and be able to act on the data," Sherman said, "as opposed to wasting all their time doing the data manipulation."

Dig Deeper on Business intelligence software