The Excel mistake that changed the world

In 2010, two leading Harvard economists, Carmen Reinhart and Kenneth Rogoff, published a paper. They had found that debt levels exceeding 90% of GDP led to a decline in economic growth. Governments took notice, and policies were implemented to reduce debt, with sometimes draconian austerity measures.

George Osborne quoted the paper as he introduced his measures to repair Britain’s economy. We are still living with the consequences.

The problem is that their results were wrong.

The Harvard professors had used Excel, and had accidentally only included 15 of the 20 countries under analysis in their key calculation. It made a big difference to their results. Their finding of a decline in GDP of 0.1% was really a increase of 2.2%.

As their data had grown, they had failed to change the formula.

Excel is a great tool. It’s intuitive, easy to work with, and presents results well with graphs and pivot tables.

It’s the best tool for creating and checking complex algorithms, doing financial projections, and quick and dirty calculations.

It falls down when handling big data, data that grows over time, or there are multiple users making updates.

It’s easy to end up with multiple versions of the data, and then have to decide which is correct.

Excel makes entering data easy. But it’s also easy to make mistakes. Validations to guard against bad data have to be programmed in. Garbage in – garbage out.

Ray Panko, a professor at the University of Hawaii and the elder statesman of small coterie of research academics who study Excel errors, claims that 84 percent of spreadsheets contain some kind of materially significant error. Other economists agree.

Databases are not visual, not in the way that Excel is. The terminology and design can seem arcane. You can’t just open a database and start entering data. They’re not intuitive. They are also not as complex as they seem.

So, which should you use? – The answer: both.

It’s a matter of deciding which is the right tool for the job. It’s not necessary to decide up front. Using Excel to become familiar with the data is a great way to start a database project. When you get going, importing the initial data into the database is easy. so nothing’s lost.

Having an existing spreadsheet that should be a database is an excellent way to learn. The best choice for a project is one where you’re struggling to tie up the relationships in the data. What’s difficult in Excel is easy in a database.

Great, now the data is in a database, but now you need to present the result of your analysis. Simple. Export the summaries into Excel and do it there. It’s easy to do, and it’s the best tool for the job.

What about the cost? Unlike Excel, many of the best databases are open source and free.

So, how do you get started. Next time.