Excel Snow Days

iconsnow It snowed here all day on Wednesday, and caused traffic problems and backaches. Usually the city clears the streets very efficiently, but things were a mess on Wednesday. Maybe some of the snow removal contracts expired in mid-March, so the full fleet wasn’t available.

Fortunately, I didn’t have any outside meetings scheduled, and spent the day working on client projects. I took a bit of time to download some weather data from the Environment Canada website, and summarized it with an Excel pivot table.

How’s your snowfall this year? Worse than ours, shown below?

Three Years of Data

The worst spring snowfall that I remember was in April 1975, so I downloaded the data for that year, plus 2010 and 2011. Unfortunately, the data for March 23rd wasn’t online yet, but the Toronto Star said it was 20 cm, so that’s the number I used.

The data is stored in an Excel table named Weather_Data.

weatherdata01

Next, I created a pivot table, using the named table as the source data.

weatherdata01b

The dates in the pivot table are grouped by Years and Months, and the Value field label is changed to “Snow (cm)”

weatherdata02

Create a Pivot Chart

I created a simple line chart from the pivot table data

weatherdata03

In Excel 2010, you can show or hide the individual field buttons on the pivot chart.

weatherdata06

Add a Chart Title

Finally, I typed a heading for the worksheet in cell A1. Then, I selected the chart, and added a chart title, above the chart.

weatherdata04

With the chart title selected, I typed an equal sign in the formula bar, then clicked on cell A1, and pressed Enter.

The chart now shows the same title as the worksheet.

weatherdata05

The Completed Chart

Here’s the final chart, showing the available data for 2011, compared to 2010 and 1975. It’s amazing that we didn’t get any snow last March or April, so maybe that’s why the city let the snow removal crews quit early this year.

Let’s hope that April 2011 is better than April 1975!

weatherdata07

___________

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *