Chart Clinic 1 on 1
1. Chart Clinic 1 on 1
Welcome to the Chart Clinic. In this video, we will turn a bad chart into a stunning visualization. Let’s look at this one. The analyst who made this graph wanted to present the evolution of songs per genre over time. Using a clustered column as one universal chart type is a frequent error. Our first decision is to choose the right chart type. Let’s see what Excel recommends. Line and area charts, stacked columns, and other less useful options. Let’s try the line chart. This already looks much better, but let’s improve it! First, we will give the chart a name. This time we won’t type it in manually. Instead, we will double-click the Chart title, go to the formula bar, place an equal sign, and refer to this cell. Press Enter, and we have a dynamic chart title. If we adapt the text in the cell, the chart will reflect that. Let’s revert that by pressing CTRL+Z. Next up, we will add the Y-axis title, so we will add the Primary Vertical axis. The primary horizontal one presents the year, and it’s very clear from the labels what is on the X-axis, so we won't title that. For the Y-axis title, we will write "# Songs". Next, we will adapt the denomination of the Y-axis. How about converting 5000 to 5K? How do we do that? Double-clicking on the element in Excel often leads us to the correct menu, so let’s try that. Indeed, we can now use the Format Axis menu. We are interested in the last element, the axis options. As you remember, we can adjust the axis bounds here, but notice that we can also change the Display units. Let’s set it to thousands. Almost good, but we’d rather display it as 5K and try to get rid of this “thousands” label. Let’s go to the Number section of the axis options. Here we can change the category of the label. Per default, "general" is displayed. Let’s change it to a number with one decimal, and in the Format Code below, we will add the letter 'K' between double quotation marks and then click on Add. We can now remove the “thousands” label from our chart by selecting and pressing delete. Now, let’s click on the legend. This will display the Legend Options menu on the right. Let’s present the legend on top. We will now try to accentuate pop as the leading genre. First, we will click on the pop line. When we click on the paint bucket, we get to the Fill menu. Here we can adjust the thickness and color of the line, but there is also the line smoothing option. Let’s click on that. We will do the same with the other lines. That looks very smooth! Back to the pop line. Let’s change the color to pink and make it much thicker. We will also detract attention from the other lines by increasing their transparency. When we do that, they automatically change their color, so we must reconfirm their color as we go along. Let’s also reduce the visibility of the grid lines by coloring them in a brighter shade of gray. Finally, we will change the title to “Pop outclasses other song genres year over year”. We can paste it over the cell with the current title. But since it’s a formula, we won’t be able to highlight any word in it. Let’s overwrite the current formula referencing the cell with a hard-coded title. Now we can increase the title font size, bolden the word 'pop', and color it in pink. That’s a stunning graph that takes just seconds to interpret, much better than the original one! Over to you; let’s nail the exercises.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.