Here’s the key to slashing both your Excel errors and the time it takes you to create and update your Excel reports…
Get your plumbing right!
Think about it. When you turn the faucet, water runs from its source into your glass nearly instantly. You can set up your Excel reports and analyses to work the same way.
But unfortunately, that’s not how most Excel users set up their Excel work For example, a reader told me recently 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! That strategy causes WAY too much work. It generates WAY too many errors. And it’s as agile as a tree stump.
There’s a much 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.
This is the first of four articles in which I’ll teach you the key steps to improving your Excel productivity significantly. In this article, I’ll explain the general strategy, which I call the Excel-Friendly Database strategy. And the other articles are:
- Introducing Excel-Friendly Databases
- How to Set Up a Pivot Table as an Excel-Friendly Database
- Two Functions You MUST Know to Return Values from Excel Tables
The following figure illustrates my simple strategy. When you follow it…
- You can give your Excel formulas full access to unlimited data, from unlimited sources.
- You can slash your errors.
- You can 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.
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.
In the second article of this series, I’ll introduce Excel-Friendly Databases.
