Connexion is a collection of the most useful and interesting spreadsheet-related articles from the web.
We review more than 200 websites and blogs to collect the best articles on tools, tips, and techniques that help you to improve your spreadsheets. Each article here is just a snippet — click on the title to open the full article.
Microsoft Excel: The ruiner of global economies?
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]
Excel corrupts certain workbooks in migrating from 2003 to 2007
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.
Do you really need to merge those cells?
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.
Single quotes in worksheet names
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.
Learn top 10 Excel features
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.
Use conditional formatting to find duplicate values
Excel 2010's conditional formatting feature is a super quick and easy way to find duplicate data in your spreadsheets.
A sad tale about spreadsheet templates
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.
Resetting the Last Cell in an Excel worksheet
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
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.