Ready for a brain-bending tutorial?

It’s *not* easy to make dot plots in Excel.

These are non-native charts — meaning we’ll have to reconfigure our table, and use a scatter plot(!) — to trick Excel into making our dot plot.

The learning curve is worth it, promise.

Download the Excel File

It’s here: https://depictdatastudio.ck.page/dumb…

What’s Inside

• 0:00 How to Make Dumbbell Dot Plots in Excel
• 0:25 Dataviz On The Go
• 0:33 Dot Plots are Non-Native Charts
• 1:03 It’s a Scatterplot (?!?!)
• 1:50 Pep Talk for the Perfectionists
• 3:23 Live Drawing Demo
• 6:59 Color-Code Your Table
• 7:08 Stack Your Table
• 7:43 Add “Y” Values
• 8:28 Sort and Put the “Y’s” Next to Each Other
• 9:09 Insert a Scatterplot with Straight Lines and Markers
• 9:45 Ack!
• 9:52 Remove the Lightning Bolt
• 11:02 Format Format Format
• 11:46 Your Turn: Questions? Comments?
• 12:05 A Personal Note

Transcript

[00:00:00] In this tutorial, I’m going to try to teach you how to make dumbbell dot plots in Excel.

I say “try” because, usually, in workshops, this takes about 30 minutes to teach, and everybody’s got laptops, and I demo a skill, and they practice, and I demo, and they practice, and it’s a captive audience.

On YouTube, everybody wants things really, really quickly, and I don’t know if I can cram this into five minutes.

… … , we might need 10, though.

You’re watching Dataviz on the Go with me, Ann Emery. Because you’re busy, I’m busy, let’s get to it with some jet speed tutorials.

Now, the first thing you’re going to notice about dumbbell dot plots is, when you highlight your table and you go up to insert, just like you normally would to add a new chart, you can look all day long…

You are not going to find a dumbbell dot plot up there. It is not here. It is not here. It’s not there.

It is not a built in chart. It is not a native chart. It’s a non native chart.

That doesn’t mean we can’t make it, but we have to do some behind the [00:01:00] scenes magic tricks to make it happen.

So we’re going to choose a similar chart, similar ish, kind, kind of, kind of not, right?

The scatterplot, the scatterplot especially with the straight connecting lines. We’re going to use a scatterplot as the foundation. And we’re going to disguise it and make it look like a dot plot.

This is a very advanced, very sophisticated, very brain bending way of thinking about Excel. It’s a scatterplot that looks like a dot plot.

So what that’s going to mean is we have to assign each of these dots X, Y coordinates, which means our table, like this, It’s going to have to be totally reconfigured and that’s going to be a little bit tricky to figure out, but that’s why I’m here to walk you through it.

So if you’re a perfectionist, please go easy on yourself. Just give yourself a break. There is a learning curve here. This is not an easy chart type at all, but it’s worth it. I [00:02:00] just, please keep going. Please, please, please.

And if you’re tired, like I clearly am just scroll down below the video, because I’m going to give you this spreadsheet with the template that I’m using so you can just download it and just punch in your numbers and not have to start from scratch. So you don’t have to fight with Excel so much.

All right, let’s keep going. Let’s transform our original table into a magic reconfigured table.

The first thing we’ve got to do though, especially the first few times you’re doing this is You’ve got to sketch it, please, please don’t skip this step. You need to draw out your dot plot and figure out where each dot’s going to go so that later as you’re making it, you can compare and say, “wait, is this in the right spot?””

No, I need to sort my table different.” “Wait, I think this is going wrong.” And you can kind of diagnose your own errors that are going on as you’re learning.

So I’m going to use my draw feature on my computer, but you might just use a good old paper and pencil when you draw your dot plots.

Okay, so let’s draw this out and I’m going to show you how it’s [00:03:00] a scatter plot with XY coordinates, that is ultimately gonna look like a dot plot.

Okay? That’s the most important thing for you to remember. It’s a scatter plot that looks like a dot plot. And then the other thing is just draw it. Please. Please,

Alright, let’s draw it. So first we’re gonna draw our xy. Okay, this looks like eighth grade math class, doesn’t it?

Here’s our x, y, x, y coordinates. That’s what a scatterplot has going on behind the scenes. Then you’re going to draw your scale.

This fictional scale goes from, uh, zero to ten. Yours in real life might go from zero to ten million. It might have percentages. It might have currency. This works with all the units. It doesn’t have to be zero to 10. It can be whatever your real life units are.

And then the height, uh, these are gonna be our categories that we’re comparing. We’re going to have category [00:04:00] A, B, C, D. I’m going to draw some grid lines in here. That’s why I’m using gray. And then let’s figure out where our dots go.

And remember, um, we’re just, I’m just drawing this. Okay. I’ve already made the finished version to show you where we’re heading, but in real life, you’d be like, what, where, what is this going to look like with my numbers and my percentages?

Okay. And let’s be consistent. Um, I think, what do I have group one in green? Yeah. Let’s always do group one in green. Let’s always do group two in purple. We’re going to use Mardi Gras colors. Why not? Why not? Consistent color coding absolutely is going to help your brain to figure this out. So please do this as you’re sketching on your own paper.

Uh, group one, let’s do those in green. Group two, we’ll do in purple.

Let’s draw out our dots and then we’re going to assign them X, Y coordinates. So that first 8. 1, where is that going to [00:05:00] go? It’s going to go over on the X, 8. And then it’s, it’s the A, so it’s going to go right here. Okay. That’s that dot and let’s assign it an XY coordinate. So that is eight.

What is it? 8. 1. That’s my X comma, the Y. is, it goes up, one, two, three, four. Let me write that out for you just to make it really easy to follow. One, two, three, four. It’s very hard to write with a mouse. 8. 1 comma four. Okay, the next one 5. 6 that goes over on the X about this far, but that’s about where the 5.

6 would be. And then it goes, that’s 5. 6. It goes up on our, [00:06:00] like, Y, Y in air quotes, right? Our fictional Y, our placeholder Y, it goes up three. Let’s do maybe one or two more. Uh, the 4. 5. Okay. So it goes over 4. 5. That’s approximately here. XY coordinates, 4. 5 comma 2. Yep. You’re right. And the next one is 8. 6.

That’s around here. 8. 6 up 1. You do the same thing for your purples. Uh, that’s a 5 around here. That is five comma four. This one is What is it? Oh, 2. 3. That’s around here. 1. 3, 4. 4. You get the gist of it? Okay. Draw it out so you can envision, like, where’s everything going? Did I get it right?

It takes about this long.

Next up, let’s take our original table and [00:07:00] we’re going to color code it. You’re just going to add some fill behind it, okay, to keep yourself all organized.

Then, we have to re orient, you’re just going to do a copy paste, you’re going to stack it. Instead of group 1, group 2, beside each other, you’re going to stack them.

You’re just going to say group 1, group 1, group 1, group 2, group 2, group 2, and, oops, this isn’t group, this is, this is your value, okay, which is also known as your X. Your X in quotes, cause it’s not a real X. It’s like the X value that we have to type into Excel to make this all, you know, work and be figured out behind the scenes.

All right. The next thing we’re going to do is we’re going to add some Y values. And again, this is supposed to be X. This is Y. Okay, so we’ve got the groups repeated, we’ve got the X’s, and then we’ve got the Y’s. Remember, we already figured out what the Y’s would be. Uh, here is another tip to make sure you’re doing it right.

[00:08:00] Figure out how many dots you’re going to have. It’s one, two, three, four, five, six, seven, eight. 1, 2, 3, 4, 5, 6, 7, 8. Okay. Eight dots means eight entries on your table. 1, 2, 3, 4, 5, 6, 7, 8. Eight dots, eight entries, eight sets of X and Y coordinates. All right, let’s keep going. The next thing you’re going to do is you’re going to sort it and you’re going to put your Y’s next to each other.

So your fours are going to go together. Your threes are going to go together, your twos and your ones. Don’t overthink sorting. Okay. It just means you take your table with your X’s and your Y’s and you go to data and you go to sort and you say, I’m going to sort by my Y. And it doesn’t matter if you do smallest to largest or largest to smallest.

Okay. It doesn’t matter. It just means they have to be next to each other. Like here, the ones are next to each other. Okay. [00:09:00] Here, the fours are next to each other. Do you see how the color coding changed? It’s green, purple, green, purple now, right? The next thing we’re going to do is we’re going to highlight just the inside of the table, just the interior that I’ve made darker for you.

And I outlined it in black to make sure you can see it. You’re going to go to insert and you’re going to insert our. Scatterplot. If you want it to look like a dumbbell dot plot with a connecting line in there, you’re going to pick this one, the scatter with straight lines and markers. If you just want the dots by themselves, you can pick this one.

Okay. I wouldn’t pick wavy. That would be super weird. I wouldn’t pick this one. That scribble, scrabble. Okay, I’m going to do this one for us. And then you get the Harry Potter lightning bolt and you’re like, and, uh, that’s not what I wanted. That’s okay. We can remove the lightning bolt. It’s going to look more like this.

Can you start to see it? Can you see your dot plot to remove that connecting lightning bolt? Okay. So like, here’s the [00:10:00] connecting line I want to remove. It depends how your table is sorted of which dot you click on. It’s going to be either this dot or this dot. Okay. So you’re just going to try one. And if the wrong line is removed, you’re just going to click undo and you’re going to try again.

So let’s try this one. Okay. I’m going to click on this dot cause I’m going to guess that it controls this line. So if you click on this dot the first time, All of the points are selected. You click on it a second time so that just that dot’s there. You’re going to hold your mouse over that spot. You’re going to do a right click, go to outline and say, no outline.

No, thank you. Okay. Part of the lightning bolt’s gone. Let’s do it again. This dot controls this connecting line. Click on it once, twice, right click. Outline no outline. Okay, you’re gonna get in the rhythm of it. It’s just gonna take this long. Don’t don’t worry It’s really really quick. Okay, there’s your dot plot kind of right you have to [00:11:00] Format format format that takes a little bit more time Things to keep on your radar would be you can control the color of the dot You can add the, uh, labels right here.

I just added the group names in text boxes, but the labels are built in. Uh, you can do this fancy ways to add whatever your category labels are. Honestly, I usually just do text boxes because I find it’s actually, uh, faster in the long run. I should say I don’t do text boxes, plural. I do see one text box that I very carefully format so that it’s lined up with its grid line.

You’re going to adjust the min and the max as you need to. You might make the connecting line thicker. All the normal things. All the normal formatting things that I cover in all my other videos and blog posts. All right, it is your turn. Comment below the video. Let me know. Are you totally lost? Are you kind of following?

How are you feeling about this scatterplot into dotplot thing? Do you want the spreadsheet? It’s there. All right. Good luck. Good luck with [00:12:00] your dotplots. Please sketch. And have fun. Bye.

Finished the video. Made a quick dinner. Now we’re outside doing turkey trot training. I’m gonna try to run with the twins and the big guy.

Hi, big guy. Well, the girls, where are they? Running with daddy.

Colors can make or break a chart.

Colors direct our eye movements, and therefore our brains and attention.

It’s up to you: will you help or hinder your reader’s understanding?

Step 1: Start with Your Brand Colors

Otherwise, your graphs, slides, and dashboards will be Frankensteined.

I’ve written about brand colors and brand presents in other posts.

Some of those resources include:

• Examples of organizations’ brand colors used in graphs
• How to read color codes in style guides
• How to enter your custom color codes in Excel

Step 2: Do Your Accessibility Testing

I’ve written about colorblindness, color contrast, grayscale printing in other posts.

Some of those resources include:

• An official color contrast test
• An official colorblindness and grayscale printing test

Then, your accessibility testing “results” should go inside your organization’s Dataviz Style Guide.

Step 3: Apply Those Brand Colors According to the Data & Variables

Now, it’s time to apply those branding colors to ensure that your graph is intuitive.

Look at your graph: Is your variable binary, sequential, diverging, or categorical?

Or, do you want to tell a story with a dark-light contrast?

Binary Variables Get Binary Color Schemes

Binary variables include yes/no data, such as:

• yes/no survey questions
• people who speak Portuguese as their primary language vs. people who don’t
• people who own a home vs. people who don’t
• people who graduated from program on time vs. people who didn’t
• people diagnosed with an illness vs. people who don’t have it

For binary variables, choose one brand color. The “presence” of the attribute gets the darker color, and the “absence” of the attribute gets the lighter color.

Here’s an example:

Sequential Variables Get Sequential Color Schemes

a.k.a. ordinal

Sequential variables have a natural order.

Examples include:

• age ranges (5-9 year olds, 10-14 year olds, and 15-19 year olds)
• income levels
• highest educational level completed (some high school, high school diploma, some college, etc.)
• years (Year 1, Year 2, and Year 3 of a project)
• semesters (fall, spring, fall, spring…)
• cohorts (first cohort of participants, second cohort, etc.)

For sequential variables, choose one brand color, and use a light-dark gradation of that color.

Here’s an example:

Categorical Variables Get Categorical Color Schemes

a.k.a. nominal

Categorical variables include:

• race/ethnicity (African American, Asian, Hispanic/Latin@, White, etc.)
• gender (male, female, nonbinary, genderfluid, etc.)
• chapters of a report
• sections of a presentation
• categories of a dashboard

For categorical variables, use a different brand color for each category.

Here’s an example:

Diverging Variables Get Diverging Color Schemes

Diverging variables are opposites.

Examples include:

• agree/disagree scales on surveys
• changes over time (e.g., “50 percent decrease” or “70 percent increase”)

For diverging variables, choose two brand colors, and place the darkest shades on the poles.

Here’s an example:

Combining these Techniques

In most real-life projects, we need to combine these color techniques.

In this map makeover, for example, we needed to:

• use brand colors, not software defaults;
• use two brand colors, one for each category; and
• apply a dark-light gradation to each map, because these are ordinal variables.

In this population pyramid makeover, we needed to:

• use two brand colors, one for each timeframe, and
• apply a dark-light storytelling emphasis to each pyramid.

Your Turn

What types of color questions do you have? Comment below..