For business users of Microsoft Excel Free guides and templates

Reporting Strategies

Ten Reasons Excel-Friendly OLAP Belongs on Excel Users' Desktops

What's the most valuable technology for business users of Excel? It may well be the multi-cube database technology, called OLAP.

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

As an Excel user, I've been excited about Excel-friendly OLAP programs for more than 20 years. Here are some reasons why:

1. Worksheet formulas are live-linked to data.

When we use Excel-friendly OLAP, our workbooks don't contain data; they contain formulas linked to data. And that means...

  There's never any doubt about the source of any value. And that means we can easly audit our workbooks.

  We can update our workbooks instantly.

  We can create interactive workbooks easily, so we can report different Divisions, Products, Regions, and so on nearly automatically.

  All Excel reports of the same data always display the same numbers...anywhere in the world.

2. Easy budgeting, forecasting, and other planning.

All Excel-friendly OLAP products offer two ways to write back from workbooks to the OLAP database.

First, they offer a special worksheet function that returns a value from the server. The function is special because if you enter a value into the cell with the formula, the software writes the new value to the server and then restores the original formula, which displays the new value.

This feature allows department managers to manually enter their budget data into worksheets while budget managers can track budgets in real time.

Second, they offer a worksheet function that writes a value to the server whenever Excel is recalculated. This lets you set up formulas that calculate a forecast and write its results to the server whenever you recalculate Excel.

Because everything happens in real time, users all over the world—who have the appropriate credentials—can see up-to-the-second planning data.

 3. Real-time consolidation and translation.

A senior manager of one of the largest companies in the world says he's addicted to using his OLAP database to track currency-translated, fully consolidated, world-wide sales in real time.

On a more practical basis, it's virtually impossible for two managers on opposite sides of the world to see competing values for the same number. This is because both of their Excel spreadsheets return the same value from the server.

4. One version of the truth.

Most Excel users are accustomed to working with many versions of the truth.

  Joe's reports use a PivotTable query written two years ago by a programmer who's long gone. Sally, on the other hand, writes similar reports using her own query against similar data. For some reason, her numbers never quite agree with Joe's.

  When Sally first ran the July report, she got one number for Total Sales. Several months later she ran the same report and got a slightly different number. She now maintains a spreadsheet database with each month's data.

  Because Joe had the same problem, he also saves his data in Excel. Unfortunately, the two spreadsheet databases never quite agree. Some co-workers link their spreadsheets to Sally's data; others link to Joe's data.

  Neither person can reconcile their numbers to similar numbers produced by their General Ledger software. They do try to keep their results close, of course. And rounding helps.

On the other hand, with OLAP, there's one version of the truth. Each Excel formula that returns August sales for the Northeast division will return the same number. This is because each Excel formula gets that number from exactly the same cell in the OLAP database.

5. Data from any number of sources

Excel users in medium and large companies can get access to data controlled by the IT Department. But many managers need data from sources beyond the control of IT. For example...

  Many subscription data sources provide data that's critical for tactical and strategic planning.

  Sources like (a D&B company), provide data about key customers, competitors, and markets.

  The Federal Reserve Bank of St. Louis currently provides economic and population data for 489,000 US and international time series from 85 sources.

  Google Analytics provides detailed data about traffic to your web site.


An OLAP database can contain data like this for your formulas to report and analyze in Excel. But your IT department can't.

6. Cube formulas

OLAP cubes don't merely contain data. They also can contain multi-dimensional formulas.

For example, you can set up OLAP formulas to...

  Translate an entire multi-currency database to any specific currency for reporting and analysis.

  Calculate standard metrics for any division, department, region, and so on, at any level of consolidation.

  Calculate standard forecasts for any data set.


With cube formulas available, forcasting can be as easy as choosing the method your manager prefers from a list of available methods.

7. Fast report development

Most reports start with data. Excel-friendly OLAPs provide an interface that allows users to explore that data quickly and easily--and then use it in Excel.

For example, you could look at sales by product by month for a particular region and customer class. Or sales by region by customer class for a particular month and product. Or whatever.

ThenThen, when you have a rough version of what you want your report to contain, you can click a button to drop the report into Excel. Many OLAPs can take this step. But most of them write raw data to your spreadsheet. Only the Excel-friendly ones give you Excel formulas that return your data from the server.

With the sample formulas in your spreadsheet, you can add your own touches. You can insert rows and columns. Add calculations. Add or delete formulas. Whatever.

Suppose you need data from a second cube, from the Headcount cube, for example. You would follow the same approach to create the initial view of your data. Then you merely copy the formulas you need into your first spreadsheet. Or you can create your formulas from scratch.

SoonSoon, you can create sophisticated spreadsheet reports that update automatically, with very little work.

8. Immediate spreadsheet updating.

Suppose you create a report for the Southwest Division for April. You show it to your boss, who says, "That's great! I'd like to see an update every month! Oh, and while you're at it, would you give me a similar report for each of the other divisions, and for the company as a whole?"

In the past, this request was really bad news. It meant that you would have to work long hours to create each report. For companies that rely on PivotTables, the problem isn't quite as bad. But for OLAP users, this request is no problem at all.

UserUsers of Excel-friendly OLAPs have it easy. To change divisions, they change the cell with the division label from "Southwest" to, say, "Central". Then they recalculate and print. Or, they enter, say, July in the cell they've set up for the month label. Then they recalculate and print.

9. Independence from IT

Excel users tend to have an uneasy relationship with programmers. Users understand what the data means, but they don't have easy access to the data. Programmers have access, but they don't understand what the data means.

Both groups would benefit if users could have easier access to data. Users benefit because they can create reports and analyses more quickly and easily. Programmers benefit because they can reduce the number of user queries that they must write.

With OLAP, programmers work with users to define and error-check the simple query that populates each cube. Then, period after period, IT runs the same query to update the cube.

ExceExcel users do the rest.

10. Reduced errors

Typical Excel reports are filled with errors. But reports linked to OLAP cubes have much less possibility of error. Here are some reasons:

  Typical Excel reports contain numbers -- values -- in many cells. Any of those numbers could have been accidentally changed in the worksheet. But because Excel-friendly OLAPs use formulas to return data from cubes, there's much less chance of error.

  Typical Excel reports have no practical way to reconcile their results to an ultimate truth. But most OLAP-based Excel reports have that power. To illustrate, a report of Sales by Division by Month could include a simple reconciliation formula outside the print area. The formula could return "Error!" if the spreadsheet total of the sales for all divisions doesn't match the equivalent value returned from the OLAP cube.

  Typical Excel reports often use data from spreadsheet databases, many of which contain errors. But with OLAP, there's no need for these private databases. Because everyone uses the same data, it's much more accurate and certainly more consistent.

  Typical Excel reports, which rely on numbers in cells, have no practical way to adapt after their source data has been changed. But users can refresh OLAP-based Excel reports merely by recalculating their workbooks.



Free Excel Dashboards

Charley's SwipeFile charts