Published Now and Again for Business Users of Microsoft Excel.
Get Office 12 Beta
+ Fight Excel Errors
Wednesday, May 24, 2006
If you like this newsletter, please forward it to
other Excel users.
The public beta of Office 2007 now is available. I've just started to
download the program. You can do the same by going to this link:
3 zillion people are trying to download the program this week, which is causing a major
traffic jam on the Microsoft servers. The first few times I tried to download I
was told to come back later. But much to my surprise, I've just managed to
According to the download dialog box, the Office 2007 install file is
440 megabytes and should take nearly three hours to download using DSL.
If you wish, you also can pay $4.95 to receive the beta by mail. The link
above offers you this option as well.
Warning, if you choose to
install the beta, make sure that you DON'T erase your current version of Office.
Fighting Spreadsheet Hell
Spreadsheet Hell comes in many forms, and has many causes. But certainly,
this is one common aspect of the problem: You pull a large file into Excel and
then sort, filter, consolidate, and transform it to generate the view of the
data you want to report or analyze.
Worse, after the first report is finished you must generate other similar
reports. Typically, these are for other regions, or product lines, or
departments, or managers, or whatever.
PivotTables sometimes can simplify this process. But unfortunately, the data
frequently needs more help than PivotTables can offer.
Another approach that
often works is to maintain your data in a spreadsheet database then use Excel
formulas to return the data to your reports and analyses. This approach lets your formulas do
In How to Fight Spreadsheet Hell With Three Excel Functions,
I explain how to use the MATCH, INDEX, and OFFSET functions to return data from
such a database. Intermediate and advanced Excel users rely on the INDEX-MATCH
combination extensively for reporting. If you haven't used it before, you may be
Using INDEX with OFFSET is less common. But OFFSET
provides power that INDEX doesn't. By keeping all three of thee functions in your Excel
user's toolkit, you have a better chance of fighting Spreadsheet Hell.
Avoid Excel Errors
In Seven Ways to Avoid Errors In Excel Reports & Analyses I
take a small step on a very long journey. There are likely dozens -- perhaps
hundreds -- of excellent ways to avoid spreadsheet errors. But every journey
begins with the first step.
In this article, I tried to offer practical ideas about a wide variety of
techniques. The way you organize your workbook can reduce errors. Saving
generations of your workbook can reduce errors during the development process.
You even can use a line chart to quickly find unusual results in a data series.
And so on.
As I write in the article, I would be interested to know your
thoughts about ways to reduce errors.
This year, I've given more seminars about Excel than I have in the past ten
years combined. Additionally, I plan to offer seminars in cooperation with other
people who have special knowledge about some aspect of Excel.
Enough for now.