Published Now and Again for Business Users of Microsoft Excel.
Lookup Methods + Excel Errors
+ Dynamic Reporting
Tuesday, Nov 30, 2010
If you like this newsletter, please forward it to
other Excel users.
Several weeks ago I got a flurry of questions about Excel lookup techniques. I
also got surprised questions during an Excel seminar this month, when I
mentioned that I never use VLOOKUP, and discourage its use.
So that topic is ripe for a series of blog entries.
The Problems With VLOOKUP
As I write at http://exceluser.com/blog/,
VLOOKUP, HLOOKUP, and LOOKUP all have limitations that INDEX-MATCH doesn't.
However, ALL Excel lookup functions, including MATCH, share one significant
problem: The default lookup method can give you incorrect results. The blog
entries I've posted so far explain this problem in detail.
That said, VLOOKUP has fundamental limitations that make it not worth using, in
my opinion. I'll cover these in my next blog entry, then show why INDEX-MATCH is
a lot more powerful and useful.
Trapping Errors in Formulas
Speaking of errors, New Excel has a function that makes it much easier to trap errors in formulas. In
Error Handling in Excel Spreadsheets, I explain why IFERROR was created and
how it's used.
I must admit that I have a special reason for liking this function: I first
suggested it to Microsoft several years ago. When I asked a manager on the Excel
team about it, he said they created the function because they agreed with my
Watch for Dynamic Spreadsheet Reporting
The seminar I referred to in the first paragraph was about Dynamic Spreadsheet
Reporting. Here's the general idea:
Most Excel reports contain static data. That is, they
have numbers in cells. In my experience, this is a horrible way to structure
reports, because Excel users need to manually massage the data each
period. And that leads to Spreadsheet Hell.
Instead, most reports should contain only a few settings, and formulas that
return data from a simple database. With this design, we update the database
each period and use the data in any number of reports.
To update a report, we just change the date in one cell of the report,
recalculate, and print.
If you're using any of my dashboard products with an Excel database, you're
using one version of this concept. But there's another version, which can be
used with transaction-type data and extensive filtering. And both versions can be used with standard
reports, as well as dashboards.
I'm working on a training series that will explain Dynamic Spreadsheet Reporting
techniques in detail. To do so, I'll cover spreadsheet functions and techniques that I used
extensively during my consulting days.
A Range-Name Wrangle
Earlier this week I discovered the FAST Modeling Standard for spreadsheets here:
When I glanced through their "best-practice" standard, I discovered an
"The FAST Modeling Standard advocates very limited use of Names, that is
adherents to the Standard are generally against Names. Adherents of the FAST
Modeling Standard believe that Names positively harm flexibility and
transparency; benefits they may provide are often achievable through simpler
techniques and design. In fact, Names are better (or only) suited to simple
spreadsheets with limited complexity, where reading a simple natural language
formula such as = Price * Quantity is a real possibility."
Because I had discovered FAST through LinkedIn's group, "Financial Modelling in
Excel", I opened a new discussion, "The FAST Standard opposes Excel range names
in most instances. Why? Does anyone agree?"
If you're on LinkedIn, you might want to check out Monday's discussion. It was
rather intense for a while.
Watch for Charley's Swipe Files
In my e-book, I recommended that you find well-designed charts and tables in
business magazines and recreate them in Excel. The last chapter of the book
includes several examples.
I've followed my own advice since about 1990, and I've got binders filled with
clippings of those professionally designed figures. I flip through the binders
frequently when I need an idea for a new design for a chart or table. But 99% of
that material is going to waste.
Also, I've got dozens -- maybe hundreds -- of useful charts and tables that I've
created in the past 20 years. But they're just sitting there, gathering digital
So here's what I'm going to do...
In several weeks I'm going to offer a very low-cost subscription to Charley's
Swipe Files. Twice a month I'll send a link to a zip file that includes
versions of these charts and other displays for both Classic and New Excel. The
zip also will include short documentation for the files.
If you print the first page of the, documentation and add it to a binder, you'll
be able to flip through it, find useful displays, and use them in your work.
Additionally, of course, if you copy the Excel files, and then experiment with
them, you'll learn all sorts of techniques that I know you'll find