Music is in our everyday life. We listen to music while driving, working, studying, exercising and during other actives. If you want to know what’s the most popular song recently, just look up the Billboard top hits. But what if you are overloaded with too much information and want to figure out what has been left behind? Today we are going to introduce you four methods to dig out fun facts from the Billboard list and related song lyrics.

To undercover those facts, we’ll use billboard lyrics from 1964-2015 as an example to showcase four ways of exploring the data.

Word Cloud of Most Frequent Word in Lyrics

Step 1: Copy all data from “Lyrics” column to Wordle.com. (http://www.wordle.net/create)

Step 2: Create a word cloud and filter all common words, such as: you, I, me. Click Language and choose “Remove all common English words.”

Visualize it!

Top 20 Artists with Most Songs on Billboard

Step 1: Load the billboard_lyrics_1964-2015. xlxs spreadsheet in Excel to create a Pivot Table.

Step 2: Build a PivotTable. Select all data and click the “Insert” tab at the top of the Excel window. Click the “PivotTable” button on the left side of the Insert ribbon.

Step 3: Configuring the Pivot Table. Add a row field and add a value field. Click and drag the “Artist” field into Row Fields section of the Pivot Table. Click and drag the “Artist” field into the Value Fields section of the Pivot Table. Under the Value Fields, choose Count of Artist.

Step 4: Sort the Count of Artist column from Largest to Smallest.

Step 5: Create a Pivot Chart. You can use a Pivot Chart to show dynamic visual reports. But here, we choose use the Pivot Chart in Google Sheets because charts in Google Sheets are more beautifully illustrated.

Visualize it!

Yearly Trend of “Love” Songs

Since we already had the data set and know that “love” is probably the main theme during the decades of hit songs, let’s see the trends of how many songs per year contains the word “love”.

To reach that, we need several steps:

Step 1: We need to build a new column G with the category of “Year,” and put 1965 as the first year in the cell G2. After input the “1965,” we use the dot in the right down corner and pull downside to get the list of the years from 1965 to 2015.

Step 2: Build a new column in column H and name it “how much songs did love appears each year” (or whatever makes sense to you). To count how many songs contain the word “love,” we need a formula here. In the 1965, the lyrics data start in row 2 to row 101, because we are looking for the top 100 songs here. So the formula for H2 should be:

=COUNTIF(E2:E101,”*love*”)

and the result for that cell should be 52.

Step 3: With the same methods in the data for 1966, we input the formula =COUNTIF(E102:E201,”*love*”) in the cell H3. And the result should be 49.

Step 4: Follow the formula and get all the data.

Step 5: Choose column G and H, insert a chart with your own preferred style. Your can start here to check the “love trend”!

Visualize it!

King of Dirty Words in 50 Years

Dirty words sometimes appeared in lyrics. If you are interested in who used dirty words the most frequently in the past 50 years, here’s how we can figure it out and transform the data into readable visual format. We are going to count two typical dirty words, fuck and shit, in the lyrics and see who uses these two words the most.

Step 1: Type in the word “fuck” in the column next to lyrics. To put this word in every cell in F column, use the green square on the right bottom of the cell. Click it and scroll all the way down to the last line of lyrics.

Step 2: To count how many times “fuck” (what’s in F2) appears in the lyric (which is in E2), we can use this function:

=IF(LEN(A2)=0,0,(LEN(A1)-LEN(SUBSTITUTE(A1,A2,””)))/LEN(A2))

Click on the green square in G2 and scroll down to quickly apply this function to every cell in column G.

Count the word “shit” in the same way.

Step 3: Sum up all numbers of dirty words in column J. Use the function:

=SUM(G2,I2)

Step 4: Create a pivot table with name of the artist and the total number of dirty words we just sum up.

Step 5: Copy the data we get from pivot table and paste them in a new sheet. Then sort the number from big to small.

Step 6: Insert a nice bar chart, play with the format and you’ll get a clear visual form of who is “the king of dirty words.”

Visualize it!