Excel provides significant power to summarize data with spreadsheet formulas. But the most powerful way to do this also seems to be the most obscure.

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 the 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.

I'm not one of Excel 2007's biggest fans. Although the program offers many outstanding new features, it has a completely new user interface.

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 Excel-Generated Cartoons.

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.
 

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 Excel.

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. 
 

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 standard reports.

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.
 

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. Isn't it?

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.

Vendors and 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?

Charley 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 requirements.

Enough for now.

More later,

Charley