Top 100 Canadian Singles in Excel
If you’re looking for love, move along — the “Canadian Singles” in the article title refers to hit songs, not eligible bachelors. Last week, a new book was published with a list of top 100 Canadian singles, based on a poll of music professionals and fans.
In his J-Walk blog, John Walkenbach posted a link to the Canada’s Top 100 Singles list, and there was a lively discussion in the comments section.
Top 100 Canadian Singles List
No discussion is complete without a spreadsheet, so I copied the list into Excel, and cleaned it up. To make it more interesting, I found the release date for each hit song, and split them into decades, using the FLOOR function.
From that data, I created a pivot table, showing the count of songs from each decade, listed by rank. Was most of the best music released in the 1970s, or were most of the voters from that era?
The top 100 songs are grouped by 10s, to summarize the data.
I added conditional formatting to highlight the decades with the largest number of songs.
Repeat Pivot Table Item Labels
A new feature in Excel 2010 pivot tables is the ability to repeat the field item labels. In another copy of the pivot table, I put the decade in the row label area, and changed the pivot table report layout to Outline Form.
Then, I right-clicked on the Decade field, and clicked Field Settings. On the Layout & Print tab, I added a check mark to Repeat Item Labels, and clicked OK.
After changing that setting, the decade is repeated in each row, instead of showing just once, at the top of the section.
Download the Top 100 Canadian Single File
To see the list, and create your own pivot table, you can download the sample file. There’s a Top 100 Canadian Singles list in Excel 2007/2010 format, and Top 100 Canadian Singles list in Excel 2003 format.