Better Excel Charts — Tips continued.
The second episode in our series on Better Excel charts is here. In our first episode, we set the context for analysis and reviewed the relationship between US rig count drop and crude oil production.
Meet Chart C.
Can you improve on Chart C?
Yes, you can. Slightly but certainly an improvement. Take a look at our new and new improved Chart E side by side with our original Chart C. Which one do you prefer? Can you tell the tweak we have used to transform C into E?
The relationship displayed in E is significantly steeper than the relationship in C. This was made possible by adjusting the starting and ending points for both scales. Excel by default picks proportionate values for the scale. You can tweak that to share a different perspective.
Here is another example. Meet Chart F and Chart G.
What are some of the immediate fixes that come to your mind when you view chart F. There is room for improvement on the scale front. It would also help if we could place the date labels some place else.
Minor differences created by tweaking the axis on the left and right-hand side lead to a slightly steeper curve and more readable data in Chart G compared to Chart F.
I like steeper curves because I want to use all the real estate available to me on a chart to convey the information or message I need to convey.
But in addition to the steepness, there is one other adjustment made to Chart G compared to Chart F. Can you spot it?
We have moved the date labels all the way down so that they are out of the way of the plot area.
To change the axis, click and select the axis you want to tweak, right click to get to the Excel pop-up menu and then chose Format Axis. Change the minimum and maximum bounds to get the desired effect.
To access the label menu, double click on the horizontal axis to open the format axis menu on the right. Click on labels and scroll all the way down to label position. Pick the most suitable position that gets the labels out of the way of your plot.
Lesson Number Two — Analyzing a relationship? Focus on rate of change or relative change.
When faced with two variables that you are trying to link through a relationship, what should you focus on? Absolute values or rate of change?
Given a choice, I prefer rate of change. I use it to calculate volatility, correlations, moving averages and trends for returns. I use absolute values to show a trend and how the variables have changed. There are certain presentations where my analysis needs and is based on absolute values and there are others where I mix and match. But the most fun I have is with rate of change.
In Charts A and B I used relative rate of change. In C and D I use absolute values because that is the unit of my analysis. In Chart F and Chart G I use rate of change (values not percentages).
One of my favorite technique is to calculate rolling or trailing volatility or correlations and plot them against the relative rate of change. Chart H provides a sample of this technique.
But you have to be careful using this variation because it often leads to information over load and is a great example of sacrificing clarity for information. Most audiences in my training session hate this chart because unlike the versions A through G, H is a lost cause.
There is no clear message or relationship on display. Sure if you follow crude oil and understand the link between crude oil prices and the USD exchange rate, you may see the link. But Chart H commits a cardinal sin.
It makes you think.
You can’t understand the message or the relationship without really applying your mind. Steve Krug wrote a brilliant book on usability and UX design titled “Don’t Make Me think”. That line needs to be your calling card as an analyst using Excel charts to communicate information and insights.
Remember simpler, clearer, cleaner is better than fancier.
Now compare Chart H with Chart I. We are back in the zone of clarity. Chart I is also a good example of mixing relative change with the underlying variable. It applies all the principles we discussed in Lesson One including the usage of two axis and tweaking the range the axis plot for the graph on the left and right-hand side. Chart J presents a similar data set on Iraq crude production without the tweaks. Once again which one do you prefer?
My final example is Chart K.
Notice the difference setting for the title. But in my zeal to utilize real estate I cropped the bottom when I tweaked the axis and forgot to do a quality check before I posted and used this chart.
There is a common theme across Chart H, I , J and K. Chart H and Chart K use the Excel gradient functionality to show the parts of the plot hidden behind the main series. Chart I uses a different technique to achieve the same effect. This is useful when one series is more relevant but the other series adds context.
For I, J and K while we are interested in the relative change we also want to share the steepness of the increase and decline across the timeline under review.
To access the gradient setting, you would again need to get to the Format Data Series menu. Pick gradient fill under Fill and then chose 90% — 99% transparency to get your desired effects.
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.
