Better Excel Charts. Part I — Laying the ground work.
How to add impact and clarity to your presentation.
I have spent the last two decades presenting data. Occasionally when I team up with young analysts I see common mistakes that impact how a presentation, a chart or image is perceived. This multi part series is based on an orientation on Excel charts I run for new analysts in my team. Here is our road map for the series.
- Part I — Context and tips.
- Part II — Tips continued.
- Part III — Takeaways.
Understanding the context.
The most important step before we begin our analysis is understanding the context. So before we begin, a quick detour on context around analysis that is often forgotten in our rush to build visual graphs and charts.
My beef with analysis.
I hate inaccurate and superficial analysis. Other than a few bright exceptions in the media who treat data and opinions with respect, I rarely take what I read today at face value. You have no idea how refreshing it is to see an opinion based on facts and well thought out analysis. Especially if it challenges accepted wisdom or the current mindset.
Imagine you are putting together a chart for a presentation to Jeff Bezos. Imagine he is the smartest man on the planet and knows everything about the subject. Now craft your graphs. Present insights and analysis rather than plotting known facts and figures.
To do that well you must have an opinion, a story, an argument that you favor.
Forming opinions.
My first step when I start exploring any topic is to read. Before I form my own opinions I want to hear all sides of the story and gather as much data as possible. I don’t want to pen and publish a piece dissing the likelihood of finding life on Mars just as NASA is breaking the news of having found life on Mars. I also want to ensure that I understand the issues involved and take my own advice — provide a balance coverage of all point of views to my readers.
Once I form my opinion I go looking for data and analysis both in favor and against it. I keep an open mind and I can change my position as long as I see compelling content that can convince me of being right or wrong. The joys of reading opinions from across the world is that you find an alternate thesis that you can make and call your own.
Here is a recent thesis I encountered last week.
On April 17th both OPEC and non OPEC members meet in Doha and I was curious to see if the meeting as an event was driving oil prices up. This would imply that the market was pricing in the possibility of a deal, which seemed at odds with the opinion of a number of oil analysts, including our own in house team. However financial media coverage was making a big push for the Doha meeting impacting oil price. So who was right; financial media coverage or oil analysts?
In the Doha’s meeting case, the compelling arguments and data were both missing. There was apparent causality given the trajectory of the event and prices, but no basis or model for that causality. A lot of talk and very little action and given the players involved the required action doesn’t seem probable. There had to be some other driver pushing prices up. Because Doha was the most visible event on the horizon it was getting all the credit. Attribution is easy, credible justification is not.
The data set for my Excel charts.
While working on Doha, I found another relationship worth exploring that supported my case (remember the story). It dealt with falling US oil rig count and US crude oil production. While US rig count has fallen by 76% over a fifteen month period, US crude oil production had only declined by 15% over the same time period. The rig count figure is reported every Friday and media coverage presents each drop with depressing write-ups about what the drop means for the future of oil production in the US and a sure sign that crude oil prices are finally going to rise again from the dead.
My core data set included the rig count updates and rig productivity data issued by EIA, US crude oil production figures also released by EIA. Iraq and Russian Federation crude oil production sourced from a range of databases, the EUR-USD foreign exchange rate collected via Quandl and a number of additional calculations and filters.
Lesson One — One axis versus two.
When I looked through the rig count data set two views or presentations were possible. Let’s take a quick look at both views:
Chart A — Using two separate axis for the two metrics
Chart A used a primary axis (on the left) to display the results for change in rig count and a secondary axis to display the change in crude oil production.
Chart B — Using the same axis for the two metrics
Chart B used the same primary axis to display the change for both metrics.
Before you proceed, take a step back and think. Which one of these presentations do you prefer? Which one of these charts pushes you towards a clear and specific interpretation?
I lean towards Chart B because I like the thought that this hoopla about rig count has very little impact on actual crude oil production in the US. That is my story.
It also supports my bias that most financial journalists before expressing opinions about the end of the world for the oil patch based on rig count changes were not doing their homework.
Conversely if I wanted to at least convey the image of a close relationship between change in rig count and the change in crude oil production, as my friends in financial media are wont to do, I would have leaned towards Chart A.
There are instances when the usage of a secondary axis adds clarity to your presentation as well as your thought. This — the relationship between rig count and oil production — is clearly not one of these instances. You can use the secondary axis to confuse the issue or forward your point of view.
Chart C presents an instance where a secondary axis is clearly required. It presents a different data set related to the same topic using two separate axis. A primary axis for the actual rig count value (left) and a secondary axis for the average volume of crude oil pumped by a rig for the same period. The relationship being displayed is the rise in rig productivity as rig counts falls across 2015.
Without a secondary axis, Chart C would transform into Chart D. The removal of the axis kills the graph and the relationship that was being portrayed so well in Chart C. This happens because the two scales don’t relate to each other in a visual setting. One ranges in the thousands, the other in tens and twentys.
To access the Format data series option, click and select your data series on the graph, then right click to open the Excel pop up menu. In Excel 2013 and above you will see the Format Data Series option. Click on the histogram bars icon and pick your choice of primary or secondary axis for your series.
Originally published at Better Excel Charts as part of a series on data analysis for data analysts.
Corsair’s Publishing seeks to provide engaging content that is both thought provoking and entertaining. Other articles on related topics can be found within our other Medium publications.
