Hello and hi there, I have a data analysis joke for you.
What is worse than trying to understand a column of figures?
Why, looking at many columns!
Ha, oh well, never mind!
But true enough a column of figures for many people is very hard to get value out of and yet these columns on a spreadsheet have all the secrets of your business’ performance.
And as we know a picture is worth a thousand words which brings us to using charts as way to convey the information hidden in your columns of numbers.
Use Excel, because on your laptop computer there is an excellent range of charts for almost every purpose and we will run through some of the more important ones you're going to use.
Amongst all the possibilities, 4 types of charts will get you the most value and do it quickly – you will be able to literally follow your nose to generate real value from your data.
We have:
- line charts
- pie charts or their equivalents
- column and bar charts
- and finally, scatter and bubble charts
We will start with the line chart - use this to display trends over time. For an example you could track high and low periods of activity to identify seasonal effects or track growth in general.
In this example, we have sales volumes over the last six months.
Line chart
Next comes pie charts and their related friends and you use these to show the share or components of a whole as you can see with the sample below
Pie chart
The big problem with pie charts is that people cannot deal with angles or segments that are not actually right angles and in a chart with similarly sized segments how do you tell them apart?
So, how do we get the best use out of right angles to better see shares by component?
What you can use is the treemap - this is also in Excel but very much underused and will get you to the same result as a pie chart but in an easy to interpret way to convey the information that you want. Compare the two examples below, using the same data, and see which one is it easier to understand:
Pie chart Treemap
Now we have bar (or column) charts – these allow us to compare levels of categories or types plus clearly display the values of those categories.
Bar or column chart
For our fourth type of chart, we come to scatter and bubble charts and these will show you how to display relationships between two or three items of data. You can see at what levels they move together and at what levels they separate.
The scatter chart does this for 2 sets of data on the x (horizontal) and the y (vertical) axes whereas the bubble chart uses the size of the bubbles to bring in the effect of a third value item as you can see in the samples below:
Scatter chart
Bubble chart
Note I did not put titles on all the charts – you can see how understanding is improved when a title is used – you and your audience won’t lose time asking “what is this?” if there is a title.
So, we have charts for trends, shares or composition of the whole, levels for comparison and relationships between items.
These are all easily available at Excel on your computer and generally pretty straight forward - you select your data range and type of chart and away you go.
Let the data tell its story and to help that don’t clutter up the chart with labels and gridlines (keep these to the bare minimum) and keep the data in a natural order (ascending, descending or by time) as much as you can as well. Use a good explanatory title.
See what story you can tell with your own spreadsheets of customer data. Using charts in Excel makes it easy to interpret the data you have.
On my next series of blogs I will take you through the application of charts as I analyse real life data from a cafe to show how you can a transform a difficult to interpret Excel spreadsheet to a dramatic, and valuable, story.