First off – although perhaps unpopular to say – I still think Excel is a great tool for quick analysis and chart making. This became especially true after Office 2007 added user-friendly style and formatting controls.

Take the following example looking at growth of Gross Domestic Product in China since 1990.

The graph is both easy to create and tells the story quite well. Data from the IMF show that GDP in China grew substantially between 1990 and 2010. The rate of growth was even quicker between 2000 and 2010.

With incomplete data sets, however, I always found it difficult not to show something misleading. Take this data from UNESCO on higher education enrollments in Comoros.

1999: 649
2000: 714
2001: Missing
2002: Missing
2003: 1,707
2004: 1,779
2005: Missing
2006: Missing
2007: 2,598
2008: Missing
2009: 3,457

Some might not recommend graphing anything with 5 of 11 years missing but I think that, even with the limited data, the underlying trend is strong enough to warrant a visual.

So if you leave the cells blank excel would spit out something like this line or column chart.

Not very pleasing. You can tell the trend is upward but most non-data people would be so distracted by the gaps that they wouldn’t even care. Another method I’ve seen used is taking out the missing years.

I find this one even more offensive. The speed (slope) of the trend is exaggerated because time has been compressed. Also, since the number of years between actual data points is inconsistent it is additionally misleading.

This issue always bothered me which is why I was so happy last week to discover a simple solution. Instead of leaving the cells with no data blank, enter =NA() as the formula. This changes the cell to read #N/A and causes Excel to interpolate the line through any missing values. The end result isn’t perfect because we don’t actually know what happened in 2001, 2002, 2005, 2006, or 2008 but I think it’s a practical real-world solution where information is often incomplete.