Excel Snow Days

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.

Weather Data

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 snow days 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.

Pivot the Data

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

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

Create a Pivot Chart

I created a simple line chart from the pivot table data

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

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.

Chart Title Formula

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.

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!

___________