## This Excel chart looks standard but gets everything wrong

##### Dec 02, 2019

The following CNBC chart (link) shows the trend of global car sales by region (or so we think).

This type of chart is quite common in finance/business circles, and has the fingerprint of Excel. After examining it, I nominate it for the Hall of Shame.

***

The chart has three major components vying for our attention: (1) the stacked columns, (2) the yellow line, and (3) the big red dashed arrow.

The easiest to interpret is the yellow line, which is labeled "Total" in the legend. It displays the annual growth rate of car sales around the globe. The data consist of annual percentage changes in car sales, so the slope of the yellow line represents a change of change, which is not particularly useful.

The big red arrow is making the point that the projected decline in global car sales in 2019 will return the world to the slowdown of 2008-9 after almost a decade of growth.

The stacked columns appear to provide a breakdown of the global growth rate by region. Looked at carefully, you'll soon learn that the visual form has hopelessly mangled the data.

What is the growth rate for Chinese car sales in 2006? Is it 2.5%, the top edge of China's part of the column? Between 1.5% and 2.5%, the extant of China's section? The answer is neither. Because of the stacking, China's growth rate is actually the height of the relevant section, that is to say, 1 percent. So the labels on the vertical axis are not directly useful to learning regional growth rates for most sections of the chart.

Can we read the vertical axis as global growth rate? That's not proper either. The different markets are not equal in size so growth rates cannot be aggregated by simple summing - they must be weighted by relative size.

The negative growth rates present another problem. Even if we agree to sum growth rates ignoring relative market sizes, we still can't get directly to the global growth rate. We would have to take the total of the positive rates and subtract the total of the negative rates.

***

At this point, you may begin to question everything you thought you knew about this chart. Remember the yellow line, which we thought measures the global growth rate. Take a look at the 2006 column again.

The global growth rate is depicted as 2 percent. And yet every region experienced growth rates below 2 percent! No matter how you aggregate the regions, it's not possible for the world average to be larger than the value of each region.

For 2006, the regional growth rates are: China, 1%; Rest of the World, 1%; Western Europe, 0.1%; United States, -0.25%. A simple sum of those four rates yields 2%, which is shown on the yellow line.

But this number must be divided by four. If we give the four regions equal weight, each is worth a quarter of the total. So the overall average is the sum of each growth rate weighted by 1/4, which is 0.5%. [In reality, the weights of each region should be scaled to reflect its market size.]

***

tldr; The stacked column chart with a line overlay not only fails to communicate the contents of the car sales data but it also leads to misinterpretation.

I discussed several serious problems of this chart form:

- stacking the columns make it hard to learn the regional data
- the trend by region takes a super effort to decipher
- column stacking promotes reading meaning into the height of the column but the total height is meaningless (because of the negative section) while the net height (positive minus negative) also misleads due to presumptive equal weighting
- the yellow line shows the sum of the regional data, which is four times the global growth rate that it purports to represent

***

PS. [12/4/2019: New post up with a different visualization.]

In my opinion, the data itself have already considered the weight of each region, that is, China's 1% growth in 2006 is China contributes 1% of world's total car sales growth, and the actual growth in Chinese market must be higher. Nevertheless, the chart is complex and not intuitive.

Posted by: Louis | Dec 03, 2019 at 04:55 AM

Louis: Here's how to think about it. If China is a quarter of the world market, and growing at 3% while the rest of the world is stagnant, then the global growth rate isn't 3%+0%, it should be 3%*(1/4) + 0%*(3/4). The 3% growth applies to one quarter of the sales while 0% applies to the other three-quarters.

Posted by: Kaiser | Dec 04, 2019 at 11:02 AM

I don`t know about this particular charts since it isn’t properly indicated, but from experience, these types of charts normally show "Contributions to percentage change", they do not show growth within a country but each countries contribution to global growth. The 1% attributed to china would be the difference in sales within china divided by global sales.

These charts are widely used in economics reporting to present GDP growth by components (Trade, Business investment, etc...).

Posted by: Hugo | Dec 04, 2019 at 12:51 PM

Yeah, I mean that the data has already considered what you said, in your example, the growth rate on the chart would be 0.75%, not 3%. I looked up the true car sales growth of China in year 2006, which is 21.8%, that is far more than 1% showed on the chart.

Posted by: Louis | Dec 04, 2019 at 10:00 PM

Hugo: Can you link to one of those charts about GDP growth? That could help.

I'm still perplexed, not quite understanding what that means. If one uses the total market growth (say, 10 million units) as the base for all growth rates, rather than sector growth, then each sector's value will be a proportion of the 10 million, and the percentages should add up to 100%.

Posted by: Kaiser | Dec 04, 2019 at 10:31 PM

Kaiser: See chart 3 of the october 2019 monetary policy report of the bank of canada.

https://www.bankofcanada.ca/2019/10/mpr-2019-10-30/

Posted by: Hugo | Dec 05, 2019 at 07:19 AM