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.
by
Charley Kyd, MBA Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
|
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.
|