Connexion

A paper used to justify austerity economics appears to contain an Excel error.

An economics paper claiming that high levels of national debt led to low or negative economic growth could turn out to be deeply flawed as a result of, among other things, an incorrect formula in an Excel spreadsheet.

[The academic paper that this article is based on is available at:
Does high public debt consistently stifle economic growth? A critique of Reinhart and Rogoff]

I got a email from a client asking for help because Excel was "destroying," to use his terminology, his 2003 workbook after conversion to the 2007 format. And, after analyzing the kind of change Excel made, I had to agree.

The basic problem is that names that are legitimate names in Excel 2003 may become unacceptable in 2007 (or later). But, a more devastating problem is with a formula using a name with a dot in it. Even though it is completely legitimate, Excel changes the dot to a colon. This causes the formula =SW1.SW2 to become =SW1:SW2. Don't ask me why. It just does. The result is the formula is all wrong and destroys the integrity of the workbook.

When working on a spreadsheet that someone else has built, there are a number of things that can make life particularly difficult. One of these, which is rarely necessary, is the use of merged cells.

The purpose of today's post, however, is not to teach you how to use Merge Cells, but to warn of the dangers of using them unnecessarily, and in the wrong place.

I was working on a function that uses regular expressions to determine whether a potential name for a workbook, worksheet or range contains illegal characters. I started by writing a little routine to determine which characters are illegal for sheet names. Of course, I could have just used one that I knew was prohibited and got the message below. But then I might never have thought about the use of single quotes in worksheet names.

Here are the top 10 features in Excel according to you:

• Excel formulas.
• VBA, macros & automation.
• Pivot tables.
• Lookup formulas.
• Excel charts.
• Sorting & filtering data.
• Conditional formatting.
• Drop down validation & form controls.
• Excel tables & structural references.
• PowerPivot, data explorer & data analysis features.

Excel 2010's conditional formatting feature is a super quick and easy way to find duplicate data in your spreadsheets.

Spreadsheet templates hold such promise. A good template can save you hours of time you would otherwise spend creating your own spreadsheet, and gives you the benefit of someone else's expertise. The sad fact though is that spreadsheet templates are fundamentally 'broken', and the concept of a good template is completely illusory.

It's frustrating when Excel acts as if the active area of a worksheet is significantly larger than the actual area where you have data. Suddenly your scroll bars move you into uncharted areas, such as column TX or row 5000.

In programming parlance, this is known as the "used range" of a worksheet. Fortunately, there's a quick and simple way to reset the used range when necessary.