Ultimate Excel finance guide
A comprehensive collection of articles, videos and more, hand-picked by our editors
Business intelligence managers once talked longingly about weaning end users off their Excel addictions to help ensure that BI data remained consistent and secure. Yeah, right: In most cases, efforts to eradicate Excel from BI processes went nowhere, forcing BI teams to shift to a strategy of accommodating rather than eliminating it.
“We have fought the war for 20 years and we have lost,” said Boris Evelson, an analyst at Forrester Research Inc. in Cambridge, Mass. What BI managers need to do now, he added, is work to control and manage Excel BI use as best they can. For example, Evelson recommended that Excel be integrated with BI software that can export data to spreadsheets, enabling Excel to function more as a user interface and data presentation layer than as a data manipulation tool itself.
There’s no simple recipe, though, for keeping business users from going in their own directions with Excel. “It’s sort of like herding cats,” said Rick Sherman, founder of consultancy Athena IT Solutions in Stow, Mass. “You’re never going to fully control Excel. Anyone who thinks they will is fooling themselves.”
Sherman, who said business users “will go kicking and screaming before they ever stop using Excel,” suggested that the top priority should be ensuring that everyone in a company is working off of the same data, even if users are formatting it in different ways in Excel spreadsheets and creating their own charts and other graphics.
To achieve that goal, Sherman, Evelson and Bill Brydges, a managing director at consulting company MorganFranklin in McLean, Va., all cited the same keyword: governance.
Excel BI use OK here, not there
Evelson said companies should implement spreadsheet-usage governance policies that are flexible but will be enforced. Under such policies, Excel use could be allowed for reporting and analysis within individual workgroups but not for enterprisewide BI functions. Nor should it be allowed as part of particularly sensitive processes, such as regulatory reporting, he said.
Similarly, Sherman said companies could extend existing data governance programs to include Excel usage. He cautioned, though, that BI managers should tread lightly and try to sell proposed governance policies on the basis of factors such as potential productivity improvements they could lead to or the need to provide uniform BI data and findings to corporate executives. “If you try to be heavy-handed, that’s a losing battle,” he said.
Excel usage for BI purposes is “more of a symptom than a problem in and of itself,” Brydges said. “People turn to Excel when it is the only tool they have to manipulate data.” To prevent its use from becoming problematic, he advised, BI teams should incorporate Excel as a key component of fully integrated BI processes so it isn’t treated as a standalone technology or a bridge to BI systems.
“Users will continue to rely on Excel and other tools to get simple tasks done, and they should,” Brydges said. “The point is to control the governance of when data from that type of approach is integrated into enterprise business processes and reporting.”
Don’t just eyeball your Excel data
For example, he said BI teams could build predefined thresholding and balancing calculations into Excel to automate error-checking instead of relying on business users to eyeball rows and columns of data to try to spot incorrect values or misapplied formulas. He added that it might also be useful to control access to specific Excel spreadsheets through encryption and password protection, if you can get approval for doing so. Microsoft’s SharePoint software and various third-party applications can help with such protections, Brydges said.
Sherman also cited SharePoint’s potential to help Microsoft-oriented organizations keep a firmer grip on Excel BI use. SharePoint enables companies to use Microsoft’s Excel Services software to share and reuse Excel workbooks; they also can deploy BI components such as the PowerPivot add-in for Excel at the server level, making it easier to ensure that data integration and other data management tasks are done outside of individual spreadsheets, Sherman said.
He added, though, that BI tools in general support tighter integration with Excel than they did in the days when spreadsheets were viewed as the enemy of proper business intelligence techniques. For the most part, he said, BI vendors have now adopted a coexistence strategy when it comes to dealing with Excel.
Charles Linville, founder and president of Ploughman Analytics Inc., a consulting and analytics services company in Champaign, Ill., said the ultimate carrot for keeping BI users from looking to use Excel in rogue ways is to make sure that their data needs are met in a timely fashion. Linville added that if users need to get multiple levels of approval for data requests and then wait for BI or IT workers to do the required development or data manipulation work, turning to Excel is only natural. “Where is the surprise,” he asked, “that they often decide to see to their own needs?”
Alan R. Earls is a Boston-area freelance writer focused on business and technology. Craig Stedman, executive editor of SearchBusinessAnalytics.com, also contributed to this story.