Viz in Excel: Scatter Plots and Line Graphs

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.

scatter1

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.

scatter4scatter5

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.

scatter6

Let’s add in Axes Titles, a Chart Title, Data Labels and remove the vertical Gridline.

scatter7

Now let’s turn the Y-axis label horizontal (double click to edit in the Format box). Label accordingly.

scatter8

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.

scatter9

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.

scatter10    scatter11

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.

scatter12       scatter13

Finished product (along with a spiffy chart title for more of a POP than just a Pow) looks like this:

scatter14

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.

scatter16

The moral of this story is that just because you CAN make something, doesn’t mean you should.

Rad Resources:

Stephanie Evergreen talks dumbbells

Stephanie Evergreen talks labeling in scatterplots

Ann Emery’s Blog

 

Advertisements

4 thoughts on “Viz in Excel: Scatter Plots and Line Graphs

  1. Great run through of this information!! You have a talent for breaking things down in a simple fashion. Would you ever consider doing an instructional for those who aren’t familiar with pivot tables?? (asking for a friend).
    Great work!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s