Bullet, waterfall charts, and scatter plots
1. Bullet, waterfall charts, and scatter plots
In this demo, we will use the new chart types in practice, while analyzing Spotify top songs across years. First up, the bullet chart! As you remember, this chart is great to compare values against the benchmark, or targets. In the table, we have genres’ average energy levels so let’s see how they stack up against the overall average energy of the songs throughout the years. How do we make a bullet chart? Excel doesn’t have it among its standard options but it’s very easy to set up! We start by clicking anywhere among the data cells and inserting a clustered column chart. We will give our chart a proper name. Let’s double click on the default title and type in “Bullet chart”, we will also exclude the total, just like we’ve learned in the previous chapter. Next, we will click on the chart and using the Change Chart Type button, we will convert it to a combo chart. We will ensure that both series, the Energy levels and the Average Energy, are plotted as clustered columns. However, we will tick that the Energy levels columns must appear on the Secondary Axis. How do we mold this to a bullet chart? Let’s right-click on the bar and select Format Data Series. Here, we can manipulate the Series overlap and the Gap Width. We will click on the benchmark, the orange columns, and set the gap to wider. But oh oh... Looking at the axis ranges of both series, our chart turns out to be very confusing! Let’s double-click on the left Y axis to access the axis formatting menu. Using the Axis Options, we will have to force the axes ranges to the same values, let’s go for 0 to 100. We will do the same with the right Y-axis. Much better. Lastly, let’s change the coloring to highlight key data points. Clicking on the orange columns, we will access the Fill option. Let’s set it up to gray. Now, onto the blue columns. We will change their fill to red, but then we will individually recolor the outstanding ones to green to draw attention to them. An easy trick? We do it once, and press CTRL+Y to repeat the action! Our bullet chart looks great! Let’s proceed to the Waterfall chart. In the table we have a comparison of the amount of new top songs per year. In the Delta column, we compare the difference between the current and the previous year. We keep the starting value of 107 and, in the last cell, we sum the entire Delta column. This is the data setup we are looking for, when dealing with a waterfall chart. From here on, it’s very easy to plot a waterfall chart! Let’s click anywhere in the cells with data and insert the waterfall chart. Then, we limit the data series to the Delta column. Our waterfall chart still needs some adjustments. We right-click on the last data point and select Set as Total. We will do the same with the first data point. Lastly, let’s call it “Waterfall chart” and let’s change the increase and decrease colors, by right-clicking on the legend: increase in green, and decrease in red. The final chart is great! Finally, we will proceed to Scatter plots. The table presents various genres scored on the average energy and danceability as well as the total count of the songs. We will click anywhere in the data and insert the second variant of the Bubble chart. Let’s call it “Scatter plot”. SInce there is a high concentration of values in one place, let’s double-click on the Y axis and change the bounds from 50 to 85. We will do the same with the X axis. That looks better! Let’s still right-click on the bubbles and add data labels. Since we can read the measure values from the charts, let’s label the bubbles with genre names instead. To do this, we click on the data labels and go to the Label option on the right. We untick Y value, tick Value from Cells and select the label range. Lastly, we will position the labels in the center. And here we go! Now, over to you!2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.