For business users of Microsoft Excel Free guides and templates
Home >  Ideas > 

Excel Strategies

Spreadsheet Hell? The Partnership of OLAP and Excel Brings a Cure

Late nights? Broken formulas? Manual data entry? Broken macros? Embarrassing errors? Monster spreadsheets? Last-minute panics? Welcome to Spreadsheet Hell!

by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Let's talk about Spreadsheet Hell...and how to cure it.

Typically, you enter Spreadsheet Hell when you start manual spreadsheet reporting.  

To get raw data into your spreadsheet, you might copy and paste it to Excel, or import it as a file. Or perhaps you use an Excel-surly OLAP. Or worse, perhaps you key it into Excel.

Unless you're very lucky, the data isn't at the right level of summary. Or it's for the wrong month. Or it's incomplete. Or it was generated using an incorrect query. Or whatever.

Once you fix the data, you often need to add and correct data from other sources. Then you typically need to sort the data, summarize it, and format it. You've got to add new rows and columns, write new formulas, and format the results. Then, you really ought to check for errors.

Finally, just when your report is done, someone mentions that the source data has changed, and you've got to start all over again.

And all the while, you've got to answer concerned questions like, "When will my report be ready?" and "What do you do all day?"

The Excel-OLAP Connection

This figure illustrates a better approach.

Here, your company stores different types of data in different cubes of an Excel-friendly OLAP database. Then Excel users rely on INDEX-like formulas to return any of this data from any number of cubes to any spreadsheet, a spreadsheet that works much like a template.

Unlike Excel's INDEX function, OLAP spreadsheet functions typically use labels rather than numbers to reference data.

To illustrate, suppose you have a simple cube of General Ledger data in the FinDat database in the directory C:\Finance. Let's say that the cube has three dimensions: Department, Account, and Month. The following formula could return year-to-date data for the Sales department for Account 5678 for June, 2018:

=OLAPRead("C:\Finance\FinDat.olp", "GL", "Sales", "5678", "Jun-2018 YTD")

(At ExcelUser I use Paris Technologies' PowerOLAP to illustrate Excel-friendly OLAPs. OLAPRead is a PowerOLAP function. Other Excel-friendly OLAP products use similar functions with different names.)

Because each dimension typically contains consolidation hierarchies, your formulas can return data from any level of consolidation for any dimension. To illustrate, the following formula could return company-wide labor expenses for the month of June, 2018:

=OLAPRead("C:\Finance\FinDat.olp", "GL", "Total Departments", "Total Labor Expense", "Jun-2018")

In practice, the labels typically will be in cells, which often are named. Therefore, your actual formula might be in cell D9 and look something like this:

=OLAPRead(DBPath, "GL", D$3, $A9, Month)

Here, your database path might be entered in a cell named "DBPath". And you might have other department names in row 3 and other expense categories in column A. You could report performance for any month merely by entering a month-year label in the cell named Month, and then recalculating.

Why Excel-Friendly OLAP Cures Spreadsheet Hell

Using Excel formulas to populate spreadsheet data is a simple idea, but a powerful one. It offers Excel users escape from Spreadsheet Hell. Here are three reasons this is so:

1. Every report acts as a template.

Suppose you create a "one-time" report for August, and then your boss asks for an update for September. Or suppose she asks you to create a similar report for other departments and other divisions.

If you were trapped in Spreadsheet Hell, you would need to create each version of the report from scratch each month. But with OLAP, there's no problem. You merely enter the new labels for department, division, and month, into the cells you've set up to contain this information. And then you recalculate Excel—that is, press the F9 key.

Similarly, if there's a last-minute change to your data, there's no problem. You merely open your spreadsheet report, recalculate, and distribute it.

2. Reports are easy to reconcile.

Suppose your company relies on two Gross Margin reports. One analyst prepares a report of Gross Margin by Division by Month, and another analyst prepares a report of Gross Margin by Product Line by Month. And suppose that the Grand Totals of the two reports don't match.

If you were trapped in Spreadsheet Hell, you might need to compare two SQL queries or two spreadsheet databases that don't quite agree. And the differences might never be resolved.

But these reports would be easy to reconcile if both get their data from an Excel-friendly OLAP database. Because both sets of formulas get their data from exactly the same source, it's easy to compare a representative formula from each report to learn where one report is picking up data that the other report is missing.

3. Reports can include data from any source.

Many managers would like to see reports that include data from external sources, or from internal sources not available to the IT Department.

Perhaps managers want to track the financial performance of major customers, vendors, and acquisition candidates. Perhaps they need to track the prices of key commodities, monitor currency exchange rates, or follow economic performance in key geographic regions.

Or perhaps they want to see consolidated financials that include the performance of a new acquisition, even though the IT Department can't merge the acquired company's data for at least two years.

Or perhaps they want to compare trends in sales at a specific location with ad buys or weather records.

If you were trapped in Spreadsheet Hell, non-IT data would be a particular problem. This is because the only place to store external data would be in a workbook on some analyst's hard drive.

But with an Excel-friendly OLAP, these types of reports are no problem. The external data is merely imported into an OLAP cube. And then any Excel user with access to the OLAP system—throughout the company—can use that data in any report or analysis.

So here's the bottom line...

...A spreadsheet-friendly OLAP database offers the most effective cure I know for Spreadsheet Hell.


Charley's SwipeFile charts

Free Excel Dashboards