Excel Productivity

The Excel-Friendly Database Strategy

Here’s the strategy that will allow your company to solve most of its productivity problems with Excel reporting and analysis.

Recently, a reader told me how he uses Excel for reporting: “I pull reports from our system and then convert them to Excel."

That’s how I used to do it, and that’s how other readers have described the way they do it.

And ALL of us have been using the wrong strategy for Excel reporting and analysis! That strategy causes WAY too much work. It generates WAY too many errors. And it’s as agile as a tree stump.

There is a better strategy for Excel reporting, analysis, modeling, and so on—all of which I tend to call “reporting.” The simple strategy slashes Excel reporting times, slashes errors, and makes all our Excel work much more agile.

However, I’m embarrassed to admit that I’ve used spreadsheets for reporting and analysis for about 40 years, but only recently have I defined that general strategy in detail. It’s one that both very large and very small companies can use to improve their Excel productivity…significantly.

I'm soon going to introduce training and coaching about Excel productivity. You can download a free PDF of the first seven pages of one training manual here.

The following figure illustrates my simple strategy. When you follow it, you use a method that can…

• give your Excel formulas full access to unlimited data, from unlimited sources,
• slash your errors,
• update your reports automatically—without using macros.

And when you take the last step in the figure—when you GROW your system—you add a continuous-improvement process to your Excel work.

For example, you improve your error-checking, add data from other sources to your system, personalize your reports for key managers, add charts, add explanations to your reports, and so on.

Why Easy Report-Updating Matters

It’s critically important that you EASILY can update the data used in your Excel reports and analyses.

When you can update your reports easily, you become much more productive. For example, suppose you could update ALL your Excel reports automatically each month...using only Excel formulas to do it, and no other programs. How much time would that save you?

At one point in my career, it probably would have saved about half my time each month.

Automatic updating also can slash your Excel errors. This is because when your reports update automatically you give yourself no opportunity to ADD new errors to them. Instead, when you find and fix existing errors, they stay fixed. And that means that you ratchet down your error rate each time you reuse a report.

Now let’s take a closer look at each of the four steps in my new, Excel-Friendly Database Strategy…

1. Stow It

The first step is to stow—to save—your data in an Excel-Friendly Database (EFD). EFDs have this key feature:

You can use worksheet formulas to return data from them.

EFDs typically offer source agility. That is, as this figure illustrates, the data can come from a wide and growing number of sources, internal and external.

These features are a perfect fit for a business environment. This is because the Promised Land for most Excel users in business is to work where your workbooks could reference any data you need, and update automatically and accurately when you open them…

...and that’s what these methods support.

By reaching that Promised Land of Excel Productivity, you slash the time you must spend to update your workbooks, and you also…

• limit your opportunity to add new errors to your workbooks;
• reveal errors caused by unexpected changes in your data;
• cause all workbooks linked to your Excel-friendly database to report the same data;
• make it possible to deliver your updated reports and analyses on schedule; and,
• leave work at night when everyone else does.

This strategy works very well for all organizations, from the smallest through the largest.

Small organizations can use one or more Excel Tables as their Excel-friendly database. Microsoft introduced their Table technology in Excel 2007.

Initially, Tables can be in the same workbook as your reports. But as you grow your system, you can move your Tables to separate data workbooks…when you set up your range names properly. This allows all Excel users in a small organization to create reports and analyses that are live-linked to the same data workbook(s).

Medium organizations can prototype with Tables and then move their data to “cubes” on an Excel-friendly multi-user OLAP server.

They can, of course, create reports that use data from both cubes and Tables.

OLAP technology allows many Excel users with many reports and analyses to be live-linked to one source of data on a server—a source that can contain significantly more data than Tables. Changing the source of your data from Tables to cubes is easy to do in your reports, because you typically just replace one worksheet function with another in the formulas linked to your data.

This technology also allows worksheet formulas to write data to cubes—with full security, of course. The cubes also perform real-time consolidation of new data. They support Excel-user collaboration. And they support cube formulas that are the multi-dimensional equivalent of calculated columns in Excel Tables.

I’ve arranged for one of the two publishers of Excel-friendly OLAP software to let ExcelUser visitors to try a single-user version of their software for a whole year. You can download your free OLAP program here.

Large organizations can use Excel-friendly cubes in a Realtime Data Hub system. This technology, which uses Excel-friendly OLAP at its core, gives real-time, read-write access to data of all kinds, including data from SAP, Dynamics, SQL Server, Oracle, SharePoint, and other relational sources. It even works with Access and QuickBooks.

And, of course, it allows any number of Excel users with any number of workbooks to use formulas live-linked to Big Data—with full security.

To illustrate, the managers of one of the largest companies in the world run their entire company using Excel workbooks and visualization tools linked to Big Data.

The company’s cubes—load-balanced across about 2,000 CPUs—allow managers to see worldwide, currency-translated performance, at any level of consolidation…in real time. One executive admits that he’s addicted to following real-time, worldwide sales on his cell phone.

Again, you can download your free OLAP here..

2. Flow It

The second step in the strategy is to use Excel formulas to flow data from your Excel-friendly database to your reports.

That is, you “link-enable” your reports and analyses.

Formulas are Excel’s most-powerful feature, and this strategy takes full advantage of that power. By using link-enabled reports and analyses with either Tables or cubes…

• You can reuse your reports and analyses. In fact, you can design them to update accurately and automatically when you open them, without using macros…even in a new fiscal year.
• Your formulas can perform any calculation that Excel supports, a feature that’s often important for reporting, and critically important for planning and analysis.
• You can error-check your workbooks automatically.
• You can create reports that rely on Excel-friendly data gathered from any number of sources, both internal (like financial data, web logs, and operating data) and external (like benchmarks, exchange-rates, economic trends, competitor data, weather patterns, ad-placement data, and so on). To be clear, one Excel report—even one cell of one report—could combine data from any number of those sources.
• You can create interactive reports and analyses, of course.

3. Show It

The third step of the Strategy is to show your Excel results.

This is a deceptively simple statement for the vast amount of value that you can provide managers and other colleagues.

When you’re at your best, your reports give their readers insight—that is, deep understanding—about some aspect of performance. This is an invaluable service to your company, a service that’s often trivialized by the general term of “Excel reporting.”

As a group, Excel users know more about what your company’s data actually means than any other group in the company. Good Excel users know what kind of information your managers want and need, and why. And you probably have the professional training to convert both internal and external data into the business insight that your company needs.

If you have the time to work on these issues—rather than continually turning the crank—the “Show It” step can be immensely valuable. And TIME is what I designed this strategy to give you.

4. Grow It

This is the most-important step, but one that’s virtually unknown in the Excel world.

When you link-enable your reports and analyses, you move them into a system that you can continuously improve, in at least five ways…

First, because you’ll be able to update and error-check your Excel results automatically—without using macros—you’ll have the time to ask your managers how to improve the reports and analyses you give them.

Responding to managers’ requests not only makes you more popular at work, it can give managers more insight in less reading time, which can improve your company's performance. And it also generates new and specific achievements—and a level of productivity—that look great during performance reviews and on your resume.

Second, when you maintain a database within easy reach of your worksheet formulas, you can save link-enabled Excel templates. That way, when you launch a new workbook based on a link-enabled template, much of your new report—and its error-alerting system—are automatically generated. This approach gets you nearly half-done with your new Excel reports before you ever get started.

Third, after you create several reports and analyses from your initial data, you’ll probably think of other internal and external sources of data that you could add to your new Excel reporting system. By adding that data, you can make your presentations more informative and your forecasts more accurate.

Fourth, unless you have special mental powers, you’ll need to learn new skills to grow your Excel-Friendly Database system. But learning those skills should benefit…

• your company, by giving managers greater insight, which should improve business performance,
• your general Excel expertise, because this whole strategy relies on ordinary Excel formulas, formatting, charts, etc., and
• your personal life, by slashing the long hours you spend as an Excel clerk, rather than as a business professional.

Fifth, after you’ve link-enabled your Excel reports and analyses, be sure to introduce other Excel users—and their managers—to your system.

By doing so, you expand a strategy that will improve both management insight and business performance. And also, you give colleagues the chance to add data and other resources that you could use in your own reports and analyses.

In short, whether or not your company uses BI software, it certainly uses Excel. So your company should make Excel—and its Excel users—as productive and accurate as possible. Therefore, it always makes sense to enhance Excel by following my Excel-Friendly Database Strategy.

As I mentioned earlier, if you want to know more about this strategy, I’ve prepared a special PDF for you. It contains the first seven pages from Lesson 1 of Productivity, my new training and coaching program for Excel.

You can download the Productivity PDF here.