Road-out-of-Excel-Hell

For those who have been there, who are there now, or—whether they acknowledge it or not—who are headed there, the indications of Excel Hell are clear and imposing. Indeed, those indications are something like oversized, flashing neon signs that scare the daylights out of us…or which we ignore at our own peril (ok, maybe not the peril of damnation, but still…).

Submitted below  are prominent signs of Excel Hell. Then below each is a suggested step that can map the road out and up, in order to put Excel Hell behind you.

5 Signs You Are in Excel Hell

  1. Massive Overuse of Excel as a Database
  2. Errors Ad Infinitum
  3. Spreadsheet "Time Suck" to the Max
  4. Un-shareable, That's What You Are
  5. My Data Collection Waaay Overfloweth

An important NOTE: We do NOT suggest, in any way, abandoning Excel. Excel itself is not hell; rather the way we use the everyday spreadsheet, for want of better tools and methodologies, leads us to the awful place. We at BI Tech are full-on advocates of Excel for planning and reporting, and so every suggested step keeps Excel firmly in the picture. And that picture should be: a bright, sunny, (maybe even heavenly) picture of successful Excel modeling (but without the hellish problems)!

Massive Overuse of Excel as a Database

Excel as a databaseAll hellish problems with Excel follow from its overuse—typically for purposes associated with financial reporting (presentation of numbers/graphical elements); analytics (performing calculations), and; enterprise planning (using spreadsheets as an unstructured data-gathering tool). Indeed, each of the signposts of Excel hell that follow could be made subtopics of this problem of “overuse” (though each deserves its own signpost). We can summarize by saying that the fundamental problem arises from using Excel as a database.

The Way Out: utilize a technology that disburdens Excel. And I don’t mean an Access database (which requires clunky queries), or even SQL Server SSAS, which has no real data collection capabilities (at least for anything-but-the -simplest planning applications). Rather, a dimensional database that can take as much of the “weight” of carrying and calculating data out of Excel as is technologically possible. Connecting Excel to a database, a multi-dimensional one like Olation, allows Excel to do all the tremendous things it does best, as a row-and-column, graphical-ready, one-off calculation-capable, data-entry (though not data storage) template for business applications.

Errors Ad Infinitum

Excel HellThe tales of woe associated with mistyped or other spreadsheet-formula errors are legion, and the dollars lost as a consequence, innumerable. And yet there is just no way to escape—if you are using Excel only—the repetition of increasingly complex cell-by-cell formulas within spreadsheets and across workbooks. Probability dictates, and statistics support, that “mistakes will be made”!

The Way Out: There is a way to centralize formulas and vastly minimize the incidence of calculations: rather than storing them in your Excel model, keep them in a place where they are secure and their governance is assured; and where formulas operate in such a way that they calculate by pointing to a defined element. For example: Gross Margin Percentage—a calculated element that can be referenced easily across all dimensions (e.g., for all Product Sales, by Region, by Year, by Month, by Plan version, etc.)—rather than pointing to a single row-by-column cell. Such multidimensional, analytical databases exist: cube models that can condense formula logic whereby a single defined metric or business rule (1st Qtr = Jan + Feb + Mar) can substitute for innumerable cells in a workbook. These centralized formulas dynamically reflect results in cells…but are safe from being typed over (once again, governance and security), as is forever a danger in Excel-only business application models.

Spreadsheet “Time Suck” to the Max

Excel HellNeed we even speak about the time that not one, but dozens of staff—or even 100s of people in larger firms—many with MBAs, spend pouring over, tending, versioning, reconciling, etc. etc. that most common Business Intelligence tool known to humanity? Hell, indeed!

The Way Out: Any solution that ensures productivity for a group—often a quite large group—of Excel users should include one certain component: Excel itself. There is no lack of vendors who identify Excel as “the problem”; but that is no more true than the everyday spreadsheet being identified as “Excel hell.” Users love Excel the world over—isn’t the logical solution to knit Excel, in a dynamic, seamless way, into a system that leverages that sentiment, and key off the familiarity and overall usefulness of Excel?

Un-shareable, That’s What You Are

Excel HellOf course, spreadsheets can be shared—how often have you sent a file attachment to an email with a spreadsheet containing worked-up report numbers? Or been sent an Excel budget template and been asked to fill it in and send it back with your latest plan figures? SharePoint is a way to share Excel files…but when we speak of “shareable” in the context of a business application, we mean utilizing Excel as a collaborative portal to a model of shared data—for reports, analytics and or plan data. Excel, on its own, fails at this, with often disastrous results.

The Way Out: In order to be utilized as a portal to a shared reporting, forecasting, or planning model, Excel needs something behind it that allows for collaboration: it requires that a spreadsheet—any enabled spreadsheet—provide dynamic connectivity to that model. That “something behind it” must return values for key business rules and metrics, plus allow for entry of plan numbers. Excel on its own is not the solution for shared modeling: it requires an engine, an application, an optimized analytical database for all kinds of planning, forecasting and reporting. Bonus point: the shared, collaborative model could use Excel as one of many touch points, which might include visualization tools, report writers, and even other BI vendors’ products.

My Data Collection Waaaay Overfloweth

Excel HellSpeaking of innumerable, disparate spreadsheets flying hither and yon (see above): that may be the very definition of Excel Hell—certainly from an IT perspective. Companies put themselves at great risk, especially by using Excel to collect “plan data”—i.e., for budget and forecast numbers submitted by staff…so many spreadsheets unsecured, liable to be deleted or overwritten, and almost certainly bound to be mistyped or miscalculated by the poor souls who try to put them all together in yet another unstructured Excel mega-workbook.

The Way Out: Once again we point the way to a shared, collaborative Excel model—but with an all-important “extra”: a modeling environment that will keep even plan data stored in a statutory system, like SQL Server. IT people, Rejoice!

Putting It All Together –
to Reach New Heights with Excel

Excel Hell

 

It’s conceivable that you might find a solution to any one or several of the above serious issues (or, dare we call them “sins”?) associated with Excel Hell. But that would leave you at the mercy of other issues, keeping you in that place that is the bane of so many business firms. Indeed, a holistic approach—call it a blanket redemptive effort—to lead yourself out of Excel Hell is the only way to go.

 

 

We at BI Tech feature solutions that meet all the criteria above, incorporating the latest technologies that ensure collaborative reporting/analytics/planning models that work seamlessly with all your current applications and products—including the best (for many) and hardest to part with: Excel!

----

You might be interested in related blog:

Excel Hell Whitepaper

Recent Posts

Subscribe to our blog!