I have a love-hate relationship with maps in Excel…

Pros of Excel Maps

• If you’re only making maps once in a while (i.e., you’re not a full-time cartographer), Excel maps are great because you don’t have to purchase/download/install/learn any specialty mapping software.
• Fast to make. Get started here: https://depictdatastudio.com/how-to-make-maps-in-excel-file-to-download/
• Works in Excel, PowerPoint, and Word (so it’s easy to copy/paste maps between those Microsoft programs, too).
• Easy to add your own branding (with Theme Colors and Theme Fonts).

Cons of Excel Maps

• Excel only makes heat maps by default (a.k.a. filled maps, choropleth maps). With behind-the-scenes tricks, you can also make hex maps, tile grid maps, tile grid trendline maps, and more.
• Currently only works with Microsoft 365. Time to upgrade, if you haven’t yet.
• Stickwithitness required. These work best for high-level U.S. maps (e.g., states). The smaller the geography (counties, zip codes), the more troubleshooting required. Making non-U.S. maps? Even more troubleshooting required. You’ve been warned. If you’re not tenacious, turn back now.

Can I Make Excel Maps for Places Outside the U.S.?

Yes!!!

When I was teaching data visualization workshops in South Africa, a woman from Eswatini asked whether she could map her country’s four regions.

After class, I attempted to make a quick YouTube video from my hotel room. I was planning to create a table with some pretend numbers… highlight those values… and insert a map.

And then I ran into all sorts of problems! Boo, Excel. I lost an hour to troubleshooting. I watched other YouTube videos. I read help forums. Here’s what I found.

10 Troubleshooting Tips for Excel Maps

In this video, you’ll learn the top 10 ways that Excel maps can get messed up.

Do you have more tips? Please comment and let us know.

What’s Inside

• 0:00 Intro
• 1:10 Dataviz On The Go
• 2:16 Making a new map – What didn’t work
• 2:56 (1) Make sure you’re on good internet
• 3:38 (2) Convert to Geography (or not)
• 5:20 (3) Make sure your headers are (correctly) labeled
• 6:02 (4) Convert to an Excel Table
• 6:45 (5) Spelling
• 7:13 (6) Leading or trailing spaces
• 7:54 (7) Special characters
• 8:19 (8) Add higher-level geographic details in a column(s) to the left
• 9:35 (9) Check alternative/former names
• 10:06 (10) Check language translations (e.g., both English and siSwati spellings)
• 10:30 Repeating the 10 troubleshooting tips
• 13:46 Your turn
• 14:13 Personal note from Ann

Download the Excel File

It’s here: https://depictdatastudio.ck.page/troubleshootingmapsinexcel

Read the Transcript

Ann K. Emery: [00:00:00] This video is turning out to be a hot mess. It was supposed to be a video like “Yes, you can make non US maps in Excel.” I made a YouTube video a while back, I’ll link to it right here. And it was about making maps in Excel. If you’re new to maps, you can watch that one first. It’s a great getting started video.

I had a US example. People commented like, “can I make non US maps?” Yes, you can. It was supposed to be that video, but then I ran into all sorts of issues. And this has turned into a, “I lost an hour of my life troubleshooting, so you don’t have to” video. And I’m going to show you 10 different things that I had to try to get this map to work.

And you’re going to just go through each of these items methodically with your map. And then at the end, please comment and tell me what the fix was. And hopefully we’ll all learn from each other.

I have a love hate relationship with Excel maps, as you [00:01:00] might be able to tell from, uh, my tone. I’m a little bit frustrated at the moment with how much troubleshooting we have to do sometimes.

So hopefully this video will just, you know, speed it up for everybody.

And by the way, if you’re new here, I’m Anne Emery. You’re watching Dataviz on the go. And I’m here in Johannesburg. Did I say that? I’m teaching a two day class and one of the women in the class, she was based in Eswatini, which you might know as Swaziland.

We had people from all different African countries in my class and we were making some maps and she raised her hand and she was like, “can I make maps for my country for the four regions of Eswatini in Excel?” and I was like, “yeah, of course.” And that’s the video this was supposed to be, right. Of, uh, making the Eswatini regions.

But I’m going to show you what I tried and what didn’t work. Right here. Okay. And then we’re going to create this “after” table. We’re going to have to reformat the table and I’m going to go [00:02:00] through these 10 troubleshooting tips, in order, of most common through least common. So please go in order as you’re troubleshooting and obviously let me know if there’s a number 11 or number 12 on this list

that you have to try that works for you.

All right. First up, here’s what didn’t work, right? You’re supposed to enter your regions. In this case, enter your values, which can be numbers. It can be, these are just made up percentages. It can be currency. Okay. You’re supposed to. So just highlight your table, go to insert, go to the map section and add a filled map, aka heat map, color coded map, choropleth map.

But of course Excel panics right away and it’s like, “uh, uh, uh, you have to have geographic data.” No big deal. We can do that, among many, many other things.

Oh, and I should mention when I was teaching this class in the [00:03:00] conference room, we did have internet, but it was like, you know, how event internet is. It was awful.

So I actually didn’t even get this far. We got stuck on. Cause I was like, “Hey. Mirela, like, come here. I made the map for you.” I was going to do that during the break in class, but we couldn’t because I wasn’t on good enough internet. And here in the hotel, I’m on like, iffy, iffy. It’s not perfect, right?

You’re not gonna be able to make maps from your airplane Wi Fi. From your public Wi Fi at the coffee shop that comes in and out, you have to have good enough Wi Fi. Okay, so earlier, number one didn’t even work.

We’re on number two now, which I expected to happen. We are going to convert to geography. Or not.

Okay. I’ll show you what both of these mean later. So let’s create the “after” table. I’m going to highlight these regions. I’m going to go actually, let me delete this one, right? Cause I want it to link to this. I want it to link to this revamped table I’m going [00:04:00] to make. This is a little hard to do with my one hand, but I got to hold my mini mic.

So you don’t get all the Background noises from the hotel. All right, where were we? Let’s convert these to geography. I’m going to highlight these regions, go to data, go to the little data types down arrow, say geography, please. It goes and connects to Bing maps and comes back down. So it takes a second to do right.

And now we have a map of the four regions. Except now we also have a new issue. We have this little caution sign, which when we click on, it tells us, sorry, we plotted 75 percent of your locations correctly. And you can see the three out of four, and this one is gray. Gray means like something was wrong with it, right?

There wasn’t any data. It tells us with the hover over, there’s no data. And I was like, but there is data there because even when you hover over the [00:05:00] little card and you can see that’s that region there’s data. So then I spent the rest of my hour, my agonizing hour, figuring out all these other things, watching other YouTube videos, reading blog posts and articles, reading help forums where Microsoft staff had responded.

One of the things that came up in a lot of help forum articles was to make sure your headers are labeled, and correctly labeled. That means if you don’t have the word Region here, you might run into issues. If you don’t have some type of column header here, you might run into issues. These words feed into the legend over here.

So if you don’t have it, you might get a error message, but it also just gives you a really poorly labeled legend. So you need to have some words there and they need to be correctly labeled. Like you can’t say that this is a country, but then it’s regions. It has… Obviously, [00:06:00] right? It’s gotta be correct.

Number four, I tried a good old fashioned Excel trick. I tried converting to an Excel table. This is a regular table, a generic table, a lowercase T table. A lot of times Excel tables can solve issues with pivot tables, pivot charts, slicers, so I thought, let me try. The, the trusty fixes, right? What you would do is you’d highlight your lowercase T table.

You go to insert. Here’s the official table. Yes, it has headers like we just talked about. Click okay. And. That, that doesn’t fix anything, right? Same issue with the map. Let me make sure you can read these.

All right. Number five, let’s try some spelling. I was not familiar with these regions before a few hours ago.

So I checked and double and triple checked all the spellings, against what the participants were saying, told me, and wrote for me, [00:07:00] against Wikipedia. I looked at all the websites to make sure that they were spelled correctly. They were. I copied and pasted them from the internet rather than even typing them in.

That wasn’t the case. I checked number six, because I copied and pasted them. I checked for leading or trailing spaces. I don’t visually see any leading or trailing spaces, but I thought let’s check everything Uncover every stone, right? A trailing space would be if you’re typing in or copying and pasting and you accidentally have a space that can cause a lot of issues in Excel and with formulas.

I’m thinking of lookup formulas that rely on exact matches with spaces. Sometimes if you copy paste, or if you download your data from a database or external source, you might have a leading space. I checked them all, that, that was not the issue. I checked for special characters. I’m thinking of Spanish speaking countries [00:08:00] where the names might have accents or they might have the little squiggle over the N.

A lot of different languages other than English have special characters. So you’re going to try with and without to see which one kind of, uh, jiggles Excel correctly, like shakes it so that it works. Not the case here.

Um, number eight, I saw this one on a lot of official Microsoft forums as well. Staff were saying, try adding higher level geographic details, which would go in a column off to the left.

Higher level geographic details are things like this. We’re going to add country so that just to make sure Excel knows we’re talking about Eswatini. So you’re going to type in your header, obviously, and then you just repeat the country name

all the way down. Not just once, right? So that it, it knows these are regions within East Swatini. This is important because a lot of [00:09:00] names are multiple places in the world. So I live in Windermere, Florida, but there’s Windermere, England. There’s a Windermere in Johannesburg. There’s lots of Windermere’s around the world.

So I can’t just make a map of Windermere. I would need to tell Excel, this is the Florida version, right? So I thought maybe these may, who knows, right? Maybe that’s the case.

I also mixed and matched. All of these troubleshooting tips. I thought, well, maybe this has to be converted to geography. Um, but we’re still, this one’s still gray, right? So I’m trying all the things.

Uh, number nine, check alternative or former names. The participant was like, “try Swaziland.” So I tried that.

When I press enter, watch what Excel does. It thinks for a split second, and then it says, nope. It’s Eswatini now, so you’re going to try things like that. Countries and [00:10:00] regions and districts and provinces, they change names. So give it a try.

And then number 10, you’re going to check for language translations. For example, um, Eswatini has two official languages. So I thought maybe the spelling is different of these regions in the two official languages, but that is the name. That is the one name of the region. There’s not like an English version or something, you know, so you’re trying all the different non US, non English things.

Then I got really frustrated and I thought, “this was supposed to take five minutes to make and record this YouTube video.” And here I am an hour into troubleshooting. I should be at dinner. I should be on the hotel treadmill, getting my 10, 000 steps in. Those are the things that I’m gonna do if everything works, any minute now.

And I said, “okay, come on, Ann, come on, brain, you can do it.”

And I went methodically down the list again, I kid you not.

So I started at the top again. I checked the internet. I [00:11:00] disconnected. I reconnected. Everything was fine. I can see my little wifi symbol at the moment is totally fine.

I tried converting to geography or not. I tried, uh, re, I tried like copying and pasting these in. To unregion it, uh, you can see that that doesn’t work. That makes it worse.

I made sure my headers are correctly labeled. I thought, I thought, and then I saw another help forum with somebody actually making a map of South Africa and they ran into the issue where,

oh, what was it? It was like they were supposed to plot provinces, but they used the word district. Or it was supposed to be district, and they used the word provinces. The naming was wrong, and the person like hadn’t realized. Or the data set they got was wrong, or something. So I thought, well, I think these are supposed to be regions.

The woman from Eswatini said they’re regions. The map [00:12:00] card says region. Online, it says there are four regions. The word region was very clear, but I was like, I don’t know, for kicks, let me try all the names, you know, so

I know they’re not states. These are not U. S. states, but I thought, let me try that.

Let me try province.

Let me try something more generic, like, what… I considered Guatemala has departments. Let me try district, because I was thinking of that South Africa help forum.

And earlier district had worked. And then I was like, I got it to work once with district, what’s going on? So this is where it gets wild.

And I was like, let me just start at the top of the list and I’ll just go back down again.

So I tried unconverting these to geography and finally, finally it worked.

I have no idea why. Okay, so to [00:13:00] recap. These are regions. Everybody says they’re regions. Everybody online says they’re regions. Microsoft on the little cards thought they were regions, but for the map to work, I can’t say region. It has to say district.

Typically maps have to typically have the convert to geography. It has to be like categorized as it’s, this isn’t a percentage. This isn’t a text field. It’s not a date. It’s geography. Not the case here. Now everything is correctly filled in. You hover over it. It knows what that region name is. The moral of this video is keep trying.

Just go from the top and let it back down. Try every little combination you possibly can. And then most importantly, please comment below the video. Let me know if you have troubleshooting tip 10 million that you’ve tried.

Let me know where you get stuck.

Let me know what works for you.

I can’t [00:14:00] wait to read through your comments because I know we’re all going to help each other so that we don’t get so frustrated. We can speed this up a little bit more.

Thank you so much in advance for your help in the comments.

Hey, future Ann here. I got my steps in, I had a steak, and now I’m no longer stressed. Funny how that works. And now I’m going to bed. Good night.

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.