I stumbled upon a thread in the Excel subreddit about creating a scatter plot that showed both the importance and the performance of something. The question itself wasn’t necessarily HOW to create a scatterplot in Excel, but rather how to properly label the end result. It got me thinking about how to show someone both how to create a scatterplot and why two line graphs side-by-side might be more effective.
I’m in the institutional research game, ya see? Showing results effectively is kind of my jam right now. The easier it is to explain something, the easier it to for the higher-ups to make decisions.
So, I’m going to show you how to create a scatterplot in Excel, and then an alternative to that figure. I apologize in advance for the picture-heavy post. This is supposed to be a tutorial though so…uhh…deal.
First of all, this is the [fake] data I was dealing with. Let’s pretend that the community rated the College on how it preforms (5-point Likert scale) and how important it is to them (also 5-point Likert scale). Ignore the “frequency” column – I originally wanted to show frequency of use of College resources but decided it was too much at once.
Now let’s just go ahead and insert a blank scatter plot and then hit “select data” under Chart Tools -> Design
I enter in my data series manually. Whenever I attempt to select a table as a whole, it never puts anything where I want it to. If you know of a better way to select everything at once – go for it. For me, the control I get by adding in series points myself is worth the extra minute. Go ahead and click “Add” under the Legend Entries (Series) area.
As you can see above, I label my series name with the Year, plotting the Performance scores on the X axis, and the Importance scores on the Y axis.
This is what Excel spits out at you. It’s ugly. Let’s make it cool.
Let’s add in Axes Titles, a Chart Title, Data Labels and remove the vertical Gridline.
Now let’s turn the Y-axis label horizontal (double click to edit in the Format box). Label accordingly.
The Format Area box is my favorite to be honest. That little down arrow (see highlighted) allows me to navigate the areas of the chart I want to reformat. If you aren’t comfortable using this feature, double clicking on different areas of the scatterplot will take you to the correct (or generally correct) formatting area box.
Now let’s edit the markets (the dots of the scatterplot). I like to make mine fairly large so they are easy to see and change the colors individually to be color-blind safe or to match up with the College’s official colors.
If you right-click on the data label of one point, you can pull open the format data label box. Under Label Options, you can click and un-click different aspects of the label you want. For scatterplots, I only check the Series Name box and then un-check the Y-value box. This way, each dot will be labeled with the year only.
Finished product (along with a spiffy chart title for more of a POP than just a Pow) looks like this:
Problem is…this is hard to read (even for me who lives for this type of shit). If I can’t explain it easily to a client, I generally don’t like using it.
An alternative to this scatterplot is using two line graphs side by side. One line graph to show performance by year and one to show importance by year. Side-by-side, we can easily see a relationship between these two ratings.
Final product of the side-by-side line graphs looks like this. It’s a big easier to read and to explain.
The moral of this story is that just because you CAN make something, doesn’t mean you should.