Published Now and Again for Business Users of Microsoft Excel.
Summarize Excel data. Excel 2007.
Tuesday, April 2, 2007
If you like this newsletter, please forward it to
other Excel users.
Excel provides significant power to summarize data with
spreadsheet formulas. But the most powerful way to do this also seems to be the
The oldest way to summarize spreadsheet data is with database functions like
DSUM and DCOUNT. The newest way is with Excel's SUMIF and COUNTIF functions. And
most obscure is with Excel's array functions.
If you want to use multiple criteria -- which many Excel users do -- the most
powerful method is also the easiest to use: array functions.
In How to Summarize Excel Data For Reporting and Analysis,
you'll find a long article that discusses SUMIF and COUNTIF and that explains
how to use array formulas to summarize multiple criteria. The article offers
many examples.What We'll Do
About Excel 2007
I'm not one of Excel 2007's biggest fans. Although
the program offers many outstanding new features, it has a completely new user
Microsoft designed the interface to make it easier for new and occasional
users to discover Excel's features. They didn't design the interface to make
frequent users more efficient.
In business, Excel users are much like factory workers. Our job is to
quickly produce reports, analyses, and other forms of business insight.
Unfortunately, Excel 2007 requires more mouse clicks and more mouse travel than
does earlier versions. It's inherently less efficient. Therefore, even after
you've learned the new interface well, you'll probably take longer to perform standard tasks in the new Excel.
Even so, there's no going back. Eventually, you'll need to adapt to Excel
2007, or to one of its successors. At ExcelUser, we also need to adapt.
Therefore, all future articles that describe hands-on use of Excel will be
offered in two versions. One version will be for old Excel, the other for new
Excel. Each such article will begin with a link to the other version. And
slowly, we'll offer new-Excel versions of past articles written for old Excel.
Because Excel 2007 introduces new terminology, many of the new-Excel articles
will reference The Excel 2007 User Interface.
This article provides one location for defining those new terms.
Now for something completely different.
I recently played around with the drawing tools and shapes in an old version
of Excel. Before I knew it, I found myself creating cartoons in Excel.
You'll see the new cartoons sprinkled around the pages that were recently
posted. And you can find all the cartoons I've created so far at
Honestly, I don't know whether this is a good idea or not. I had fun creating
them. But I'm not sure they belong at ExcelUser.
Please let me know what you think. Should I try to continue with them? Or
forget about them? And if you have any suggestions for new cartoons, please
send me email. Be sure to use "Excel
cartoons" for your email's subject.
Excel's Shortcut Keys For
The set of shortcut key combinations is one area of the Excel 2007 user interface that
hardly changed at all. If you use a shortcut key combination to launch a dialog
with old Excel, you'll probably be able to use the same key combination with new
If you don't use shortcut keys to launch dialogs, this might be the time to
learn. That way, as you switch between the two versions, your fingers will have
a consistent way to launch certain dialogs for either version.
The article, Excel Shortcut Keys Offer Quick Access to Dialogs,
offers all the shortcut key combinations I know of that launch dialogs.
Another Reporting Lesson from
I get more reporting ideas from Business Week than from any other source.
At Show Key Stats Automatically In Periodic Excel Reports I describe
another idea that I stole from that magazine. I explain how to create and
maintain figures that report interesting facts that don't have a place in your
From an Excel perspective, the article offers yet another reason that INDEX-MATCH
and the Camera tool belong in every Excel user's bag of tricks.
My Article in Business
In February, Business Performance Management Magazine published my article. You can read
Don't Discard Those Spreadsheets: The Power of Excel-Friendly OLAP
at the magazine's web site.
Here's how the magazine promoted the article in a recent email blast:
What If Spreadsheets Are the Answer, After All?
Developing corporate budgets in spreadsheets -- that's heresy these days.
Many sellers of business performance management (BPM) software and services
proclaim that spreadsheets are a relic of the past, at least in terms of their
ability to support corporate performance management processes.
consultants contend that any company which still uses spreadsheets for budgeting
or reporting on financials is behind the times. It's plain and simple, and a lot
of corporate executives who've implemented BPM software suites agree with the
sentiment. Horror stories of erroneous financials, broken links, lost data, and
untrackable changes are enough to make the most seasoned finance pro cringe.
But spreadsheets are versatile, and the finance staff knows them inside and
out. What if they could be closely connected to a central data source?
Kyd, president of
ExcelUser.com, explains in a
feature article in the February issue of
BPM Magazine how a few OLAP
database products enable users to easily pull data from and write it to an OLAP
database from their financial planning spreadsheets.
This isn't a panacea. A company that wasn't terribly organized with its
OLAP data stores could still maintain multiple versions of the truth by
storing the same information in more than one OLAP database. And just
because a spreadsheet connects to a database doesn't mean that its change
tracking is acceptable to a company facing Sarbanes-Oxley reporting
Enough for now.