Charts are an excellent way to not only visualise data but also to identify trends in data. In the following example, we have some weather statistics for Cape Town, South Africa. Cape Town is in the Western Cape which receives winter rainfall. The statistics are monthly maximum temperatures and the average number of rainy days per month.
In this post:
1. The Data
Unless you are very good with numbers (which I am not) it is not particularly easy to see if there is a trend in either set of numbers. It is even more difficult to see if there is any type of correlation between the two sets of statistics.
|MONTH||MAX TEMP (°C)||DAYS OF RAINFALL|
2. The How-To
- Select the range A1:B13
- Hold down the CTRL key and select the range E1:E13
- Select the Insert tab
- Left-click the Insert Line or Area Chart button
- Select the Line with Markers option
- To insert the default Line with Markers chart
- Left-click the series to select it
- Right-click the selected series
- Select Format Data Series…
- The Format Data Series panel will open
- Select the Secondary Axis option from the Series Options
- The secondary value is added on the right of the plot area
- The series is re-plotted on the chart
3. The Analysis
The chart shows — as one might expect in Cape Town — that toward the middle of the year (winter in the southern hemisphere) the maximum temperatures drop and the average number of rainy days increases.
So you can now easily see that we have an inverse relationship between maximum temperatures and days of rain!
4. Emphasising the relationship
The two charts below are based on the same data sets. To further highlight the nature of the inverse relationship described above, I have modified the minimum and maximum values on both the primary y-axis and the secondary y-axis of the graph on the right-hand side. If you move the slider left-to-right you can see that the difference between the two series appears to be more than it does in the left-hand side graph.
This modification of the value axes can be deceptive and is often used to misrepresent data!