1. Building the first charts
In this demo, we will work with a similar dataset to the one you will see in the exercises.
It contains Spotify’s most popular songs per year and genre and several interesting values such as energy or danceability scores.
Next to the raw data, we also have several aggregations, so summaries of the data, in respective tabs.
Let’s inspect the “Per Artist” worksheet. It contains the popularity score and the number of songs per artist.
Let’s build a bar chart to visualize the popularity. To do this, we will click anywhere within the data and go to the Insert tab. We have a whole range of charts to choose from, but let’s see what Excel recommends:
Hmm, there are a few options within the Recommended Charts, but we can also see what’s in the All Charts tab.
This is where we can freely choose between any chart type. Let’s inspect the bar chart options.
The first one, the clustered one, already looks good. Notice that there are other chart variants; we will explore them next.
Clicking on OK, the chart appears in our worksheet. Let’s expand it a bit by dragging the arrow in the corner.
Notice the highlighted cells in our data which appear when we click on the chart. They show us what is being visualized in a chart.
The blue ones are the Series with values, so both Average popularity and the number of songs. The purple ones are Categories, so the labels such as Rihanna or Eminem. Finally, the orange ones are the names of the measures. When we hover over the table, we can easily adapt what we want to focus on. By dragging the blue area to the left, we will narrow down the selection to the first measure:
We can then change that by moving the area of the blue selection to the # of Songs, and the visualization will follow our selection.
Similarly, we can just select the top 10 artists by shortening the blue or purple area:
Notice that in the table, the Artists are sorted based on the number of songs, from highest to lowest. However, the chart displays another order. To tackle this, let’s double-click on the labels to access the Format Axis menu and we will tick “Categories in reverse order” option.
This looks much better!
Now, let’s explore the Chart Design tab.
It gives us several options to improve the chart and to quickly change its type:
Notice also the Add Chart Element option, which allows us to add elements such as data labels, legends or axis names.
The same can be achieved using the small plus icon next to the chart.
Let’s add Data Labels.
Let’s proceed to the Per Genre tab. We already have here another bar chart showing the amount of hits per genre. Let’s convert it to a 100% stacked bar chart using the Chart design and Change Chart type option to understand which genres have the most top hits.
What would happen if we now clicked on Switch Row and Column?
Notice that it doesn’t convert the bar to column chart, instead it swaps the categories. We are now visualizing the genre contribution to top, medium and no hits. Interesting!
Lastly, let’s proceed to “Per Year” worksheet. This time, we will select the data first and click on the small “Quick Analysis” prompt at the bottom.
This allows us to quickly add a chart! Line chart might be a good idea! Let’s go to More charts:
Let’s go to line charts and add this one:
Hmm. Let’s narrow down the data to start from 2002 as we have some outliers prior to that:
Visualizing data in Excel really is that easy! But enough of experimenting for now. Onto you, let’s see how you excel in the practical exercises!
2. Let's practice!