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

Excel Strategies

You REALLY Don’t Know What Excel Can Do!

Here's how cutting-edge companies that understand Excel's real power have turned Excel users into a strategic resource.

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

You really need to expand your thinking about the power that Excel can give you and your company.

The simple fact is that Excel can make a significant contribution to your company’s success, in spite of common wisdom to the contrary.

You’ve been told that Excel is a “personal productivity tool” that should be replaced immediately with several of the many expensive, special-purpose products that compete with different uses of Excel.

You’ve been asked, “Are you still using spreadsheets?” by consultants, software vendors, training companies, and the publications that serve them.

You’ve seen your company’s Excel users struggling with their Excel work…possibly confirming your worst fears about Excel.

But meanwhile, other medium and large companies are treating Excel, Excel users—and the products that empower them—as a strategic asset.

An Excel Success Story

One thriving retailer with thousands of stores offers a great example. Their executives can receive real-time, currency-translated, world-wide sales results, at any level of consolidation—on their mobile phones, in workbooks, and via nearly any Business Intelligence front end.

Do you want to know what your worldwide sales were in the last hour? Yesterday? Last week? The executives at this retailer just ask whatever end-user tool is available…including Excel.

One executive admits that he's addicted to tracking worldwide hourly sales on his mobile phone.

To give Excel users universal connectivity, the company linked Excel to the Realtime Data Hub that manages the data, calculations, and connections. By doing so, they gave worksheet formulas read-write access to their Big Data…all with proper security, of course.

Their department managers can update their budgets with Excel. Their budget managers can see those entries in real time, at any level of consolidation.

Their accountants can create and commit journal entries with Excel…because their Realtime Data Hub supports read/write access to their ERP. That is, the RDH allows Excel to write journal transactions just like an ERP can...again, with proper security.

Their product planners enter their sales plans into a workbook—at the store level by SKU. Meanwhile, corporate planners forecast from the top down in Excel. And then, if the plans and forecasts aren’t similar at various levels of consolidation, everyone knows they have a problem to solve.

The company hired people with advanced degrees to develop specialized, proprietary analytics. The calculations they developed are updated in real time by the RDH, and their results are presented like data to the rest of the company.

All users—including Excel users—have real-time access to data from virtually any available source. Sources can include data from the Web, and from Power Pivot, and from SAP HANA, Dynamics, SQL Server, SharePoint, Oracle, calculated results, and other sources…simultaneously.

Let’s be clear about Excel’s involvement here…

An analyst can change one number in an Excel forecast at a coffee shop in London, which—instantly—can update a relational record in a SQL Server file in Seattle, which—instantly—can be displayed with Tableau in a hotel in Bozeman, and also which—instantly—can appear in a worksheet in Winnipeg...along with sales data consolidated in real time from SAP in one division and Oracle Financials in another, and also financial data for competitors imported from

From another perspective, a short macro can turn Excel into a powerful data-mining tool…a tool that could rely on thousands of formulas to analyze hundreds of millions of views of the data—obtained from any number of sources—to uncover significant problems and opportunities hidden in hundreds of gigabytes of data.

Which products in which divisions report daily revenues that are highly correlated with the weather? Or with web traffic? Does the sale of certain products indicate the likely sale of other products within the following two weeks? What drivers could increase those follow-on sales? Simple Excel data-mining macros could tell you.

In short, Excel is the key to the kingdom. It’s the one tool that can empower knowledge workers—not programmers—to explore, forecast, explain, data-mine, and collaborate about a galaxy of data from any number of available sources.

A Contrary Opinion

This wide range of enhanced Excel capabilities occurred to me when I read an article on the Harvard Business Review web site, To Survive, Health Care Data Providers Need to Stop Selling Data.

I didn't agree with their recommendation, and I have to think about why this was so. 

“Most data-driven healthcare IT (HCIT) providers aren’t going to survive,” the authors begin. “Their business models are at serious risk of failure in the next three to five years. To beat those odds, they need to evolve dramatically, and fast, to a point where they are not selling data at all.”

“End users,” they tell us, “can be overwhelmed by the flood of raw data and reports that may not fit well with their existing workflow or answer their specific question.”

What should the HCIT providers do? The providers’ best option, the authors tell us, is to “evolve from providing data to providing insight.”

For decades, I've seen the power that Excel offers when it's live-linked to massive amounts of data. And I've never seen an Excel user overwhelmed by gigabytes of data on a server. So a different idea occurred to me...

I suggest that HCIT providers help their customers develop their own insights. Those insights can be found by combining HCIT data, customers' own data, and other data available from other the creativity and experience of key knowledge workers, particularly Excel users.

More than anyone else, Excel users tend to understand what their company’s data actually means. They understand their company’s industry and business problems. They understand their managers’ information needs. And many of them hold advanced degrees in subjects that could help them to discover problems and opportunities in massive amounts of data...information that no one else would ever think to look for.

Good Excel users don’t get “overwhelmed by the flood of raw data.” They get overwhelmed by the massive work caused by static data—that is, numbers in cells.

But giving Excel users formula-access to dynamic, relevant, Big Data will slash their work. It will make them—and their company—much more productive.

The technology is available now. It’s time to use it.


Charley's SwipeFile charts

Free Excel Dashboards