Microsoft Excel Charts banner image.

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
  2. The How-To
  3. The Analysis
  4. Emphasising the Relationship

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.

MONTHMAX TEMP (°C)DAYS OF RAINFALL
January293
February292
March272
April256
May229
June2010
July2010
August2010
September218
October245
November254
December274
Extract from weather statistics

2. The How-To

Annotated screenshot of data selection in Excel for Insertion of a Chart.Annotated screenshot of a Line Chart being selected and inserted in an Excel worksheet.
  1. Select the range A1:B13
  2. Hold down the CTRL key and select the range E1:E13
  3. Select the Insert tab
  4. Left-click the Insert Line or Area Chart button
  5. Select the Line with Markers option
  6. To insert the default Line with Markers chart
Select the line representing the secondary series.Switching the series to the Secondary Axis.
  1. Left-click the series to select it
  2. Right-click the selected series
  3. Select Format Data Series…
  4. The Format Data Series panel will open
  5. Select the Secondary Axis option from the Series Options
  6. The secondary value is added on the right of the plot area
  7. 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.

Excel chart with 2 data series & a secondary value axis

An inverse relationship means that as one set of values increases, the other set of values decreases.

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!

By MisterFoxOnline

CAT Educator

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.